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!

You want to execute the query in column qSQL and insert the result into FACTRESTTBL? What are the columns of FACTRESTTBL? — Leniel Maccaferri
– Leniel Maccaferri, Commented Feb 13, 2013 at 21:44

Leniel Maccaferri · Accepted Answer · 2013-02-13 23:21:50Z

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

There's also github.com/petehunt/PyMySQL, which is pure python if that matters for distribution purposes.
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

Collectives™ on Stack Overflow

Insert data into MySQL table from Python script

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related