Skip to content

Pymysql fail to use LOAD DATA LOCAL INFILE #407

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Closed
bioinfornatics opened this issue Jan 5, 2016 · 6 comments
Closed

Pymysql fail to use LOAD DATA LOCAL INFILE #407

bioinfornatics opened this issue Jan 5, 2016 · 6 comments

Comments

@bioinfornatics
Copy link

Dear,
I would like to use pymysql with sqlachemy (python3) to load a local file. Thus I execute this statement:

    metadata = Base.metadata
    SessionMaker = sessionmaker(bind=engine)
    session = SessionMaker()
    query = "LOAD DATA LOCAL INFILE {} INTO TABLE Sequence_String FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'".format( SS_file_name )
    session.execute( query )
    session.flush()

But an error is raised:

sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'tmp_file_SS_table.txt INTO TABLE Sequence_String FIELDS TERMINATED BY '\t' LINES ' at line 1") [SQL: "LOAD DATA LOCAL INFILE tmp_file_SS_table.txt INTO TABLE Sequence_String FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'"]

I tried with another engine mysqldb instead of pymysql and works as expected.

Any help are welcome
@methane
Copy link
Member

methane commented Jan 6, 2016

Which version of PyMySQL do you use?
Have you specified local_infile=True option to PyMySQL?

Since PyMySQL doesn't use libmysqlclient, PyMySQL's my.cnf support is very limited.
You should specify it by connection's argument, not in my.cnf.

@bioinfornatics
Copy link
Author

Thanks @methane for this quick answer.

So I have done a check on my database to display if local_infile variable is activated

mysql> SHOW GLOBAL VARIABLES LIKE 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | ON    |
+---------------+-------+

As you can see the variable is set to ON but I was unable to load a local file programmatically.

I finally discover how to do it :

engine = create_engine('mysql+pymysql://user:user@localhost/DB?local_infile=1')

Adding ?local_infile=1solve the initial issue but I can't to load huge file.

I tried to set the variable max_allowed_packet from a mysql shell

mysql> select SHOW GLOBAL VARIABLES LIKE 'max_allowed_packet';
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 1048576 |
+--------------------+---------+
1 row in set (0.01 sec)

mysql> SET GLOBAL max_allowed_packet=16777216;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'max_allowed_packet';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 16777216 |
+--------------------+----------+

But that do not fix the problem, I tried to do it programmatically:

engine = create_engine('mysql+pymysql://user:user@localhost/DB?local_infile=1&max_allowed_packet=16777216')

Same here that don't works as expected while the file is not bigger as 1Gb

Thanks for your helps

@methane
Copy link
Member

methane commented Jan 10, 2016

Syntax Error means your SQL may be broken.
It seems you doesn't quote your filename.

@aldeirm2
Copy link

I'm also experiencing errors when I try to LOAD DATA LOCAL INFILE.

I get a:

Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/pymysql/connections.py", line 986, in _write_bytes
    self.socket.sendall(data)
BrokenPipeError: [Errno 32] Broken pipe

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/pymysql/connections.py", line 1457, in send_data
    conn.write_packet(chunk)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/pymysql/connections.py", line 936, in write_packet
    self._write_bytes(data)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/pymysql/connections.py", line 988, in _write_bytes
    raise err.OperationalError(2006, "MySQL server has gone away (%r)" % (e,))
pymysql.err.OperationalError: (2006, "MySQL server has gone away (BrokenPipeError(32, 'Broken pipe'))")

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/pymysql/connections.py", line 986, in _write_bytes
    self.socket.sendall(data)
BrokenPipeError: [Errno 32] Broken pipe

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/pymysql/connections.py", line 1326, in _read_load_local_packet
    sender.send_data()
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/pymysql/connections.py", line 1462, in send_data
    conn.write_packet(b'')
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/pymysql/connections.py", line 936, in write_packet
    self._write_bytes(data)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/pymysql/connections.py", line 988, in _write_bytes
    raise err.OperationalError(2006, "MySQL server has gone away (%r)" % (e,))
pymysql.err.OperationalError: (2006, "MySQL server has gone away (BrokenPipeError(32, 'Broken pipe'))")

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/multiprocessing/process.py", line 254, in _bootstrap
    self.run()
  File "/Users/maldeiri/raw_data_processing/raw_file_extractor.py", line 109, in run
    "LINES TERMINATED BY '\n';".format(file_location=self.delta_stream_load_file_name))
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/pymysql/cursors.py", line 146, in execute
    result = self._query(query)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/pymysql/cursors.py", line 296, in _query
    conn.query(q)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/pymysql/connections.py", line 819, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/pymysql/connections.py", line 1001, in _read_query_result
    result.read()
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/pymysql/connections.py", line 1290, in read
    self._read_load_local_packet(first_packet)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/pymysql/connections.py", line 1328, in _read_load_local_packet
    self.connection._read_packet()  # skip ok packet
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/pymysql/connections.py", line 952, in _read_packet
    (packet_number, self._next_seq_id))
pymysql.err.InternalError: Packet sequence number wrong - got 3 expected 2

I have read in other issues created that PyMYSQL is not thread safe however my connection is not shared and is running in a completely separate Python Process where I initiate the connection and then try to LOAD the data.

Should I create a separate issue?

@methane
Copy link
Member

methane commented Feb 13, 2016

@aldeirm2 Yes, please.
But I can do nothing without sample code reproducing your issue.

@aldeirm2
Copy link

@methane

I created a issue with some sample code.

Thanks

@methane methane mentioned this issue Feb 23, 2016
Merged
@methane methane closed this as completed May 17, 2016
@sharathc3 sharathc3 mentioned this issue Jan 4, 2018
Merged
@github-actions github-actions bot locked as resolved and limited conversation to collaborators Aug 4, 2021
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@methane @aldeirm2 @bioinfornatics