Python: MySQLdb LOAD DATA INFILE silently fails

Python:MySQLdb INFILE默默地加载数据失败

问题 (Question)

I am attempting to use a Python script to import a csv file into a MySQL database. It seems to fail silently.

Here is my code:

#!/usr/bin/python

import MySQLdb

class DB:
    host = 'localhost'
    user = 'root'
    password = '**************'
    sqldb = 'agriculture'
    conn = None

    def connect(self):
        self.conn =  MySQLdb.connect(self.host,self.user,self.password,self.sqldb )

    def query(self, sql, params=None):
        try:
            cursor = self.conn.cursor()
            if params is not None:
                cursor.execute(sql, params)
            else:
                cursor.execute(sql)
        except (AttributeError, MySQLdb.OperationalError):
            self.connect()
            cursor = self.conn.cursor()
            if params is not None:
                cursor.execute(sql, params)
            else:
                cursor.execute(sql)

        print vars(cursor)
        return cursor

    def load_data_infile(self, f, table, options=""):
        sql="""LOAD DATA LOCAL INFILE '%s' INTO TABLE %s FIELDS TERMINATED BY ',';""" % (f,table)
        self.query(sql)


db = DB()

pathToFile = "/home/ariggi/722140-93805-sqltest.csv"
table_name = "agriculture.degreedays"

db.load_data_infile(pathToFile, table_name)

In an attempt to debug this situation I am dumping the cursor object to the screen within the "query()" method. Here is the output:

{'_result': None, 'description': None, 'rownumber': 0, 'messages': [], '_executed': "LOAD DATA LOCAL INFILE '/home/ariggi/722140-93805-sqltest.csv' INTO TABLE degreedays FIELDS TERMINATED BY ',';", 'errorhandler': >, 'rowcount': 500L, 'connection': , 'description_flags': None, 'arraysize': 1, '_info': 'Records: 500 Deleted: 0 Skipped: 0 Warnings: 0', 'lastrowid': 0L, '_last_executed': "LOAD DATA LOCAL INFILE '/home/ariggi/722140-93805-sqltest.csv' INTO TABLE agriculture.degreedays FIELDS TERMINATED BY ',';", '_warnings': 0, '_rows': ()}

If I take the "_last_executed" query, which is

LOAD DATA LOCAL INFILE '/home/ariggi/722140-93805-sqltest.csv' INTO TABLE agriculture.degreedays FIELDS TERMINATED BY ',';

and run it through the mysql console it works as expected and fills the table with rows. However when I execute this script my database table remains empty.

I am pretty stumped and could use some help.

我试图使用Python脚本csv文件导入到一个MySQL数据库。它似乎默默地失败。

这是我的代码:

#!/usr/bin/python

import MySQLdb

class DB:
    host = 'localhost'
    user = 'root'
    password = '**************'
    sqldb = 'agriculture'
    conn = None

    def connect(self):
        self.conn =  MySQLdb.connect(self.host,self.user,self.password,self.sqldb )

    def query(self, sql, params=None):
        try:
            cursor = self.conn.cursor()
            if params is not None:
                cursor.execute(sql, params)
            else:
                cursor.execute(sql)
        except (AttributeError, MySQLdb.OperationalError):
            self.connect()
            cursor = self.conn.cursor()
            if params is not None:
                cursor.execute(sql, params)
            else:
                cursor.execute(sql)

        print vars(cursor)
        return cursor

    def load_data_infile(self, f, table, options=""):
        sql="""LOAD DATA LOCAL INFILE '%s' INTO TABLE %s FIELDS TERMINATED BY ',';""" % (f,table)
        self.query(sql)


db = DB()

pathToFile = "/home/ariggi/722140-93805-sqltest.csv"
table_name = "agriculture.degreedays"

db.load_data_infile(pathToFile, table_name)

为了调试这种情况我倾倒到屏幕上光标对象在“查询()”的方法。这是输出:

{“_result”:没有,“描述”:没有,“rownumber”:0,“消息”:[],“_executed”:“数据加载本地INFILE / home / ariggi / 722140 - 93805 sqltest。csv的degreedays字段到表终止”,“;”,“errorhandler”:>,“rowcount”:500 l,“连接”:,“description_flags”:没有,“arraysize”:1、��_info”:“删除记录:500:0跳过:0警告:0 ',' lastrowid:0 l,“_last_executed”:“加载数据当地INFILE / home / ariggi / 722140 - 93805 sqltest。csv”表农业。degreedays字段终止”、“;”,“_warnings”:0,“_rows”:()}

如果我拿“_last_executed”查询,这是

LOAD DATA LOCAL INFILE '/home/ariggi/722140-93805-sqltest.csv' INTO TABLE agriculture.degreedays FIELDS TERMINATED BY ',';

并运行它通过mysql控制台是预期和填充的表行。然而当我执行这个脚本的数据库表是空的。

我很难住了,可以使用一些帮助。

最佳答案 (Best Answer)

Try calling db.conn.commit() at the end of your code to make the changes permanent. Python by default does not use the "autocommit" mode, so until you issue a commit the DB module regards your changes as part of an incomplete transaction.

试着打电话db.conn.commit()在您的代码修改永久。Python在默认情况下不使用“自动提交”模式,所以直到你发出commit DB模块作为您的更改一个不完整的事务的一部分。

本文翻译自StackoverFlow,英语好的童鞋可直接参考原文:http://stackoverflow.com/questions/22082092