MySQL

Package for interfacing with MySQL databases from Julia

PackageEvaluator	Build Status

Table of Contents

• Installation
• Project Status
• Contributing and Questions
• Documentation
  • Functions
  • Types
  • Example

Installation

The package is registered in METADATA.jl and so can be installed with Pkg.add.

julia> Pkg.add("MySQL")

Project Status

The package is tested against the current Julia 1.1 release and nightly on Linux and OS X.

Contributing and Questions

Contributions are very welcome, as are feature requests and suggestions. Please open an issue if you encounter any problems or would just like to ask a question.

Documentation

Functions

MySQL.connect

MySQL.connect(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. Returns a MySQL.Connection object to be passed to other API functions.

Options are passed via dictionary. The available keys are below and a description of the options can be found in the MySQL documentation.

MySQL.API.MYSQL_OPT_CONNECT_TIMEOUT
MySQL.API.MYSQL_OPT_COMPRESS
MySQL.API.MYSQL_OPT_NAMED_PIPE
MySQL.API.MYSQL_INIT_COMMAND
MySQL.API.MYSQL_READ_DEFAULT_FILE
MySQL.API.MYSQL_READ_DEFAULT_GROUP
MySQL.API.MYSQL_SET_CHARSET_DIR
MySQL.API.MYSQL_SET_CHARSET_NAME
MySQL.API.MYSQL_OPT_LOCAL_INFILE
MySQL.API.MYSQL_OPT_PROTOCOL
MySQL.API.MYSQL_SHARED_MEMORY_BASE_NAME
MySQL.API.MYSQL_OPT_READ_TIMEOUT
MySQL.API.MYSQL_OPT_WRITE_TIMEOUT
MySQL.API.MYSQL_OPT_USE_RESULT
MySQL.API.MYSQL_OPT_USE_REMOTE_CONNECTION
MySQL.API.MYSQL_OPT_USE_EMBEDDED_CONNECTION
MySQL.API.MYSQL_OPT_GUESS_CONNECTION
MySQL.API.MYSQL_SET_CLIENT_IP
MySQL.API.MYSQL_SECURE_AUTH
MySQL.API.MYSQL_REPORT_DATA_TRUNCATION
MySQL.API.MYSQL_OPT_RECONNECT
MySQL.API.MYSQL_OPT_SSL_VERIFY_SERVER_CERT
MySQL.API.MYSQL_PLUGIN_DIR
MySQL.API.MYSQL_DEFAULT_AUTH
MySQL.API.MYSQL_OPT_BIND
MySQL.API.MYSQL_OPT_SSL_KEY
MySQL.API.MYSQL_OPT_SSL_CERT
MySQL.API.MYSQL_OPT_SSL_CA
MySQL.API.MYSQL_OPT_SSL_CAPATH
MySQL.API.MYSQL_OPT_SSL_CIPHER
MySQL.API.MYSQL_OPT_SSL_CRL
MySQL.API.MYSQL_OPT_SSL_CRLPATH
MySQL.API.MYSQL_OPT_CONNECT_ATTR_RESET
MySQL.API.MYSQL_OPT_CONNECT_ATTR_ADD
MySQL.API.MYSQL_OPT_CONNECT_ATTR_DELETE
MySQL.API.MYSQL_SERVER_PUBLIC_KEY
MySQL.API.MYSQL_ENABLE_CLEARTEXT_PLUGIN
MySQL.API.MYSQL_OPT_CAN_HANDLE_EXPIRED_PASSWORDS

MySQL.disconnect

MySQL.disconnect(conn::MySQL.Connection)

Disconnect a MySQL.Connection object from the remote database.

MySQL.escape

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

Escape an SQL statement

MySQL.query (deprecated)

Deprecated - see MySQL.Query

MySQL.execute!

MySQL.execute!(conn::MySQL.Connection, sql::String)
MySQL.execute!(stmt::MySQL.Stmt, params)

Execute an SQL statement without returning results (useful for DDL statements, update, delete, etc.)

The SQL can either be passed as either a string or a prepared MySQL statement (see MySQL.Stmt).

MySQL.insertid

MYSQL.insertid(conn::Connection)

Get the insert id of the most recently executed SQL statement.

Types

MySQL.Connection

MySQL.connect(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())

A connection to a MySQL database.

MySQL.Stmt

MySQL.Stmt(conn::MySQL.Connection, sql::String) => MySQL.Stmt

A prepared SQL statement that may contain ? parameter placeholders.

A MySQL.Stmt may then be executed by calling MySQL.execute!(stmt, params) where params is a vector with the values to be bound to the ? placeholders in the original SQL statement. Params must be provided for every ? and will be matched in the same order they appeared in the original SQL statement.

Alternately, a source implementing the Tables.jl interface can be streamed by executing MySQL.execute!(itr, stmt). Each row must have a value for each param.

MySQL.Query

MySQL.Query(conn, sql, kwargs...) => MySQL.Query

Execute an SQL statement and return a MySQL.Query object. Result rows can be iterated as NamedTuples via Table.rows(query) where query is the MySQL.Query object.

Supported Key Word Arguments:

• streaming - Defaults to false. If true, length of the result size is unknown as the result is returned row by row. May be more memory efficient.

To materialize the results as a DataFrame, use MySQL.Query(conn, sql) |> DataFrame.

Example

Connect to a database, query a table, write to a table, then close the database connection.

using MySQL
using DataFrames

conn = MySQL.connect("localhost", "root", "password", db = "test_db")

foo = MySQL.Query(conn, """SELECT COUNT(*) FROM my_first_table;""") |> DataFrame
num_foo = foo[1,1]

my_stmt = MySQL.Stmt(conn, """INSERT INTO my_second_table ('foo_id','foo_name') VALUES (?,?);""")

for i = 1:num_foo
  MySQL.execute!(my_stmt, [i, "foo_$i"])
end

MySQL.disconnect(conn)