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?
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
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 cursor.execute(qSQL) # Fetch all the rows in a list of lists. qSQLresults = cursor.fetchall() for row in qSQLresults: id = row city = row #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()