【最全】Python连接数据库取数与写入数据

阿黎逸阳

发布于 2023-08-21 19:55:27

9620

发布于 2023-08-21 19:55:27

文章被收录于专栏：阿黎逸阳的代码

不管是做数据分析还是风控建模，都避免不了从数据库中取数，和把数据写入数据库。

本文整理连接数据库的不同方法，以及单条写入数据和批量写入数据。

所有代码都实测可用，并实际应用于生产，分享给更多在这方面遇到困难的朋友。

取数后的分析结果若想定时发送给相关人员，可参考【干货】用Python每天定时发送监控邮件。

一、连接数据库方法一(pymysql)

首先介绍连接数据库的方法一，具体代码如下：

import pymysql
import numpy as np 
import pandas as pd

#36数据库
conn = pymysql.connect(host='ip', user='用户名', passwd='密码', db='数据库名')
#建立连接，host中填mysql服务器所在的主机的ip，user中填为用户名，passwd中填密码，db中填数据库名
sql = ''' select * from  credit_approve_result limit 3'''
date = pd.read_sql_query(sql, conn)

主要思路是先导入库，再填入ip、用户名、密码、数据库名等信息建立连接。

然后定义sql查询语句，进行数据查询。

如果对pymysql函数有疑问，可以参考历史文章：实现Python连接数据库取数需求。

二、连接数据库方法二(create_engine)

接着介绍连接数据库的方法二，具体代码如下：


import pandas as pd 
from sqlalchemy import create_engine

conn = create_engine('mysql+pymysql://user:passwd@ip:3306/db',encoding='utf8')
#建立连接，user替换为用户名，passwd替换为密码，ip替换为mysql服务器所在的主机的ip，db中填数据库名
sql = ''' select * from  credit_approve_result limit 3'''
date = pd.read_sql_query(sql, conn)

主要思路是先导入库，再填入用户名、密码、数据库名等信息建立连接，其中3306是默认端口。

然后定义sql查询语句，进行数据查询。

可以发现这两种连接方式很像，只是用到的库不一样，填信息的形式不一样，大体步骤是一样的。

三、把数据导入到数据库

刚刚我们已经从数据库中读取数据了，我们试下把sql取数的结果导入到新表中。具体代码如下：

conn = create_engine('mysql+pymysql://user:passwd@ip:3306/test',encoding='utf8')
#建立连接，user替换为用户名，passwd替换为密码，ip替换为mysql服务器所在的主机的ip
date.to_sql("jlkj_cs", conn, if_exists='replace', index=False)

以上代码是把date数据导入到test库的jlkj_cs表中，如果之前存在这张表，用date的数据替换这张表。 四、一行一行追加写入少量数据 为了让大家更清晰地看到取数，写入数据，追加写入数据的逻辑。这一节把前面几小节的内容进行了汇总，并增加了一行一行追加写入少量数据的代码。具体如下：

import pandas as pd
import pymysql.cursors
from sqlalchemy import create_engine

#读取数据
conn = create_engine('mysql+pymysql://user:passwd@ip:3306/temp_data_2',encoding='utf8')
jxb_sx_head3 = pd.read_sql('''select session_id, customerName from xb_policy_sxall limit 3''',conn)

#写入数据
conn = create_engine('mysql+pymysql://user:passwd@ip:3306/test',encoding='utf8')
jxb_sx_head3.to_sql("jlkj_cs", conn, if_exists='replace', index=False)

#单条插入数据
conn = pymysql.connect(host='ip',user = "用户名", passwd = "密码", db = "test")
cursor = conn.cursor()
cursor.executemany(
"insert into jlkj_cs values(%s, %s)",
[
('OT111', '张一'),
('OT112', '张二')
]
)
conn.commit()

#检查是否插入成功
conn = pymysql.connect(host='ip',user = "用户名", passwd = "密码", db = "test")
cs_add_date = pd.read_sql('''select * from jlkj_cs''',conn)
cs_add_date

得到结果：

五、批量追加写入数据在第四小节中已经介绍了一条一条写入数据的方法，本小节介绍把数据框直接追加写入到数据库表中的方法。具体代码如下：

conn = create_engine('mysql+pymysql://user:passwd@ip:3306/test',encoding='utf8')
date_pl.to_sql(name='jlkj_cs', con=conn, if_exists='append', index=False, index_label=False)
cs_add_date2 = pd.read_sql('''select * from jlkj_cs''',conn)
cs_add_date2

得到结果：