postgresql 不同数据库不同模式下的数据迁移

编写不容易,转载请注明出处谢谢,

数据迁移

因为之前爬虫的时候，一部分数据并没有上传到服务器，在本地。本来用的就是postgresql，也没用多久，数据迁移的时候，也遇到了很多问题，第一次使pg_dump xx > filename进行数据备份迁移的时候，发现恢复后的数据，和原来的数据库，模式一样，后来这种方法就被我舍弃了。

后来想到把原来的数据库中数据使用pandas导出来，再次存入新的数据库中，可能有一点麻烦,但是我觉得这种方法挺好用的。下边就介绍这种方法。

获取需要迁移数据库中模式下的所有表名

import pandas as pd
import psycopg2

# 连接数据库
conn = psycopg2.connect(database='58TC',
                        user='postgres',
                        password='123456',
                        host='127.0.0.1',
                        port=5432)

# 获取模式下的所有表的名字
tables = pd.read_sql_query("select * from pg_tables where schemaname='2019_3_11'",con=conn)
tables.head()

当前模式下的所有表

table_list = tables['tablename']

DataFrame中的数据写入postgresql

此处我借鉴的网上的一种方法,原文是哪里,我已经忘记了，感谢他的分享，下次找到再补上去。因为单独使用df.to_sql速度太慢了,我的数据想还挺大的，使用sqlalchemy和copy语句能大幅度提到写入效率。

# df 写入数据库

import io
import pandas as pd
from sqlalchemy import create_engine

def write_to_table(df, table_name, if_exists='fail'):
    db_engine = create_engine('postgresql://postgres:xxxxxx@XXXXX/***')# 初始化引擎
    # db_engine = create_engine('postgresql://user:password@host/database')# 初始化引擎
    string_data_io = io.StringIO()      # 内存缓冲粗进行读写操作
    df.to_csv(string_data_io, sep='|', index=False)
    pd_sql_engine = pd.io.sql.pandasSQL_builder(db_engine)
    table = pd.io.sql.SQLTable(table_name, pd_sql_engine, frame=df,
                               index=False, if_exists=if_exists,
                               schema = '2019-3-11-particulars')
    table.create()
    string_data_io.seek(0)
    string_data_io.readline()  # remove header
    
    # 连接数据库
    with db_engine.connect() as connection:
        with connection.connection.cursor() as cursor:  # 游标
            copy_cmd = '''COPY "2019-3-11-particulars"."%s" FROM STDIN HEADER DELIMITER '|' CSV''' %table_name
            # copy语句, 2019-3-11-particulars新数据库中的模式名
            print(copy_cmd)
            cursor.copy_expert(copy_cmd, string_data_io)     # 执行语句
        connection.connection.commit()

pd.io.sql.pandasSQL_builder() PandasSQL子类
pd.io.sql.SQLTable() 用于将panda表映射到SQL表

参数说明:

• table_name表名,
• pd_sql_engine sql引擎,
• framedf,
• index,索引
• if_exists,添加方式参数有
  append表存在追加, fail,表存在跳过, replace,表存在删除重建
• schema 模式名

到此为止,基本工作完成,最后就是调用函数,执行迁移

for city_table in city_list:
    # 需要迁移的城市列表
    df = pd.read_sql_query('select * from "2019_3_12"."%s"' % city_table, con=conn)
  
    try:
        write_to_table(df,city_table)
    except Exception as e:
        print('c城市：',city_table,'错误',e)
    print(city_table,'导入完成')

原文链接: https://www.cnblogs.com/liqk/p/10682274.html
转载请说明出处.