从Python脚本将数据插入MySQL表 [英] Insert data into MySQL table from Python script

问题描述

我有一个名为TBLTEST的MySQL表，具有两列ID和qSQL.每个qSQL都有SQL查询.

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

我还有另一个表FACTRESTTBL.

I have another table FACTRESTTBL.

表TBLTEST中有10行.

There are 10 rows in the table TBLTEST.

例如，在TBLTEST上，让id = 4，而qSQL =从ABC中选择id，城市，州".

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

如何使用python从TBLTEST插入FACTRESTTBL，可能正在使用字典?

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

谢谢！

推荐答案

您可以使用 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()

这篇关于从Python脚本将数据插入MySQL表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！