Insert data into MySQL table from Python script

Question

I have a MySQL Table named TBLTEST with two columns ID and qSQL. Each qSQL has SQL queries in it.

I have another table FACTRESTTBL.

There are 10 rows in the table TBLTEST.

For example, On TBLTEST lets take id =4 and qSQL ="select id, city, state from ABC".

How can I insert into the FACTRESTTBL from TBLTEST using python, may be using dictionary?

Thx!

• Leniel Maccaferri: You want to execute the query in column qSQL and insert the result into FACTRESTTBL? What are the columns of FACTRESTTBL?
• Mario: Columns in FACTRESTTBL are id, city

Answer 1

You can use MySQLdb for Python.

Sample code (you'll need to debug it as I have no way of running it here):

#!/usr/bin/python
import MySQLdb
# Open database connection
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Select qSQL with id=4.
cursor.execute("SELECT qSQL FROM TBLTEST WHERE id = 4")
# Fetch a single row using fetchone() method.
results = cursor.fetchone()
qSQL = results[0]
cursor.execute(qSQL)
# Fetch all the rows in a list of lists.
qSQLresults = cursor.fetchall()
for row in qSQLresults:
 id = row[0]
 city = row[1]
 #SQL query to INSERT a record into the table FACTRESTTBL.
 cursor.execute('''INSERT into FACTRESTTBL (id, city)
 values (%s, %s)''',
 (id, city))
 # Commit your changes in the database
 db.commit()
# disconnect from server
db.close()

• theodox: There's also github.com/petehunt/PyMySQL, which is pure python if that matters for distribution purposes.
• Mario: Given query works. Since there are 10 rows in TBLTEST and above query runs if I am trying to execute 1 query at a time. I want to execute multiple queries, say: cursor.execute("SELECT qSQL FROM TBLTEST WHERE id in(4, 5, 6)"), for id=5, qSQL="select id, city, zip from DEF" for id=6, qSQL="select id, city, county from ABC" How would you do that? Thx