python读取和写入PostgreSQL问题总结

92 阅读 0 评论 61 点赞

我是靠谱客的博主彪壮河马，这篇文章主要介绍python读取和写入PostgreSQL问题总结，现在分享给大家，希望可以做个参考。

Python读取PostgreSQL数据

流程如下：

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
import psycopg2
#连接数据库
conn = psycopg2.connect(database = 'name', user = 'admin', password = '123456', host = '10.10.10.10', port = '5432')
curs=conn.cursor()
#编写Sql，只取前两行数据
sql = 'select * from table_name limit 2'
#数据库中执行sql命令
curs.execute(sql)
#获得数据
data = curs.fetchall()
#关闭指针和数据库
curs.close()
conn.close()

返回的data结果是一个以各行数据为元组的列表，如下：

复制代码

1
2
3
4
5
6
7
8
[('L002','WKQ1','WZ1A','WZ1A','L','WZ01-12',
'10073864791','R5400','5','18','36','362.29',
'372.57','351','20190311','20190317','11','0','0',
'0,8','3','3','0.83','3','3','20190310'),
('L002','WKQ1','WZ1A','WZ1A','L','WZ01-14',
'10073864791','R5400','5','18','36','300.29',
'372.57','351','20190311','20190317','11','0','0',
'0,8','3','3','0.83','3','3','20190310')]

可以通过pandas对data进行进一步处理：pd.DataFrame(data)

Python写入PostgreSQL数据

python写入PG的代码如下：

复制代码

import psycopg2
#连接数据库
conn = psycopg2.connect(database = 'name', user = 'admin', password = '123456', host = '10.10.10.10', port = '5432')
curs=conn.cursor()
#编辑写入数据的sql
insert_sql = 
"insert into table_name
(warehouse_code,storehouse_code,zone_code,picking_zone_code,picking_type,location_code,
gds_id,kunnr,yest_tot_qty,week_tot_qty,month_tot_qty,week_avg_prlab,month_avg_prlab,
present_storage,start_date,end_date,week_flag,unsaleble_flag,super_A,sales_segment,
present_class,week_avg_qty,month_avg_qty,now_level,pred_level,statis_date,
prediction_class,action,target_area)
values
('L121','6','IWDX','IWDX','L','IWDX-001-0101','120339999','S70227820','0','0','2','1','1','1','20190311','20190317','11','0','0','0,7','1','0','0','1','1','20190310','2','2','targ_null'),
('L002','WKQ1','WZ1A','WZ1A','L','WZ0114','10073864791','R5400','5','18','36','300.29',
'372.57','351','20190311','20190317','11','0','0',
'0,8','3','3','0.83','3','3','20190310')"
curs.execute(insert_sql)
#提交数据
conn.commit()
#关闭数据库
curs.close()

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
import psycopg2
#连接数据库
conn = psycopg2.connect(database = 'name', user = 'admin', password = '123456', host = '10.10.10.10', port = '5432')
curs=conn.cursor()
#编辑写入数据的sql
insert_sql = 
"insert into table_name
(warehouse_code,storehouse_code,zone_code,picking_zone_code,picking_type,location_code,
gds_id,kunnr,yest_tot_qty,week_tot_qty,month_tot_qty,week_avg_prlab,month_avg_prlab,
present_storage,start_date,end_date,week_flag,unsaleble_flag,super_A,sales_segment,
present_class,week_avg_qty,month_avg_qty,now_level,pred_level,statis_date,
prediction_class,action,target_area)
values
('L121','6','IWDX','IWDX','L','IWDX-001-0101','120339999','S70227820','0','0','2','1','1','1','20190311','20190317','11','0','0','0,7','1','0','0','1','1','20190310','2','2','targ_null'),
('L002','WKQ1','WZ1A','WZ1A','L','WZ0114','10073864791','R5400','5','18','36','300.29',
'372.57','351','20190311','20190317','11','0','0',
'0,8','3','3','0.83','3','3','20190310')"
curs.execute(insert_sql)
#提交数据
conn.commit()
#关闭数据库
curs.close()