Python 中的 MySQL 连接器不允许 LOAD DATA INFILE 语法 [英] MySQL Connector in Python does not allow LOAD DATA INFILE Syntax

问题描述

我正在尝试将文本文件发送到 MySQL 数据库中.我正在尝试使用 python 3.2 中的 mysql 连接器来做到这一点.问题是关于 LOAD DATA INFILE 语法.你可以在上面找到我的代码.我的第一个问题是无论如何都可以解决这个问题.请注意，我尝试过 local-infile =1 选项，而 Python 不允许此选项.其次，有没有其他方法可以将这些数据作为一个块发送到mysql数据库中?

I' m trying to send a text file into MySQL database. I am trying to do this with mysql connector in python 3.2. The problem is about LOAD DATA INFILE syntax. You can find my code above. My first question is is there anyway to solve this problem. Note that I have tried local-infile =1 option and Python does not allow this option. Second, is there any other way to send this data as a block into the mysql database?

from __future__ import print_function
import os
import mysql.connector
from mysql.connector import errorcode
config = {
    'user':'root',
    'password':'3778',
##  'host':'localhost',
#   'database':'microstructure',
#    'local-infile':'1',
    }


DB_NAME = 'EURUSD'
TABLES ={}
TABLES['microstructure']=(
    "CREATE TABLE `microstructure` ("
   # "  `p_id` int NOT NULL AUTO_INCREMENT,"
    "  `ticker` varchar(255),"
    "  `time` date,"
    "  `last_price` decimal(6,3)"
    ") ENGINE=InnoDB")

TABLES['cumulative']=(
    "CREATE TABLE `cumulative` ("
    "  `p_id` int NOT NULL AUTO_INCREMENT,"
    "  `ticker` varchar(255),"
    "  `time` date,"
    "  `last_price` decimal(6,3),"
    "  PRIMARY KEY(`p_id`)"
    ") ENGINE=InnoDB")

cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
path_txt = 'C:/Users/ibrahim/Desktop/testfile.txt'

def create_database(cursor):
    try:
        cursor.execute(
            "CREATE DATABASE IF NOT EXISTS {} DEFAULT CHARACTER SET 'utf8'".format(DB_NAME))
    except mysql.connector.Error as err:
            print("Failed creating database: {}".format(err))
            exit(1)
try:
    cnx.database = DB_NAME
except mysql.connector.Error as err:
    if err.errno == errorcode.ER_BAD_DB_ERROR:
        create_database(cursor)
        cnx.database=DB_NAME
    else:
        print(err)
        exit(1)

for name, ddl in TABLES.items():
    try:
        print("Creating table {}: ".format(name), end ='')
        cursor.execute(ddl)
    except mysql.connector.Error as err:
        if err.errno == errorcode.ER_TABLE_EXISTS_ERROR:
            print("Already exists")
        else:
            print(err)

    else:
        print("OK")

cursor.execute("SET @@global.local_infile = 1")

cursor.execute("LOAD DATA LOCAL INFILE 'testfile.txt' into table microstructure")

os.system("start")

cursor.close()        

推荐答案

当我在 MySQLdb 中使用它来启用 LOAD DATA LOCAL INFILE 功能:

When in MySQLdb I use this to enable LOAD DATA LOCAL INFILE feature:

MySQLdb.connect(..., local_infile=True)

这篇关于Python 中的 MySQL 连接器不允许 LOAD DATA INFILE 语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！