MySQL Server has a feature where you can insert data into a table from a file with the data delimited by commas, tabs, or another delimiter. This is particularly useful when you need to bulk import data, for example when restoring a backup or migrating data from one system to another including from another database product than MySQL. The mysqldump backup utility is an example of a program that supports exporting the data to delimited text files.
The statement to load the data is
LOAD DATA INFILE. By default the file must be server-side and MySQL Server will load it without involving the connections (other than for submitting the query and returning the result). However, there is also an optional keyword
LOCAL that can be used to tell MySQL that a the file is located client-side:
LOAD DATA LOCAL INFILE. It is this local variant that is the topic of this blog.
By default, MySQL Server 8 does not allow loading client-side files using the
LOAD DATA LOCAL INFILE statement. (Similarly the
mysql command-line client has the feature disabled by default.) Why? The reason is that there are security implications enabling the feature. For that reason, it is recommended to disable the feature unless you know you need it, and then only allow it for as short time as possible.
In MySQL Connector/Python, the
mysql.connector module includes the option
allow_local_infile which determines whether MySQL Connector/Python will allow the server to request a client-side file. In version 8.0.15 the default value for
allow_local_infile has been changed from
False to align with the default value for MySQL Server and the
mysql command-line client.
This means that if you attempt to execute
LOAD DATA LOCAL INFILE in MySQL Connector/Python 8.0.15 without explicitly enabling it, a
ProgrammingError exception is raised:
mysql.connector.errors.ProgrammingError: 1148 (42000): The used command is not allowed with this MySQL version
(Admitted, the error message is not particularly good at telling what you are doing wrong.) What do you do, if you need to load client-side data files? These are the steps:
- Enable the
local_infileoption in MySQL Server. This only exists for the global scope. You can temporary enable it with
SET GLOBAL local_infile = ON;, then disable it again when you are done by setting the variable to
allow_local_infilein your MySQL Connector/Python program. I will show an example of how to do this below. Again, it is recommended to only have it enabled when needed.
An example of creating a connection with
allow_local_infile enabled is:
import mysql.connector # Create connection to MySQL db = mysql.connector.connect( option_files="my.ini", allow_local_infile=True )
This example uses an INI formatted configuration file to specify the connection options, and the
allow_local_infile option is added explicitly.
If you are interested in learning more about using
LOAD DATA [LOCAL] INFILE with MySQL Connector/Python, then my book MySQL Connector/Python Revealed (Apress) has a section describing how to load data from files including an example. The book is available from Apress (print and DRM free ePub+PDF), Amazon (print and Kindle), Barnes & Noble (print), and others.