python连接postgresql数据库及操作

1.python连接数据库

1.1 安装相关库

pip install psycopg2
pip install pandas 
1
2

1.1 创建连接

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
4

1.1 关闭连接

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

2.python postgresql创建表

2.1 创建连接

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

2.2 创建事务提交函数

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
7
8

2.3 创建表语句并调用函数

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

2.4 关闭连接

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

3.python postgresql批量插入表数据

3.1 创建连接

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

3.2 创建插入表数据函数

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
5
6
7
8
9
10
11
12
13
14
15

3.3 读取表数据，调用函数

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

3.4 关闭连接

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