python - 如何使用python在数据库之间进行ETL？

Question

使用 psycopg2，我能够从一个 PostgreSQL 数据库连接中的表中选择数据，并将其插入到第二个 PostgreSQL 数据库连接中的表中。

但是，我只能通过设置我想要提取的确切特征，并为我尝试插入的每一列写出单独的变量来做到这一点。

有没有人知道以下两者的良好做法:

• 在数据库之间移动整个表，或者
• 迭代功能，而不必为要移动的每一列声明变量
• 或者...？

这是我目前正在使用的脚本，您可以在其中看到特定特征的选择和变量的创建(它有效，但这不是一个实用的方法):

import psycopg2

connDev = psycopg2.connect("host=host1 dbname=dbname1 user=postgres password=*** ")
connQa = psycopg2.connect("host=host2 dbname=dbname2 user=postgres password=*** ")
curDev = connDev.cursor()
curQa = connQa.cursor()

sql = ('INSERT INTO "tempHoods" (nbhd_name, geom) values (%s, %s);')

curDev.execute('select cast(geom as varchar) from "CCD_Neighborhoods" where nbhd_id = 11;')
tempGeom = curDev.fetchone()

curDev.execute('select nbhd_name from "CCD_Neighborhoods" where nbhd_id = 11;')
tempName = curDev.fetchone()

data = (tempName, tempGeom)

curQa.execute (sql, data)


#commit transactions
connDev.commit()
connQa.commit()

#close connections
curDev.close()
curQa.close()
connDev.close()
connQa.close()

另一个注意事项是 python 允许显式使用 SQL 函数/数据类型转换的能力，这对我们来说很重要，因为我们使用 GEOMETRY 数据类型。在上方您可以看到我将其转换为 TEXT，然后将其转储到源表中的现有几何列中 - 这将适用于 MSSQL Server，这是地理空间社区中的一个重要功能...

Answer 1

在您的解决方案中(您的解决方案和您的问题有不同的语句顺序)更改以“sql =”开头的行和“#commit transactions”之前的循环>'评论

sql_insert = 'INSERT INTO "tempHoods" (nbhd_id, nbhd_name, typology, notes, geom) values '
sql_values = ['(%s, %s, %s, %s, %s)']


data_values = []
# you can make this larger if you want
# ...try experimenting to see what works best
batch_size = 100 
sql_stmt = sql_insert + ','.join(sql_values*batch_size) + ';'
for i, row in enumerate(rows, 1):
    data_values += row[:5]
    if i % batch_size == 0:
        curQa.execute (sql_stmt , data_values )
        data_values = []
if (i % batch_size != 0):
    sql_stmt = sql_insert + ','.join(sql_values*(i % batch_size)) + ';'
    curQa.execute (sql_stmt , data_values )

顺便说一句，我认为您不需要 promise 。您不开始任何交易。因此，没有必要提交它们。当然，如果您所做的只是对游标进行一系列选择，则不需要提交游标。