python - 将行从数据库插入另一个数据库

Question

我正在尝试使用 pymysql 将一行从数据库服务器插入到不同环境中的另一个数据库服务器

prod_conn = pymysql.connect(
        prod_common_db_endpoint,
        prod_user,
        prod_password,
        prod_common_table_name)

prod_cursor = prod_conn.cursor()


prod_ConnectFirm_table = prod_cursor.execute("select * from Common.ConnectFirm where FirmId = " + prod_source_firm + ";")
for row in prod_ConnectFirm_table:
    ConnectFirmId = int(row[1])
    ConnectFirmName = (row[2])
    FirmId = int(row[3])
    isDeleted = (row[4])
    tags = (row[5])
    createdDate = (row[6])
    createdBy = int(row[7])
    editedDate = (row[8])
    editedBy = int(row[9])


conn = pymysql.connect(target_common_db_endpoint, user, password, common_schema_name)

cursor = conn.cursor()

cursor.execute("INSERT INTO Common.ConnectFirm(ConnectFirmId, ConnectFirmName, editedDate) VALUES(" + ConnectFirmId + ", " + ConnectFirmName + ", " + editedDate + ", ")")
conn.commit()
print("error inserting")

它失败给我这个错误

Traceback (most recent call last):
  File "sql_test_3_d.py", line 109, in <module>
    for row in prod_orionConnectFirm_table:
TypeError: 'int' object is not iterable

Answer 1

prod_ConnectFirm_table 将包含执行状态，它是一个不能超过的整数值。因此在执行查询后:

prod_ConnectFirm_table = prod_cursor.execute("select * from Common.ConnectFirm where FirmId = " + prod_source_firm + ";")

您需要从光标获取数据:

data = prod_cursor.fetchall()

“data”将包含您解析的元组形式的查询结果。