python写入mysql数据库_python对MySQL数据库进行读写

❝ 数据分析师需要经常从数据库中取数据进行分析，MySQL数据库是较为常用的关系型数据库，python是目前火热的数据分析语言，在python中，利用第三方库直接对数据库进行操作，包括数据的读取和写入等，能极大提高数据分析师的工作效率。本文介绍利用PyMySQL和SQLAlchemy两个python库对MySQL数据库进行读写操作。

❞

1 软件版本Win10 64bit

Python 3.8

PyCharm 2019.3.4

PyMySQL 0.9.3

SQLAlchemy 1.3.16

2 安装库

需要安装PyMySQL和SQLAlchemy两个库，在终端中用pip命令进行安装，如下：

pip install PyMySQL

pip install SQLAlchemy

3 PyMySQL读写数据库

脚本如下：

import pymysql

import pandas as pd

if __name__ == '__main__':

# 创建数据库连接(需要修改)

con = pymysql.connect(host='xxx',

port=3306,

user='xxx',

password='xxx',

db='xxx',

charset="utf8")

try:

# 创建游标(默认数据返回tuple,修改为dict)

cur = con.cursor(cursor=pymysql.cursors.DictCursor)

# 读取表

get_sql = "select * from tb_newCity" # sql语句

cur.execute(get_sql) # 执行sql语句

get_df = pd.DataFrame(cur.fetchall()) # 获取结果转为dataframe

print(get_df)

# 创建表

create_sql = 'create table if not exists new(id int,value double)'

cur.execute(create_sql)

# 写入表(数据库中必须存在该表)

df = pd.DataFrame({'id': [1, 2], 'value': [12, 13]})

insert_sql = 'insert into new (id,value) values (%s,%s)' # %s占位符

# 1.循环执行

for i in range(df.__len__()):

# 插入的数据类型需要与数据库中字段类型保持一致

cur.execute(insert_sql, (int(df.iloc[i, 0]), float(df.iloc[i, 1])))

# 2.批量执行

cur.executemany(insert_sql, [df.iloc[0].to_list(), df.iloc[1].to_list()])

# 提交所有执行命令

con.commit()

print('数据写入成功！')

cur.close() # 关闭游标

except Exception as e:

raise e

finally:

con.close() # 关闭连接

注意点：「连接及游标：」创建数据库连接时，需要根据实际情况修改主机host、端口port，用户名user，密码password，数据库db参数。

端口port默认值是3306，设置charset为utf8，解决读取中文问题。

默认游标返回的查询数据格式是tuple，通过修改游标类型可以控制返回数据格式。「读取表：」用游标cur的execute()方法运行sql语句完成表的读取。

调用游标cur的fetchall()方法可以获取全部的查询数据。此外，fetchone()方法获取第一条数据，fetchmany(n)方法获取前n条数据。「写入表：」写入表时，需要保证数据库中存在该表，可以先创建该表。

用sql语句完成写入表的操作，sql语句中可以先用%s表示占位符，在执行语句中再用具体值替换。

pandas的dataframe类型数据写入数据库中，可以用循环execute()或采用executemany()实现。

写入数据库中的数据必须与数据库中定义的字段类型保持一致，不一致时可以进行转换。

创建表和写入表操作，需要最后用commit()方法提交，才算完成操作。

运行脚本，输出如下：

4 SQLAlchemy读写数据库

脚本如下：

from sqlalchemy import create_engine

import pandas as pd

if __name__ == '__main__':

try:

# 创建数据库引擎

con = create_engine("mysql+pymysql://user:password@host:port/db")

# 读取表

sql = 'select * from t_json' # sql语句

get_df = pd.read_sql_query(sql, con) # 结果为dataframe

print(get_df)

# 写入表

df = pd.DataFrame({'id': [1, 2], 'value': [12, 13]})

df.to_sql('new', con, if_exists='append', index=False)

print('数据写入成功！')

except Exception as e:

raise e

注意点：创建数据库引擎con时，需要根据实际情况修改主机host、端口port，用户名user，密码password，数据库db参数。

调用pandas的read_sql_query方法运行sql语句完成表的读取。

pandas的dataframe对象可以直接写入数据库，调用to_sql方法即可，数据库中无该表时，会自动创建表，无需先建表。其中的if_exists参数控制写入行为，具体解释如下：

if_exists 参数解释 fail 存在表，则报错 replace 存在表，则替换原表 append 存在表，则在原表行末追加 运行脚本，输出如下：

5 常见报错「Access denied for user...」

原因：MySQL配置参数出错

解决办法：检查user,password,host,port,db等参数的设置，用其他工具测试mysql连接是否成功，做对比检查。「pymysql.err.InterfaceError...」

原因：使用PyMySQL时，关闭了创建的连接，对游标操作产生

解决办法：重新创建连接，游标，再对游标进行操作。「AtrributeError: numpy.int64 object has...」

原因：使用PyMySQL时，python里定义的数据类型mysql不支持

解决办法：对数据进行类型转换，参考第三章的脚本。

6 总结

PyMySQL和SQLAlchemy是两个常见的python操作MySQL工具，但从数据分析角度出发，数据分析师基本的数据库操作就是读写。就数据库的读写方面而言，SQLAlchemy库的优势更明显，操作简单，取与pandas的dataframe之间的转换更为容易，python做数据分析推荐用SQLAlchemy库去完成MySQL的读写操作。