我在python上工作。我有一个查询,我的数据库上有3个表。


我已经连接到数据库并从mysql表中拔出了两列(idnum,clientname)的所有行,例如(1234,renolds),(1235,renolds2)
然后每个idnum有一个单独的表,例如sample_divya_1234; sample_divya_1235,我们需要从每个表中获取所有电子邮件和名称。 (注意:每个idnum都有许多电子邮件和lname记录)
步骤1中采用的所有客户端名称都存在于另一个表sample_divya3中,因此对于每个客户端名称都需要提取fname,例如(saha,renolds)
现在email,lname,fname必须全部放入新表sql_table1中


输入表

Sample_divya1:

    +-----+------------+---
    | idnum | clientname |
    +-------+------------+-
    | 1234  | renold    |
    | 1235  | renold1    |
   +-------+------------+

sample_divya_1234:

   +-------------------+----------+
    | email             | lname    |
    +-------------------+----------+
    | [email protected]     | abcd     |
    | [email protected]     | bcda     |
    +-------------------+----------+
    **sample_divya_1235**
    +------------------+-----------+
    | email            | lname     |
    +------------------+-----------+
    | [email protected]    | xyza      |
    | [email protected]    | sai       |
    | [email protected]    | klm       |
    +------------------+-----------+

    sample_divya3:

    +--------+------------+
    | fname  | clientname |
    +--------+------------+
    | saha   | renold     |
    | hasini | renold1      |
    +--------+------------+


PRG:

import pandas as pd
import pymysql
import pymysql.cursors
from sqlalchemy import create_engine
from time import time
import datetime

conn=pymysql.connect(CONNECTDETAILS)
query = "select idnum from sample_divya1"
cursor=conn.cursor()
cursor.execute(query)
data = cursor.fetchall()
cursor.execute("drop table if exists sql_table1")
sql_table = "create table sql_table1(email varchar(128),lname varchar(128),fname varchar(128))"
cursor.execute(sql_table)


for id in data:
    cursor.execute("select A.idnum, B.fname, B.clientname,C.lname,C.email from (select idnum,clientname from sample_divya1)A cross join (select fname,clientname  from sample_divya3 )B where A.clientname=B.clientname cross join (select email, lname from sample_divya_"+id[0]+")C where A.idnum =id")
    data_1=cursor.fetchall()
    conn.commit()
    print data_1
    cursor.executemany("insert into sql_table1 (email,lname,fname) values (?,?,?)",data_1)
    conn.commit()
conn.commit()


需要的输出:

+------------------+----------+--------+
| email            | lname    | fname  |
+------------------+----------+--------+
| [email protected]    | abcd     | saha   |
| [email protected]    | bcda     | saha   |
| [email protected]    | xyza     | hasini |
| [email protected]    | sai      | hasini |
| [email protected]    | klm      | hasini |


错误是我查询中突出显示的部分

“”“#cursor.execute(”选择B.fname,C.lname,C.email(从sample_divya1选择idnum,clientname)A交叉连接(从sample_divya3选择fname,clientname)B其中A.clientname = B.clientname交叉连接(选择电子邮件,来自sample_divya _“ + id [0] +”的lname)C,其中A.idnum = id“)”“”

在这里的足迹,并知道“”“ where子句中的未知列“ id”””
所以有人可以在此where子句中提供帮助

最佳答案

这是for id in data:后一行的字符串连接。 id是一个数组(是select idnum from sample_divya1返回的行)。

将代码更改为:

for id in data:
    cursor.execute("select A.idnum, B.fname, B.clientname,C.lname,C.email from (select idnum,clientname from sample_divya1)A cross join (select fname,clientname  from sample_divya3 )B where A.clientname=B.clientname cross join (select email, lname from sample_divya_"+id[0]+")C where A.idnum =id")


请注意id[0]而不是id。现在,它应包含查询idnum中所需的select idnum from sample_divya1

如果对此有疑问,请在执行变量之前将生成的查询分配给变量并打印它。用眼睛检查它,然后尝试从MySQL命令行运行它。

08-07 16:43