python - 如何仅将更新的数据从 sql server 迁移到 postgres?而不是全部数据
问题描述
我已经完成了从 SQL Server 到 Postgres 的数据迁移。在这里,我可以比较不同数据库中两个表之间的行差异。我已经向 SQL Server 表添加了一个新行,现在我只希望在 Postgres 表中更新该新行,而不是整个表。
import pandas as pd
from sqlalchemy import create_engine
def ask_mssql():
engine = create_engine('mssql+pyodbc://@' + 'RONY' + '/' + 'testdb' + '?driver=SQL+Server')
return engine
def ask_postgre():
db_string = "postgres+psycopg2://{}:{}@{}/{}".format('postgres', 'rony1234',
'127.0.0.1',
'mockdb')
engine = create_engine(db_string)
return engine
def mssql_syntax(diff, tablename, order):
query = 'select top {} * from {} order by {} desc'.format(diff, tablename, order)
return query
def postgre_syntax(diff, tablename, order):
query = 'select * from {} order by {} desc limit {}'.format(tablename, order, diff)
return query
if __name__ == '__main__':
db_type = input("Please enter client's db: mssql, postgresql, oracle\n")
db_type = db_type.lower()
if db_type == 'mssql':
engine_client = ask_mssql()
db_type_our = input("Please enter your db: mssql, postgresql, oracle\n")
db_type_our = db_type_our.lower()
if db_type_our == 'postgresql':
engine_our = ask_postgre()
table_name = input('\nInput the client table:\n')
query = 'select count(*) from {}'.format(table_name)
df_client = pd.read_sql(query,engine_client)
our_table = input('\nInput the table name in your db\n')
pquery = 'select count(*) from {}'.format(our_table)
df_our = pd.read_sql(pquery,engine_our)
if df_client.values[0][0] != df_our.values[0][0]:
diff = df_client.values[0][0] - df_our.values[0][0]
print('\nDifference of {} rows found'.format(diff))
order_column = input('\nPlease enter a column to order the data on\n')
if db_type == 'mssql':
query = mssql_syntax(diff, table_name, order_column)
if db_type == 'postgresql':
query = postgre_syntax(diff, table_name, order_column)
df = pd.read_sql(query, engine_client)
df.to_sql(our_table, engine_our, if_exists='append')
else:
print('\nTable already Updated.')
解决方案
推荐阅读
- c - strcat 函数不适用于指针
- swift - How do I update UserDefaults after deleting an MKAnnotation?
- java - got 源服务器没有找到目标资源的当前表示或不愿意透露存在的表示
- multithreading - 调用的 C# Thread 方法因工作线程和主 UI 线程而异
- java - 如何在 API 包装器上进行集成测试
- javascript - 为数组中的一个一个数字设置超时 5 秒,但不是一次全部设置
- android - 如何完全关闭android应用程序以便回收所有内存?
- entity-framework-6 - 从 WebJob 使用 EF6 查询 SQL 时,查询失败,因为它试图获取表的复数版本
- python - pyftdi:SPI - CLK空闲状态反转(模式3)
- c++ - 如何在 C++ (UNIX) 中完全阻止用户输入