从Python将数据插入MySQL数据库

I have 2 tables TBL1 and TBL2. TBL1 has 3 columns date, id, nSql. TBL2 has 3 columns date, custId, userId. I have 17 rows in TBL1 with id 1 to 17 (which will grow later). Each nSql has a SQL query in it. For example nSql for id=1 is: "select date, pId as custId, tId as userId from TBL3" For example nSql for id=2 is: "select date, qId as custId, rId as userId from TBL4" ... nSql result is always same 3 columns.

我有2张桌子TBL1和TBL2。 TBL1有3列date,id,nSql。 TBL2有3列date,custId,userId。我在TBL1中有17行,id为1到17(稍后会增长)。每个nSql都有一个SQL查询。例如nSql为ID = 1时: “选择日期,PID作为CUSTID,TID作为用户id从TBL3” 例如为nSql ID = 2是: “选择日期,QID如CUSTID,RID作为用户id从TBL4” ... nSql结果总是相同的3列。

Below query only runs nSql for id =1. So, in TBL2 I have only output for nSql =1. I want result for all nSql. I want my query to run for all nSql not just for id =1.

以下查询仅针对id = 1运行nSql。因此,在TBL2中,我只输出nSql = 1。我想要所有nSql的结果。我希望我的查询运行所有nSql而不仅仅是id = 1。

import MySQLdb

# Open database connection

con=MySQLdb.Connection(host="localhost", user="root", passwd="root", db="test")

# create a cursor object using cursor() method

cur=con.cursor()

selectStatement=("select nSql from TBL1") # I do not want to limit the number of id to select. For example, I do not want: select nSql from TBL1 where id in (1, 2, ..17)
cur.execute(selectStatement)
res=cur.fetchone()
nSql=res[0]
cur.execute(nSql)
reslt=cur.fetchall()
for row in reslt:
    date= row[0]
    custId= row[1]
    userId=row[2]
    insertStatement=("insert into TBL2( date, custId, userId) values ('%s', %d, %d)" % (date, custId, userId))
    cur.execute(insertStatement)
    con.commit()

2 个解决方案

#1

You already fetch the nSql result and loop over it. You need to loop over both:

您已经获取了nSql结果并在其上循环。你需要遍历两个:

cur.execute(selectStatement)
res = cur.fetchall()
for outerrow in res:
    nSql = outerrow[0]
    cur.execute(nSql)
    # rest of your code

#2

Here you are doing:

你在这里做:

res=cur.fetchone()
nSql=res[0]
cur.execute(nSql)

It means you are taking only first ID (because you wrote fetchone()). In this case, ID equals to 1. You can keep calling fetchall() and by using loop, you can access all IDs.

这意味着你只获取第一个ID(因为你写了fetchone())。在这种情况下,ID等于1.您可以继续调用fetchall(),并通过使用循环,您可以访问所有ID。

#1