CODEWITHSUNDEEP
pythonmysql-python
user64066
user64066

Reputation: 149

MySQL Connector in Python does not allow LOAD DATA INFILE Syntax

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

Upvotes: 4

Views: 10697

Answers (6)

jimmy
jimmy

Reputation: 31

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

Upvotes: 3

Maaz
Maaz

Reputation: 2445

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)

Upvotes: 14

AsAP_Sherb
AsAP_Sherb

Reputation: 1691

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

Upvotes: 1

kdragger
kdragger

Reputation: 446

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.

Upvotes: 4

Matthew Cornell
Matthew Cornell

Reputation: 4184

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';

Upvotes: 1

schemacs
schemacs

Reputation: 2891

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

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

Upvotes: 4

Related Questions