Fill table from local file - Error

Asked by Atul

Hello,

I am using python3.2 with mysql.connector. Though it works perfectly fine but for some reason I not being able to fill the table using it.

My code:

cur = con2.cursor()##Connect to destination server with table
cur.execute("TRUNCATE TABLE kakrana_data.mir_page_results")## Clear table before writing
 ##Original query - LOAD DATA LOCAL INFILE "./scoring_input_extend" INTO TABLE kakrana_data.mir_page_results FIELDS TERMINATED BY ',';

cur.execute("LOAD DATA LOCAL INFILE './scoring_input_extend2' INTO TABLE kakrana_data.mir_page_results FIELDS TERMINATED BY ','")

The error which I get is:

Table cleared successfully, update in process
Traceback (most recent call last):
  File "./cl3_combined_v1.5_20nt_miRpage.py", line 575, in <module>
    TableUpload(con2)
  File "./cl3_combined_v1.5_20nt_miRpage.py", line 547, in TableUpload
    cur.execute("LOAD DATA LOCAL INFILE './scoring_input_extend2' INTO TABLE kakrana_data.mir_page_results FIELDS TERMINATED BY ','")
  File "/usr/lib/python2.6/site-packages/mysql/connector/cursor.py", line 314, in execute
    res = self.db().protocol.cmd_query(stmt)
  File "/usr/lib/python2.6/site-packages/mysql/connector/protocol.py", line 136, in deco
    return func(*args, **kwargs)
  File "/usr/lib/python2.6/site-packages/mysql/connector/protocol.py", line 479, in cmd_query
    return self.handle_cmd_result(self.conn.recv())
  File "/usr/lib/python2.6/site-packages/mysql/connector/connection.py", line 179, in recv_plain
    errors.raise_error(buf)
  File "/usr/lib/python2.6/site-packages/mysql/connector/errors.py", line 82, in raise_error
    raise get_mysql_exception(errno,errmsg)
mysql.connector.errors.NotSupportedError: 1148: The used command is not allowed with this MySQL version

Please tell me how can I fill a table with a local file. As, when I run this query from my SQL the table is updated perfectly well. Its just that I am not being able to it from the script (code above). Awaiting Reply.

Thanks

Atul

Question information

Language:
English Edit question
Status:
Solved
For:
MySQL Connector/Python Edit question
Assignee:
No assignee Edit question
Solved by:
Atul
Solved:
Last query:
Last reply:
Revision history for this message
Atul (atulkakrana) said :
#1

Ok. somehow Python2.6 was getting used to run script but the problem is same with python3.

Error:
Traceback (most recent call last):
File "cl3.py", line 575, in <module>
TableUpload(con2)
File "cl3.py", line 547, in TableUpload
cur.execute("LOAD DATA LOCAL INFILE './extend2' INTO TABLE kakrana_data.mir_page_results FIELDS TERMINATED BY ','")
File "/usr/local/lib/python3.2/site-packages/mysql/connector/cursor.py", line 333, in execute
res = self.db().protocol.cmd_query(stmt)
File "/usr/local/lib/python3.2/site-packages/mysql/connector/protocol.py", line 137, in deco
return func(*args, **kwargs)
File "/usr/local/lib/python3.2/site-packages/mysql/connector/protocol.py", line 495, in cmd_query
return self.handle_cmd_result(self.conn.recv())
File "/usr/local/lib/python3.2/site-packages/mysql/connector/connection.py", line 180, in recv_plain
errors.raise_error(buf)
File "/usr/local/lib/python3.2/site-packages/mysql/connector/errors.py", line 84, in raise_error
raise get_mysql_exception(errno,errmsg)
mysql.connector.errors.NotSupportedError: 1148: The used command is not allowed with this MySQL version

I looked into the arguments available for mysql connector but couldnt find 'local_infile' option. Please tell me how upload data to a table using this connector?

Awaiting reply.

AK

Revision history for this message
Geert JM Vanderkelen (geertjmvdk) said :
#2

LOAD DATA LOCAL INFILE is indeed not possible with the current Connector/Python.

The workaround is to create a parse in Python which would transform the CSV lines into INSERT statements (which is basically what the connector would do).

Revision history for this message
Atul (atulkakrana) said :
#3

Hello Geert,

Unfortunately thats what I am doinf right now. Hoping to see Local file upload soon in mysql connector.

Thanks

Atul