How to input multiple SQL statements with pymysql

[Red Rice]

Hi all,

I am new to both Python, pymysql and AWS Mysql.

I found youtube tutorial to demo how to connect and query aws mysql with pymysql module. But i am looking for do a sql query with :

1.multiple lines ( select from multiple tables and with multiple where conditions ) 

2.multiple queries ( refer to result of previous queries and do further actions )

The demo used cursor.execute to execute a one-line query, please advise how I can procceed above two requirements, thanks.

[Coyot Linden (Glenn Glazer)]

I think you need to revise your requirements. Accessing a database through an API like pymysql uses an ODBC driver and the access patterns are fundamentally different than using a mySQL client.

The general way of doing things in programmatic access is to:

1) create a database connection
2) from the connection get a cursor
3) construct a single query and pass it to cursor.execute()
4) use one of the cursor fetch methods to store the results in variable(s)

repeat 3) and 4) for each query.

E.g., something like:

conn = pymysql.connections.Connection(...)
cursing = conn.cursor(cursor = DictCursor)
query1 = """SELECT * FROM users WHERE id > 100"""
rows1 = cursing.execute(query1)
if rows1 > 0:
   results1 = cursing.fetchall()
query2 = """SELECT * FROM places WHERE country_code = 'USA'"""
rows2 = cursing.execute(query2)
if rows2 > 0:
   results2 = cursing.fetchall()

and so on. The execute() method returns the number of rows that match the query and fetchall() returns all of the rows. Note that it is perfectly okay to reuse the same cursor over and over after you have fetched the results. Doing it before fetching will wipe out previous data.

After this code executes, the results of each query will be in lists of dicts, one dict per row of the result and in each row dict, the keys are the columns and the values the row value for that column. Something like [{'fname':'Suzy', 'lname':'Smith', 'id:101'...}, {...}, ...] .

As for "multiple lines", multiple tables are queried using whichever you prefer of inner queries or JOINs, they are equivalent. If you are doing something in mySQL that looks like "query1; query2;" break it up into separate queries and get the results separately.

Best,

coyot