Home

MySQL.jl Documentation

Getting started

The MySQL.jl package provides a client library for Julia to interface with a mysql server. The package can be installed by doing:

] add MySQL

Once installed, you start using the package by making a connection to the mysql database by doing something like:

conn = DBInterface.connect(MySQL.Connection, host, user, passwd)

This utilizes the DBInterface.jl package method connect and passes in MySQL.Connection as the first argument to signal the type of database we're connecting to. DBInterface.connect also supports a host of options like the port to connect to, whether to use a socket, where an options file is located etc. To see the full list of supported keyword arguments, see the help for DBInterface.connect.

Once connected, there are two ways to submit queries to the server:

Both execution methods return a Cursor object that supports the Tables.jl interface, which allows materializing a query resultset in a number of ways, like DataFrame(x), CSV.write("results.csv", x), etc.

MySQL.jl attempts to provide a convenient MySQL.load(table, conn, table_name) function for generically loading Tables.jl-compatible sources into database tables. While the mysql API has some utilities for even making this possible, just note that it can be tricky to do generically in practice due to specific modifications needed in CREATE TABLE and column type statements.

API reference

DBInterface.connect — Function.
DBInterface.connect(MySQL.Connection, host::String, user::String, passwd::String; db::String="", port::Integer=3306, unix_socket::String=API.MYSQL_DEFAULT_SOCKET, client_flag=API.CLIENT_MULTI_STATEMENTS, opts = Dict())

Connect to a MySQL database with provided host, user, and passwd positional arguments. Supported keyword arguments include:

  • db::String="": attach to a database by default
  • port::Integer=3306: connect to the database on a specific port
  • unix_socket::String: specifies the socket or named pipe that should be used
  • found_rows::Bool=false: Return the number of matched rows instead of number of changed rows
  • no_schema::Bool=false: Forbids the use of database.tablename.column syntax and forces the SQL parser to generate an error.
  • compress::Bool=false: Use compression protocol
  • ignore_space::Bool=false: Allows spaces after function names. All function names will become reserved words.
  • local_files::Bool=false: Allows LOAD DATA LOCAL statements
  • multi_statements::Bool=false: Allows the client to send multiple statements in one command. Statements will be divided by a semicolon.
  • multi_results::Bool=false: currently not supported by MySQL.jl
  • init_command="": Command(s) which will be executed when connecting and reconnecting to the server.
  • connect_timeout::Integer: Connect timeout in seconds
  • reconnect::Bool: Enable or disable automatic reconnect.
  • read_timeout::Integer: Specifies the timeout in seconds for reading packets from the server.
  • write_timeout::Integer: Specifies the timeout in seconds for reading packets from the server.
  • data_truncation::Bool: Enable or disable reporting data truncation errors for prepared statements
  • charset_dir::String: character set files directory
  • charset_name::String: Specify the default character set for the connection
  • bind::String: Specify the network interface from which to connect to the database, like "192.168.8.3"
  • max_allowed_packet::Integer: The maximum packet length to send to or receive from server. The default is 16MB, the maximum 1GB.
  • net_buffer_length::Integer: The buffer size for TCP/IP and socket communication. Default is 16KB.
  • named_pipe::Bool: For Windows operating systems only: Use named pipes for client/server communication.
  • protocol::MySQL.API.mysql_protocol_type: Specify the type of client/server protocol. Possible values are: MySQL.API.MYSQL_PROTOCOL_TCP, MySQL.API.MYSQL_PROTOCOL_SOCKET, MySQL.API.MYSQL_PROTOCOL_PIPE, MySQL.API.MYSQL_PROTOCOL_MEMORY.
  • ssl_key::String: Defines a path to a private key file to use for TLS. This option requires that you use the absolute path, not a relative path. If the key is protected with a passphrase, the passphrase needs to be specified with passphrase keyword argument.
  • passphrase::String: Specify a passphrase for a passphrase-protected private key, as configured by the ssl_key keyword argument.
  • ssl_cert::String: Defines a path to the X509 certificate file to use for TLS. This option requires that you use the absolute path, not a relative path.
  • ssl_ca::String: Defines a path to a PEM file that should contain one or more X509 certificates for trusted Certificate Authorities (CAs) to use for TLS. This option requires that you use the absolute path, not a relative path.
  • ssl_capath::String: Defines a path to a directory that contains one or more PEM files that should each contain one X509 certificate for a trusted Certificate Authority (CA) to use for TLS. This option requires that you use the absolute path, not a relative path. The directory specified by this option needs to be run through the openssl rehash command.
  • ssl_cipher::String: Defines a list of permitted ciphers or cipher suites to use for TLS, like "DHE-RSA-AES256-SHA"
  • ssl_crl::String: Defines a path to a PEM file that should contain one or more revoked X509 certificates to use for TLS. This option requires that you use the absolute path, not a relative path.
  • ssl_crlpath::String: Defines a path to a directory that contains one or more PEM files that should each contain one revoked X509 certificate to use for TLS. This option requires that you use the absolute path, not a relative path. The directory specified by this option needs to be run through the openssl rehash command.
  • ssl_verify_server_cert::Bool: Enables (or disables) server certificate verification.
  • ssl_enforce::Bool: Whether to force TLS
  • default_auth::String: Default authentication client-side plugin to use.
  • connection_handler::String: Specify the name of a connection handler plugin.
  • plugin_dir::String: Specify the location of client plugins. The plugin directory can also be specified with the MARIADBPLUGINDIR environment variable.
  • secure_auth::Bool: Refuse to connect to the server if the server uses the mysqloldpassword authentication plugin. This mode is off by default, which is a difference in behavior compared to MySQL 5.6 and later, where it is on by default.
  • server_public_key::String: Specifies the name of the file which contains the RSA public key of the database server. The format of this file must be in PEM format. This option is used by the cachingsha2password client authentication plugin.
  • read_default_file::Bool: only the default option files are read
  • option_file::String: the argument is interpreted as a path to a custom option file, and only that option file is read.
  • read_default_group::Bool: only the default option groups are read from specified option file(s)
  • option_group::String: it is interpreted as a custom option group, and that custom option group is read in addition to the default option groups.
