MySQL console runs the query, Python doesn't

Question

I have thoroughly searched for a solution but couldn't find any. Issue is that I can run below query successfully in MySQL console while when I try to run it using Python it gives error. I'm able to run other sql queries using python but not this one.

Query -

    qry = "UPDATE test.flight_track e, 
           (SELECT c.connection,COUNT(c.bag_seal_number) bag_count 
           FROM 
           (SELECT a.bag_seal_number, a.status_code, a.connection, a.scanned_datetime 
           FROM 
           livedata.livebag a 
           WHERE a.auto_incr IN (SELECT MAX(b.auto_incr) FROM livedata.livebag b 
           GROUP BY b.bag_seal_number)) c 
           WHERE c.status_code = 'In Transit' 
           GROUP BY c.connection) d 
           SET e.bag_count = d.bag_count 
           WHERE e.conn_id = d.connection"

    cursor.execute(qry)
    db.commit()

Error-

    _mysql_exceptions.OperationalError: (1046, 'No database selected')

Please edit this so the code is readable. To make a code block jump down two spaces and indent over 4 spaces. Paste the code in. As it stands now this hard to read Edit: much nicer formatting now — Peter Steele
– Peter Steele, Commented Apr 5, 2017 at 13:20
Is this your complete python code ? Do you have connection part ? — Satish Prakash Garg
– Satish Prakash Garg, Commented Apr 5, 2017 at 13:25
I'm sorry for that, it was first time I posted any question. It's readable now. — Bhaskar Choudhary
– Bhaskar Choudhary, Commented Apr 5, 2017 at 13:26
Can we see a sample of your connection file? Are you sure that you are connected at the time that this query is being executed? — Peter Steele
– Peter Steele, Commented Apr 5, 2017 at 13:26
it's as follow - db = MySQLdb.connect("dashboardcentral.cz3sq5x0ps8m.ap-southeast-1.rds.amazonaws.com","bhas*****","***********",local_infile = 1 ); cursor=db.cursor(). I'm sure I'm connected because for the same connection I'm able to execute other SQL queries. — Bhaskar Choudhary
– Bhaskar Choudhary, Commented Apr 5, 2017 at 13:30

Peter Steele · Accepted Answer · 2017-04-05 13:47:22Z

1

In your query, try explicitly selecting the database and let us know if that works:

USE database_name

It doesn't seem to be an issue with the query itself based on the error that you are receiving.

answered Apr 5, 2017 at 13:47

Peter Steele

2243 silver badges11 bronze badges

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

3 Comments

Bhaskar Choudhary Over a year ago

Thanks ! it worked. Although I'm using two databases in the query but I just added 'USE db' line for one of them (USE test). I don't get this why it happened this way. I had mentioned each db name before each table occurrence.

Peter Steele Over a year ago

@BhaskarChoudhary Glad I was able to help. SQL is weird like that sometimes. I've made it practice to always explicitly name the database I want to use in my queries to avoid this exact situation. Glad it's working now

Bhaskar Choudhary Over a year ago

I'm gonna do the same from now on and thanks again :)

Collectives™ on Stack Overflow

MySQL console runs the query, Python doesn't

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related