python+pgsql+psycopg2实现一键数据库生成
1.tips
Navcat转储sql文件,存在主键自增,加入下面代码。
CREATE SEQUENCE IF NOT EXISTS t 表名_id_seq START 1000;
手动导入表不全时,也请检查主键自增情况,加入上面代码。
import psycopg2
from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT
import os
'''
1.file_dir:文件路径
2.文件为.sql文件,文件名为数据库名([数据库名].sql),文件内容为创建表和插入数据的sql语句
3.主方法中变量根据需要修改
4.操作数据库:postgresql
'''
def insertOperate(user, pwd, port, host, file_dir):
# 文件名称列表
sql_script_name = []
# 数据库名称列表
db_name = []
# 读取文件名
for root, dirs, files in os.walk(file_dir, topdown=False):
sql_script_name = files
# 获取数据库名
for i in sql_script_name:
db_name.append(i.split('.')[0])
# 创建数据库
for i in db_name:
con = psycopg2.connect(port=port, host=host, user=user, password=pwd)
con.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
cursor = con.cursor()
cursor.execute("DROP DATABASE IF EXISTS {}".format(i))
cursor.execute("CREATE DATABASE {}".format(i))
con.commit()
cursor.close()
con.close()
# 创建表并插入数据
for i in db_name:
con1 = psycopg2.connect(database=i, port=port, host=host, user=user, password=pwd)
con1.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
cursor1 = con1.cursor()
name = file_dir + "/" + i + ".sql"
with open(name, 'r', True, 'UTF-8') as f:
cursor1.execute(f.read())
con1.commit()
cursor1.close()
con1.close()
if __name__ == '__main__':
user = "postgres"
pwd = "123456"
port = 5432
host = "localhost"
file_dir = "E:/pythonProject1/sql脚本"
insertOperate(user, pwd, port, host, file_dir)