MySQL/Python LOAD DATA LOCAL INFILE error

elfxx

I am working with OSX 10.10.5 and MySQL 5.7.11 and trying to load txt data to mysql tables. My code was working until I add load_data function.

import pymysql as mdb
import os

def connect(user, password):
    return mdb.connect('localhost', user, password, local_infile=True)

def create_table(name):
    cursor.execute("""drop table if exists %s """ % (name))
    cursor.execute("""create table %s(try VARCHAR(4))""" %(name))

def load_data(file_name, table_name):
    load_data="""load data local infile "%s" into table %s""" % (file_name, table_name)
    cursor.execute(load_data)

con = connect('root','xxx')
cursor = con.cursor()
cursor.execute("use mydb")

table_names = ['dbtry','dbtry1','dbtry2']
file_names = os.listdir("/usr/local/mysql/bin/try")
for i in range(0,3):
    create_table(table_names[i])
    load_data(file_names[i],table_names[i])

con.close()

Then the code gave this error:

ERROR 1148: The used command is not allowed with this MySQL version

And it directed me to the:

 http://dev.mysql.com/doc/refman/5.7/en/load-data-local.html

Then I found "my-default.cnf" in "/usr/local/mysql/support-files" and copied it to "/etc". I added local-infile=1 under [mysqld]. When I asked MySQL:

 SHOW VARIABLES LIKE "local%";

it gave me:

 +---------------+-------+
 | Variable_name | Value |
 +---------------+-------+
 | local_infile  | ON    |
 +---------------+-------+

Finally, when I added local_infile=True to return of the connect function, it raised a new error:

File "/Volumes/RADYO/dbtry.py", line 26, in <module>
  load_data(file_names[i],table_names[i])
File "/Volumes/RADYO/dbtry.py", line 15, in load_data
  cursor.execute(load_data)
File "/Users/elf/Desktop/elif/mysql-env/lib/python2.7/site-packages/pymysql/cursors.py", line 158, in execute
  result = self._query(query)
File "/Users/elf/Desktop/elif/mysql-env/lib/python2.7/site-packages/pymysql/cursors.py", line 308, in _query
  conn.query(q)
File "/Users/elf/Desktop/elif/mysql-env/lib/python2.7/site-packages/pymysql/connections.py", line 820, in query
  self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File "/Users/elf/Desktop/elif/mysql-env/lib/python2.7/site-packages/pymysql/connections.py", line 1002, in _read_query_result
  result.read()
File "/Users/elf/Desktop/elif/mysql-env/lib/python2.7/site-packages/pymysql/connections.py", line 1290, in read
  self._read_load_local_packet(first_packet)
File "/Users/elf/Desktop/elif/mysql-env/lib/python2.7/site-packages/pymysql/connections.py", line 1326, in _read_load_local_packet
  sender.send_data()
File "/Users/elf/Desktop/elif/mysql-env/lib/python2.7/site-packages/pymysql/connections.py", line 1457, in send_data
  raise err.OperationalError(1017, "Can't find file '{0}'".format(self.filename))
pymysql.err.OperationalError: (1017, "Can't find file 'try1.txt'")

But there is the file 'try1.txt' in the directory. How can I solve the problem?

dfb

The problem is likely in the pymysql client. It looks like you should be able to do the following:

def connect(user, password):
    return mdb.connect('localhost', user, password,local_infile=True)

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

LOAD DATA LOCAL INFILE syntax error

From Dev

Error When Using LOAD DATA LOCAL INFILE

From Dev

LOAD DATA LOCAL INFILE - results error

From Dev

Error in Powershell running LOAD DATA LOCAL INFILE

From Dev

Load Data Local Infile with Date

From Dev

Load Data Local Infile with Date

From Dev

Python2.7 MySQL Connector Error on LOAD DATA LOCAL INFILE

From Dev

LOAD DATA LOCAL INFILE with specific column Syntax error

From Dev

LOAD DATA INFILE error 1064

From Dev

LOAD DATA INFILE MYSQL Error

From Dev

LOAD DATA LOCAL INFILE inserting blank record

From Dev

load data local infile with decimal values

From Dev

LOAD DATA LOCAL INFILE large file?

From Dev

Python/MySQL - LOAD DATA LOCAL INFILE

From Dev

MySQL LOAD DATA LOCAL INFILE to a remote server

From Dev

LOAD DATA (LOCAL) INFILE - Invalid authorization specification

From Dev

Python/MySQL - LOAD DATA LOCAL INFILE

From Dev

LOAD DATA LOCAL INFILE - php 5.5 not working

From Dev

Import CSV file with LOAD DATA LOCAL INFILE

From Dev

LOAD DATA LOCAL INFILE inserting blank record

From Dev

mysql LOAD DATA LOCAL INFILE not finding file

From Dev

LOAD DATA LOCAL INFILE lines starting by issue

From Dev

Yii2 LOAD DATA LOCAL INFILE

From Dev

mysql LOAD DATA LOCAL INFILE syntax error when executed via JDBC

From Dev

#1064 - You have an error in your SQL syntax; LOAD DATA LOCAL INFILE

From Dev

MYSQL local data infile syntax error

From Dev

LOAD DATA INFILE - FIELDS TERMINATED error

From Dev

LOAD DATA INFILE - FIELDS TERMINATED error

From Dev

Error 1062 while using load data infile