source
DBInterface.close! — Function.
DBInterface.close!(conn::MySQL.Connection)

Close a MySQL.Connection opened by DBInterface.connect.

source
DBInterface.close!(cursor)

Close a cursor. No more results will be available.

source
DBInterface.close!(stmt)

Close a prepared statement and free any underlying resources. The statement should not be used in any way afterwards.

source
DBInterface.close!(cursor)

Close a cursor. No more results will be available.

source
MySQL.escape — Function.
MySQL.escape(conn::MySQL.Connection, str::String) -> String

Escapes a string using mysql_real_escape_string(), returns the escaped string.

source
DBInterface.execute — Function.
DBInterface.execute(conn::MySQL.Connection, sql) => MySQL.TextCursor

Execute the SQL sql statement with the database connection conn. Parameter binding is only supported via prepared statements, see ?DBInterface.prepare(conn, sql). Returns a Cursor object, which iterates resultset rows and satisfies the Tables.jl interface, meaning results can be sent to any valid sink function (DataFrame(cursor), CSV.write("results.csv", cursor), etc.). Specifying mysql_store_result=false will avoid buffering the full resultset to the client after executing the query, which has memory use advantages, though ties up the database server since resultset rows must be fetched one at a time.

source
DBInterface.execute(stmt, params; mysql_store_result=true) => MySQL.Cursor

Execute a prepared statement, optionally passing params to be bound as parameters (like ? in the sql). Returns a Cursor object, which iterates resultset rows and satisfies the Tables.jl interface, meaning results can be sent to any valid sink function (DataFrame(cursor), CSV.write("results.csv", cursor), etc.). Specifying mysql_store_result=false will avoid buffering the full resultset to the client after executing the query, which has memory use advantages, though ties up the database server since resultset rows must be fetched one at a time.

source
DBInterface.prepare — Function.
DBInterface.prepare(conn::MySQL.Connection, sql) => MySQL.Statement

Send a sql SQL string to the database to be prepared, returning a MySQL.Statement object that can be passed to DBInterface.execute(stmt, args...) to be repeatedly executed, optionally passing args for parameters to be bound on each execution.

Note that DBInterface.close!(stmt) should be called once statement executions are finished. Apart from freeing resources, it has been noted that too many unclosed statements and resultsets, used in conjunction with streaming queries (i.e. mysql_store_result=false) has led to occasional resultset corruption.

source
DBInterface.lastrowid — Function.
DBInterface.lastrowid(c::MySQL.TextCursor)

Return the last inserted row id.

source
DBInterface.lastrowid(c::MySQL.Cursor)

Return the last inserted row id.

source