我在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命令行运行它。