利用pandas和数据库交互

pandas.io.sql模块提供了一组查询包装器,以便于数据检索和减少对特定于DB的API的依赖性

主要方法:

read_sql_table(table_name, con[, schema, …])     将SQL数据库表读入DataFrame

read_sql_query(sql, con[, index_col, …])     将SQL查询读入DataFrame

read_sql(sql, con[, index_col, …])     将SQL查询或数据库表读入DataFrame

DataFrame.to_sql(name, con[, schema, …])     将存储在DataFrame中的记录写入SQL数据库

引擎:

engine = create_engine('postgresql://scott:tiger@localhost:5432/mydatabase')

engine = create_engine('mysql+mysqldb://scott:tiger@localhost/foo')

engine = create_engine('oracle://scott:tiger@127.0.0.1:1521/sidname')

engine = create_engine('mssql+pyodbc://mydsn')

# sqlite://<nohostname>/<path>
# where <path> is relative:
engine = create_engine('sqlite:///foo.db')

# or absolute, starting with a slash:
engine = create_engine('sqlite:////absolute/path/to/foo.db')

以MySQL为例:

1、创建链接数据库引擎

import pandas as pd
from pandas import DataFrame
from sqlalchemy import create_engine
engine = create_engine("mysql+pymysql://root:root@192.168.2.232:3306/test", encoding="utf8")
# create_engine()有个参数为echo,默认为False,如果指定为True,则会输出查询过程

2、read_sql_table(读取整张表)

pandas.read_sql_table(table_name, con, schema=None, index_col=None, coerce_float=True, parse_dates=None, columns=None, chunksize=None)

data1 = pd.read_sql_table('user', con=engine)
type(data1)
data1
id name age sex
0 0 张三 20
1 1 李四 21
2 2 王五 22
3 15 赵六 50
4 16 Jack 40
5 17 Bob 45
6 18 Linda 28

3、read_sql_query(自定义sql)

read_sql_query(sql, con, index_col=None, coerce_float=True, params=None, parse_dates=None,chunksize=None)
– 该方法可以执行怎删改查操作
– 在执行增删改操作的时候会报ResourceClosedError的错误,但是不影响正常功能的执行(貌似是个BUG),可以通过try…except解决(有大佬知道问题根源或者更佳解决方式欢迎指教)。

# 3.1 查询1
select_sql1 = "select * from user"
data2 = pd.read_sql_query(select_sql1, con=engine)
data2
id name age sex
0 0 张三 20
1 1 李四 21
2 2 王五 22
3 15 赵六 50
4 16 Jack 40
5 17 Bob 45
6 18 Linda 28
# 查询2
select_sql2 = "select name,age from user where age > 20"
data3 = pd.read_sql_query(select_sql2, con=engine)
data3
name age
0 李四 21
1 王五 22
2 赵六 50
3 Jack 40
4 Bob 45
5 Linda 28
# 3.2 增加
insert_sql = "insert into user (name, age, sex) values %(data)s"
params = {'data': ['赵六', '36', '男']}
pd.read_sql(insert_sql, con=engine, params=params)
# 3.3 修改
update_sql = "update user set age=50 where name=%(data)s"
params = {'data':'赵六'}
pd.read_sql_query(update_sql, con=engine, params=params)
# 3.4 删除
delete_sql = "delete from user where name=%(data)s"
params = {'data': '赵六'}
pd.read_sql_query(delete_sql, con=engine, params=params)

4、read_sql(为了向后兼容,可以认为是read_sql_table()和read_sql_query()的结合体)

pandas.read_sql(sql, con, index_col=None, coerce_float=True, params=None, parse_dates=None, columns=None, chunksize=None)

综合前两个方法,只是为了兼容。用法类似

5、to_sql (写入数据库)

to_sql(name, con, flavor=’sqlite’, schema=None, if_exists=’fail’, index=True, index_label=None, chunksize=None, dtype=None)

  • if_exists=’replace’,如果数据库中有test_cjk表,则替换。
  • if_exists=’append’,如果数据库中有test_cjk表,则在表后面添加。
  • if_exists=’fail’,如果数据库中有test_cjk表,则在写入失败。
data = [['Jack', 40, '男'],
        ['Bob', 45, '男'],
        ['Linda', 28, '女']]
columns = ['name', 'age', 'sex']
df = DataFrame(data, columns=columns)
df
name age sex
0 Jack 40
1 Bob 45
2 Linda 28
df.to_sql('user', con=engine, if_exists='append', index=False)

点个赞呗:程序员虾说 » 利用pandas和数据库交互

赞 (10) 打赏

评论 0

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

请作者喝杯咖啡~

支付宝扫一扫打赏

微信扫一扫打赏