MySQL Connector in Python does not allow LOAD DATA INFILE Syntax

Question

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()

Maaz · Accepted Answer · 2019-03-20 10:31:19Z

14

I just saw this old post, but None of theses answers solved my problem.

I saw here that there is an argument dedicated for LOCAL DATA INFILE: allow_local_infile=True

So it is possible to do:

mysql.connector.connect(user='[username]', password='[pass]', host='[host]', allow_local_infile=True)

answered Mar 20, 2019 at 10:31

Maaz

2,4451 gold badge17 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

abu Over a year ago

That shouldn't be necessary: the same link clearly states that the default value for allow_local_infile argument is already True

schemacs · Accepted Answer · 2013-05-04 04:19:33Z

4

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

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

answered May 4, 2013 at 4:19

schemacs

2,9719 gold badges38 silver badges54 bronze badges

1 Comment

nizam.sp Over a year ago

Also. don't forget to do a commit() on db .

kdragger · Accepted Answer · 2013-10-24 14:19:47Z

4

I had similar problems too. I am new to python and mysqldb. i added a conn.commit and it worked. it seems that it protects the database by requiring a commit.

answered Oct 24, 2013 at 14:19

kdragger

4461 gold badge5 silver badges16 bronze badges

Comments

jimmy · Accepted Answer · 2021-07-20 07:23:30Z

I am using:
MySQL server 8.0.25
mysql-connector-python 8.0.25
Python 3.8.10

@Maaz's answer to this question worked for me. I can't comment, so I thought I would post an answer to show activity on this page.

I also dug a little bit after the fact that adding this argument made a difference despite it shouldn't since the default should be allow_local_infile=True according to @abu and the link provided.

After a quick dive into the constructor of mysql.connector.connect, I found that the default eventually traced to [python packages]\mysql\connector\constants.py, in which it listed defaults for many of the arguments, one of which was 'allow_local_infile': False.

I hope this helps any people finding this problem in the future! :D

Matthew Cornell · Accepted Answer · 2013-09-05 14:38:41Z

1

What version of mysql.connector are you using? I'm on 1.0.11 and I got the feature working. What I did is create data.txt with three lines (1, 2, 3), cd into it, start python 3.3.1, then ran:

import mysql.connector
conn = mysql.connector.connect(database='test', user='test', password='xx')
curs = conn.cursor()
curs.execute('''CREATE TABLE foo (id text)''')
curs.execute('''LOAD DATA INFILE '/<full path to>/data.txt' INTO TABLE foo''')
curs.execute('''SELECT * FROM FOO''')
print(curs.fetchall())
curs.execute('''DROP TABLE foo''')

My output:

[('1',), ('2',), ('3',)]

Note that you'll need to grant the FILE privilege to the 'test' user, e.g.,

GRANT FILE ON *.* TO 'test'@'localhost';

answered Sep 5, 2013 at 14:38

Matthew Cornell

4,2343 gold badges30 silver badges40 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:32:46Z

1

I answered this same question here: https://stackoverflow.com/a/25495823/2364773

in short, you need to add a client flag in the connection using the client_flags parameter

edited May 23, 2017 at 12:32

CommunityBot

11 silver badge

answered Aug 25, 2014 at 23:31

AsAP_Sherb

1,6911 gold badge13 silver badges16 bronze badges

Collectives™ on Stack Overflow

MySQL Connector in Python does not allow LOAD DATA INFILE Syntax

6 Answers 6

1 Comment

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related