TypeError: cannot concatenate 'str' and 'tuple' objects while using pymysql.cursors

Question

I work on python. I have a query where I have 3 tables on my db.

I have connected to a db and from a mysql table pulled out all the rows from two columns (idnum,clientname) e.g. (1234,renolds), (1235,renolds2)
Then each idnum has a seperate table e.g. sample_divya_1234;sample_divya_1235, from each of these tables we need to take all emails and lnames. (Note: each idnum has many email and lname records)
All the clientnames taken in step 1 are present in another table sample_divya3, so for each clientname need to pull out fname e.g.(saha, renolds)
Now email,lname,fname must all get dropped into a new table sql_table1

Input Tables

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()

Output required:

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

Mistake is in my highlighted part of my query

""" # cursor.execute("select B.fname,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")"""

Here on trail and got to know that ""Unknown column 'id' in 'where clause"" so can someone help in this where clause

I'd recommend to create stored procedure which accepts source parameters and returns whole outpuut you need. — Akina
– Akina, Commented Jul 25, 2018 at 7:48

Phil · Accepted Answer · 2018-07-25 05:34:35Z

1

It's the string concatenation on the line after for id in data:. id is an array (which is the row returned by select idnum from sample_divya1).

Change your code to read:

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")

Note the id[0] instead of id. This should now contain the required idnum from the query select idnum from sample_divya1.

If you have trouble with this, assign the generated query to a variable and print it before executing it. Inspect it with your eyes, then try to run it from the MySQL command line.

answered Jul 25, 2018 at 5:34

Phil

2,43219 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

RDFozz Over a year ago

Similarly, you'll need to concatenate id[0] for "id" where you have where A.idnum =id

Barbaros Özhan · Accepted Answer · 2019-12-25 20:28:14Z

0

Once:

CREATE VIEW sample_divya_all
SELECT *, '1234' AS tablenum FROM sample_divya_1234
UNION ALL
SELECT *, '1235' AS tablenum FROM sample_divya_1235;

If all partitional tables are Engine=MyISAM, may create a table with Engine=Merge instead.

The query:

SELECT sda.email, sda.lname, sd3.fname
  FROM sample_divya_all sda
  JOIN sample_divya1 sd1 ON sd1.idnum = sda.tablenum
  JOIN sample_divya3 sd3 ON sd3.clientname = sd1.clientname;

edited Dec 25, 2019 at 20:28

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

answered Jul 26, 2018 at 11:24

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Collectives™ on Stack Overflow

TypeError: cannot concatenate 'str' and 'tuple' objects while using pymysql.cursors

Input Tables

Output required:

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Input Tables

Output required:

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related