python连接postgresql数据库及操作

1
2
3
pip install psycopg2
pip install pandas

1
2
3
4
5
import psycopg2
import pandas as pd
conn = psycopg2.connect(database="postgres", user="postgres", password="", host="127.0.0.1", port="5432", client_encoding='utf-8')
cursor = conn.cursor()

1
2
3
cursor.close()
conn.close()

1
2
3
4
5
import psycopg2
import pandas as pd
conn = psycopg2.connect(database="mapdb", user="postgres", password="", host="127.0.0.1", port="5432", client_encoding='utf-8')
cursor = conn.cursor()

1
2
3
4
5
6
7
8
9
def func_conn(conn,cursor,sql):
try:
cursor.execute(sql)
conn.commit()
print("successfully")
except Exception as e:
print('error',e)
conn.rollback()

1
2
3
4
5
6
sql= """CREATE TABLE climate
(ID INT PRIMARY KEY NOT NULL,
date date NOT NULL
);"""
func_conn(conn,cursor,sql)

1
2
3
cursor.close()
conn.close()

1
2
3
4
5
6
import psycopg2
import pandas as pd
from io import StringIO
conn = psycopg2.connect(database="mapdb", user="postgres", password="", host="127.0.0.1", port="5432", client_encoding='utf-8')
cursor = conn.cursor()

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
conn = psycopg2.connect(host='127.0.0.1', user="postgres", password="", database="mapdb")
cur = conn.cursor()
def insert_table(data,table_name):
try:
f = StringIO()
data.to_csv(f, sep='t', index=False, header=False)
f.seek(0)
cur.copy_from(f,table_name,columns=data.columns.to_list()) # 将内存对象f中的数据写入数据库，参数columns为所有列的元组
conn.commit()
f.close()
print('成功写入数据库')
except Exception as e:
print(e)
conn.rollback()
f.close()

1
2
3
4
# 注意：插入数据时，数据库中需要存在这张表
data=pd.read_csv('data/data.csv')
insert_table(data,'table_name')

1
2
3
cursor.close()
conn.close()