python MySQL db和Cursor无法执行INSERT INT

Question

在Python版本2.7.6中

熊猫0.18.1版

MySQL5.7

import MySQLdb as dbapi
import sys
import csv
import os
import sys, getopt
import pandas as pd

df = pd.read_csv('test.csv')
rows = df.apply(tuple, 1).unique().tolist()

db=dbapi.connect(host=dbServer,user=dbUser,passwd=dbPass)

cur=db.cursor()

for (CLIENT_ID,PROPERTY_ID,YEAR) in rows:

                INSERT_QUERY=("INSERT INTO {DATABASE}.TEST SELECT * FROM {DATABASE}_{CLIENT_ID}.TEST WHERE PROPERTY_ID = {PROPERTY_ID} AND YEAR = {YEAR};".format(
                CLIENT_ID=CLIENT_ID,
                PROPERTY_ID=PROPERTY_ID,
                YEAR=YEAR,
                DATABASE=DATABASE
             ))

                print INSERT_QUERY
                cur.execute(INSERT_QUERY)
                db.query(INSERT_QUERY)

但是，当我在MySQL中检查结果时，这将打印出我要查找的查询，但没有成功返回INSERT INTO的结果

^{pr2}$

但是，如果我只是将这个MySQL查询复制并粘贴到MySQL GUI中，它将毫无问题地执行。任何一位大师都能开悟吗？在

我也尝试了以下方法

cur.execute(INSERT_QUERY, multi=True)

返回错误

TypeError: execute() got an unexpected keyword argument 'multi'

Answer 1

答案是我们需要使用“from”mysql.connector“还有一个数据库提交(). 这是一个很好的例子

http://www.mysqltutorial.org/python-mysql-insert/

import MySQLdb as dbapi
import mysql.connector
import sys
import csv
import os
import sys, getopt
import pandas as pd

df = pd.read_csv('test.csv')
rows = df.apply(tuple, 1).unique().tolist()

db=dbapi.connect(host=dbServer,user=dbUser,passwd=dbPass)

cur=db.cursor()

conn = mysql.connector.connect(host=dbServer,user=dbUser,port=dbPort,password=dbPass)

cursor=conn.cursor()

for (CLIENT_ID,PROPERTY_ID,YEAR) in rows:

                INSERT_QUERY=("INSERT INTO {DATABASE}.TEST SELECT * FROM {DATABASE}_{CLIENT_ID}.TEST WHERE PROPERTY_ID = {PROPERTY_ID} AND YEAR = {YEAR};".format(
                CLIENT_ID=CLIENT_ID,
                PROPERTY_ID=PROPERTY_ID,
                YEAR=YEAR,
                DATABASE=DATABASE
             ))

                print INSERT_QUERY
                cursor.execute(INSERT_QUERY)
                conn.commit()

只有通过提交，才能接受数据库/表的更改