python读写mysql数据库
方法一:
1. python连接mysql数据库:需要用到 pymysql 库和 sqlalchemy库:
import pandas as pd from sqlalchemy import create_engine import pymysql
创建连接 create_engine:
con_engine = create_engine('mysql+pymysql://username:passwd@localhost:3306/database_name') # 示例: # con_engine = create_engine('mysql+pymysql://root:123456@localhost:3306/database_name') # username:数据库用户名,例如 root # passwd:密码,例如 123456 # localhost:host主机地址,例如:127.0.0.1 # 3306:是数据库的端口,一般是3306,但是也可能改变 # database_name:数据库名 con_engine = create_engine('mysql+pymysql://username:passwd@localhost:3306/database_name?charset=utf8') #需要时也可用
2. 提供 sql 语句
sql_ = "select * from table_name;" #table_name某个表名
3. 执行查询,使用 read_sql_query 得到pandas的dataframe数据
df_data = pd.read_sql_query(sql_, con_engine) xxx = df['key_words'].tolist() print(xxx)
4. 写数据到 mysql 数据库
#写两列数据,建立dataframe格式数据 df_write = pd.DataFrame({'id_py':[1,2,3,4],'name_py':['Tom','Bob','Cathy','Jack']}) df_write.to_sql('table_py', con_engine, index = False) # index = False表示不加索引列,写到table_py表,如果表已经存在,会报错:ValueError: Table 'table_py' already exists.
方法二:使用 read_sql
# username = 'root' # passwd = '123456' # database_name = 'db_test' con_engine = pymysql.connect(host = 'localhost' ,user = username, password = passwd, database = database_name, port=3306, charset = 'utf8') #通过参数形式传递,参数是字符串形式 sql_ = "select * from table_name;" #table_name某个表名 df_data = pd.read_sql(sql_ , con_engine)
方法三:直接使用pymysql进行查询,插入数据、更新数据、删除数据等修改数据表的操作需要添加 commit 和 rollback 语句
import pymysql # 建立数据库连接,username, passwd, database_name都是如上,预先定义的参数,localhost也可以替换成某个数据库的ip地址 con_engine = pymysql.connect(host = 'localhost' ,user = username, password = passwd, database = database_name, port=3306, charset = 'utf8') # 使用cursor()方法获取游标 cursor = con_engine.cursor() # SQL语句 sql_ = "select * from table_name;" try: # 执行SQL语句 cursor.execute(sql_) # fetchall()获取所有记录,形成的是元组,results = cursor.fetchmany(10)获取前10条,results = cursor.fetchone()获取一条数据 results = cursor.fetchall() for row in results: #依次获取每一行数据 id_ = row[0] #第1列 key_words_ = row[1] type_words_ = row[2] address_name_ = row[3] # 打印结果 print(id_,key_words_,type_words_,address_name_) except: print ("Error: unable to fetch data") # 关闭数据库连接 con_engine.close()
例如删除操作:
import pymysql # 建立数据库连接,username, passwd, database_name都是如上,预先定义的参数,localhost也可以替换成某个数据库的ip地址 con_engine = pymysql.connect(host = 'localhost' ,user = username, password = passwd, database = database_name, port=3306, charset = 'utf8') # 使用cursor()方法获取游标 cursor = con_engine.cursor() # SQL语句 sql_ = "delete * from table_name where id > 5;" try: # 执行SQL语句 cursor.execute(sql_) con_engine.commit() #提交修改,类似github的commit操作 except: con_engine.rollback() #不成功则回滚 # 关闭数据库连接 con_engine.close()
参考:
https://www.jianshu.com/p/238a13995b2b
https://blog.csdn.net/luoluopan/article/details/100074959
https://www.runoob.com/python3/python3-mysql.html