If you encounter any errors, please first carry out the checks shown below:

Once you have installed MySQL Connector/C++ you can carry out a quick test to check the installation. To do this you can compile and run one of the example programs, such as examples/standalone_example.cpp. This example is discussed in more detail later, but for now you can use it to test the connector has been correctly installed. This procedure assumes you have a working MySQL Server that you can connect to.

The basic steps for building the connector on Windows are the same as for Unix. It is important to use CMake 2.6.2 or newer to generate build files for your compiler and to invoke the compiler.

CMake makes it easy for you to try other compilers. However, you may experience compile warnings, compile errors or linking issues not detected by Visual Studio. Patches are gratefully accepted to fix issues with other compilers.

Consult the CMake manual or check cmake --help to find out which build systems are supported by your CMake version:

C:\>cmake --helpcmake version 2.6-patch 2Usage[...]GeneratorsThe following generators are available on this platform:  Borland Makefiles   = Generates Borland makefiles.  MSYS Makefiles  = Generates MSYS makefiles.  MinGW Makefiles = Generates a make file for use with mingw32-make.  NMake Makefiles = Generates NMake makefiles.  Unix Makefiles  = Generates standard UNIX makefiles.  Visual Studio 6 = Generates Visual Studio 6 project files.  Visual Studio 7 = Generates Visual Studio .NET 2002 project files.  Visual Studio 7 .NET 2003   = Generates Visual Studio .NET 2003 project files.  Visual Studio 8 2005 = Generates Visual Studio .NET 2005 project files.  Visual Studio 8 2005 Win64  = Generates Visual Studio .NET 2005 Win64 project files.  Visual Studio 9 2008 = Generates Visual Studio 9 2008 project fil  Visual Studio 9 2008 Win64  = Generates Visual Studio 9 2008 Win64 proje files.[...]

It is likely that your CMake binary will support more compilers, known by CMake as generators, than supported by MySQL Connector/C++. We have built the connector using the following generators:

Please see the building instructions for Unix, Solaris and Mac OS X for troubleshooting and configuration hints.

The steps to build the connector are given below:

An application that uses MySQL Connector/C++ can be either statically or dynamically linked to the MySQL Connector/C++ libraries. MySQL Connector/C++ is usually statically linked to the underlying MySQL Client Library (or Connector/C). Note, that unless otherwise stated, reference to the MySQL Client Library is also taken to include Connector/C, which is a separately packaged, stand alone version of the MySQL Client Library. From MySQL Connector/C++ version 1.1.0 it is possible to also dynamically link to the underlying MySQL Client Library. The ability of MySQL Connector/C++ to dynamically link to MySQL Client Library is not enabled by default, and enabling this feature is done through a compile time option, when compiling the MySQL Connector/C++ source code.

To use the ability to dynamically link the client library to MySQL Connector/C++, define the MYSQLCLIENT_STATIC_BINDING:BOOL when building the MySQL Connector/C++ source code:

rm CMakeCache.txtcmake -DMYSQLCLIENT_STATIC_BINDING:BOOL=1 .make cleanmakemake install

Note that precompiled binaries of MySQL Connector/C++ use static binding with the client library by default.

Now, in your application, when creating a connection, MySQL Connector/C++ will select and load a client library at runtime. It will choose the client library by searching defined locations and environment variables depending on the host operating system. It is also possible when creating a connection in an application to define an absolute path to the client library to be loaded at runtime. This can be convenient if you have defined a standard location from which you want the client library to be loaded. This is sometimes done to circumvent possible conflicts with other versions of the client library that may be located on the system.

A connection to MySQL is established by retrieving an instance of sql::Connection from a sql::mysql::MySQL_Driver object. A sql::mysql::MySQL_Driver object is returned by sql::mysql::MySQL_Driver::get_mysql_driver_instance().

sql::mysql::MySQL_Driver *driver;sql::Connection *con;driver = sql::mysql::MySQL_Driver::get_mysql_driver_instance();con = driver->connect("tcp://127.0.0.1:3306", "user", "password");delete con;

Make sure that you free the sql::Connection object as soon as you do not need it any more. But do not explicitly free the connector object!

For running simple queries, you can use the methods sql::Statement::execute(), sql::Statement::executeQuery() and sql::Statement::executeUpdate(). Use the method sql::Statement::execute() if your query does not return a result set or if your query returns more than one result set. See the examples/ directory for more on this.

sql::mysql::MySQL_Driver *driver;sql::Connection *con;sql::Statement *stmt;driver = sql::mysql::get_mysql_driver_instance();con = driver->connect("tcp://127.0.0.1:3306", "user", "password");stmt = con->createStatement();stmt->execute("USE " EXAMPLE_DB);stmt->execute("DROP TABLE IF EXISTS test");stmt->execute("CREATE TABLE test(id INT, label CHAR(1))");stmt->execute("INSERT INTO test(id, label) VALUES (1, 'a')");delete stmt;delete con;

Note that you have to free sql::Statement and sql::Connection objects explicitly using delete.

The API for fetching result sets is identical for (simple) statements and prepared statements. If your query returns one result set, use sql::Statement::executeQuery() or sql::PreparedStatement::executeQuery() to run your query. Both methods return sql::ResultSet objects. The preview version does buffer all result sets on the client to support cursors.

// ...sql::Connection *con;sql::Statement *stmt;sql::ResultSet  *res;// ...stmt = con->createStatement();// ...res = stmt->executeQuery("SELECT id, label FROM test ORDER BY id ASC");while (res->next()) {  // You can use either numeric offsets...  cout << "id = " << res->getInt(1); // getInt(1) returns the first column  // ... or column names for accessing results.  // The latter is recommended.  cout << ", label = '" << res->getString("label") << "'" << endl;}delete res;delete stmt;delete con;

Note that you have to free sql::Statement, sql::Connection and sql::ResultSet objects explicitly using delete.

The usage of cursors is demonstrated in the examples contained in the download package.

If you are not familiar with Prepared Statements on MySQL have an extra look at the source code comments and explanations in the file examples/prepared_statement.cpp.

sql::PreparedStatement is created by passing an SQL query to sql::Connection::prepareStatement(). As sql::PreparedStatement is derived from sql::Statement, you will feel familiar with the API once you have learned how to use (simple) statements (sql::Statement). For example, the syntax for fetching results is identical.

// ...sql::Connection*con;sql::PreparedStatement*prep_stmt// ...prep_stmt = con->prepareStatement("INSERT INTO test(id, label) VALUES (?, ?)");prep_stmt->setInt(1, 1);prep_stmt->setString(2, "a");prep_stmt->execute();prep_stmt->setInt(1, 2);prep_stmt->setString(2, "b");prep_stmt->execute();delete prep_stmt;delete con;

As usual, you have to free sql::PreparedStatement and sql::Connection objects explicitly.

The following code shows a complete example of how to use MySQL Connector/C++:

/* Copyright 2008, 2010, Oracle and/or its affiliates. All rights reserved.This program is free software; you can redistribute it and/or modifyit under the terms of the GNU General Public License as published bythe Free Software Foundation; version 2 of the License.There are special exceptions to the terms and conditions of the GPLas it is applied to this software. View the full text of theexception in file EXCEPTIONS-CONNECTOR-C++ in the directory of thissoftware distribution.This program is distributed in the hope that it will be useful,but WITHOUT ANY WARRANTY; without even the implied warranty ofMERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See theGNU General Public License for more details.You should have received a copy of the GNU General Public Licensealong with this program; if not, write to the Free SoftwareFoundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA*//* Standard C++ includes */#include <stdlib.h>#include <iostream>/*  Include directly the different  headers from cppconn/ and mysql_driver.h + mysql_util.h  (and mysql_connection.h). This will reduce your build time!*/#include "mysql_connection.h"#include <cppconn/driver.h>#include <cppconn/exception.h>#include <cppconn/resultset.h>#include <cppconn/statement.h>using namespace std;int main(void){cout << endl;cout << "Running 'SELECT 'Hello World!' »   AS _message'..." << endl;try {  sql::Driver *driver;  sql::Connection *con;  sql::Statement *stmt;  sql::ResultSet *res;  /* Create a connection */  driver = get_driver_instance();  con = driver->connect("tcp://127.0.0.1:3306", "root", "root");  /* Connect to the MySQL test database */  con->setSchema("test");  stmt = con->createStatement();  res = stmt->executeQuery("SELECT 'Hello World!' AS _message");  while (res->next()) { cout << "\t... MySQL replies: "; /* Access column data by alias or column name */ cout << res->getString("_message") << endl; cout << "\t... MySQL says it again: "; /* Access column fata by numeric offset, 1 is the first column */ cout << res->getString(1) << endl;  }  delete res;  delete stmt;  delete con;} catch (sql::SQLException &e) {  cout << "# ERR: SQLException in " << __FILE__;  cout << "(" << __FUNCTION__ << ") on line " » << __LINE__ << endl;  cout << "# ERR: " << e.what();  cout << " (MySQL error code: " << e.getErrorCode();  cout << ", SQLState: " << e.getSQLState() << " )" << endl;}cout << endl;return EXIT_SUCCESS;}

The following code shows a complete example of how to use MySQL Connector/C++:

/* Copyright 2008, 2010, Oracle and/or its affiliates. All rights reserved.This program is free software; you can redistribute it and/or modifyit under the terms of the GNU General Public License as published bythe Free Software Foundation; version 2 of the License.There are special exceptions to the terms and conditions of the GPLas it is applied to this software. View the full text of theexception in file EXCEPTIONS-CONNECTOR-C++ in the directory of thissoftware distribution.This program is distributed in the hope that it will be useful,but WITHOUT ANY WARRANTY; without even the implied warranty ofMERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See theGNU General Public License for more details.You should have received a copy of the GNU General Public Licensealong with this program; if not, write to the Free SoftwareFoundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA*//* Standard C++ includes */#include <stdlib.h>#include <iostream>/*  Include directly the different  headers from cppconn/ and mysql_driver.h + mysql_util.h  (and mysql_connection.h). This will reduce your build time!*/#include "mysql_connection.h"#include <cppconn/driver.h>#include <cppconn/exception.h>#include <cppconn/resultset.h>#include <cppconn/statement.h>#include <cppconn/prepared_statement.h>using namespace std;int main(void){cout << endl;cout << "Let's have MySQL count from 10 to 1..." << endl;try {  sql::Driver *driver;  sql::Connection *con;  sql::Statement *stmt;  sql::ResultSet *res;  sql::PreparedStatement *pstmt;  /* Create a connection */  driver = get_driver_instance();  con = driver->connect("tcp://127.0.0.1:3306", "root", "root");  /* Connect to the MySQL test database */  con->setSchema("test");  stmt = con->createStatement();  stmt->execute("DROP TABLE IF EXISTS test");  stmt->execute("CREATE TABLE test(id INT)");  delete stmt;  /* '?' is the supported placeholder syntax */  pstmt = con->prepareStatement("INSERT INTO test(id) VALUES (?)");  for (int i = 1; i <= 10; i++) { pstmt->setInt(1, i); pstmt->executeUpdate();  }  delete pstmt;  /* Select in ascending order */  pstmt = con->prepareStatement("SELECT id FROM test ORDER BY id ASC");  res = pstmt->executeQuery();  /* Fetch in reverse = descending order! */  res->afterLast();  while (res->previous()) cout << "\t... MySQL counts: " << res->getInt("id") << endl;  delete res;  delete pstmt;  delete con;} catch (sql::SQLException &e) {  cout << "# ERR: SQLException in " << __FILE__;  cout << "(" << __FUNCTION__ << ") on line " » << __LINE__ << endl;  cout << "# ERR: " << e.what();  cout << " (MySQL error code: " << e.getErrorCode();  cout << ", SQLState: " << e.getSQLState() << » " )" << endl;}cout << endl;return EXIT_SUCCESS;}

Stored procedures can be called using both Statements and Prepared Statements. This tutorial looks at calling stored procedures using Statements. The following tutorial Section 22.4.6.2, "Tutorial: Calling Stored Procedures with Prepared Statements in MySQL Connector/C++" will cover the use of Prepared Statements.

You can construct and call various combinations of stored procedures:

The following stored procedures illustrate each of these scenarios.

The following routine adds a country into the World database, but does not return a result. This corresponds to Scenario 1 above.

CREATE PROCEDURE add_country (IN country_code CHAR(3), IN country_name CHAR(52),IN continent_name CHAR(30))BEGIN   INSERT INTO Country(Code, Name, Continent) VALUES (country_code, country_name, continent_name);END

The next routine returns the population of a specified country, and corresponds to Scenario 2 above:

CREATE PROCEDURE get_pop (IN country_name CHAR(52), OUT country_pop INT(11))BEGIN   SELECT Population INTO country_pop FROM Country WHERE Name = country_name;END

The next routine is an example of a procedure returning a result set containing multiple records. This routine corresponds to Scenario 3 above.

CREATE PROCEDURE get_data ()BEGIN SELECT Code, Name, Population, Continent FROM Country WHERE Continent = "Oceania" AND Population < 10000; SELECT Code, Name, Population, Continent FROM Country WHERE Continent = "Europe" AND Population < 10000; SELECT Code, Name, Population, Continent FROM Country WHERE Continent = "North America" AND Population < 10000;END

Enter and test the stored procedures to ensure no errors have been introduced. You are now ready to start writing applications using Connector/C++ that call stored procedures.

Scenario 1 - Stored procedure does not return a result set

The first case illustrates Scenario 1, calling a Stored procedure that does not return a result set.

The code in this case simply creates a statement and then invokes the execute method on it, passing the call to the stored procedure as a parameter. The stored procedure itself does not return a value, although it is important to note there will always be a return value from the call - this is simply the call status. MySQL Connector/C++ handles this status for you, so you do not need code to handle it explicitly. If the call fails for some reason, an exception will be raised, and this will be handled by the catch statement in the code.

Scenario 2 - Stored procedure returns an output parameter

You will now see how to handle a stored procedure that returns an output parameter.

In this scenario, the stored procedure sets an output parameter. This is not returned as such, but needs to be obtained using a query. If running the SQL statements directly, this might be similar to the following:

CALL get_world_pop(@pop);SELECT @pop;

In the C++ code, a similar sequence is carried out. First, the CALL is executed as seen earlier. To obtain the output parameter, an additional query must be executed. This query results in a ResultSet that can then be processed in a while loop. The simplest way to retrieve the data in this case is to use a getString method on the ResultSet, passing the name of the variable to access. In this example _reply is used as a placeholder for the variable and therefore is used as the key to access the correct element of the result dictionary.

Scenario 3 - Stored procedure returns a Result Set

You will now see how to handle a stored procedure that returns a result set.

The code is similar to the examples you have previously seen. The code of particular interest in this case is:

do {   res.reset(stmt->getResultSet());   while (res->next()) {  cout << "Name: " << res->getString("Name") << " Population: " << res->getInt("Population")   << endl;   }} while (stmt->getMoreResults());

The CALL is executed as before, with the results being returned into multiple ResultSets. This is because the Stored Procedure in this case uses multiple SELECT statements. In this example, the output shows that three Result Sets are processed, because there are three SELECT statements in the Stored Procedure. All of the Result Sets have more than one row.

The results are processed using the pattern:

do {   Get Result Set   while (Get Result) {  Process Result   }} while (Get More Result Sets);

Before working through this tutorial, it is recommended you first work through the previous tutorial Section 22.4.6.1, "Tutorial: Calling Stored Procedures with Statements in MySQL Connector/C++".

Scenario 1 - Using a Prepared Statement to prepare a Stored Procedure that does not return a result set

The code is relatively simple, as no processing is required to handle Result Sets. The procedure call, CALL add_country(?,?,?), is made using placeholders for input parameters denoted by '?'. These placeholders are replaced by values using the Prepared Statement's setString method in this case. The for loop is set up to iterate 3 times, as there are three data sets in this example. The same Prepared Statement is executed three times, each time with different input parameters.

Scenario 2 - Using a Prepared Statement to prepare a Stored Procedure that uses an output parameter

In this scenario a different Stored Procedure is going to be used compared to the one used in the tutorial Section 22.4.6.1, "Tutorial: Calling Stored Procedures with Statements in MySQL Connector/C++". This is to illustrate passing an input parameter as well as fetching an output parameter. The stored routine is as follows:

CREATE PROCEDURE get_pop_continent (IN continent_name CHAR(30), OUT continent_pop INT(11))BEGIN SELECT SUM(Population) INTO continent_pop FROM Country WHERE Continent = continent_name;END

In this scenario a Prepared Statement is created that calls the Stored Procedure get_pop_continent. This procedure takes an input parameter, and also returns an output parameter. The approach used is to create another statement that can be used to fetch the output parameter using a SELECT query. Note that when the Prepared Statement is created, the input parameter to the Stored Procedure is denoted by '?'. Prior to execution of Prepared Statement it is necessary to replace this placeholder by an actual value. This is done using methods such as setString and setInt, for example:

pstmt->setString(1,cont_vector[i]);

Although for the query used to obtain the output parameter a single result set is expected, it is important to use the while loop to catch more than one result, to avoid the possibility of the connection becoming unstable.

Scenario 3 - Using a Prepared Statement to prepare a Stored Procedure that returns multiple Result Sets

The code executes the Stored Procedure using a Prepared Statement. The standard do-while construct is used to ensure that all Result Sets are fetched. In this case the returned values are fetched from the Result Sets using the getInt and getString methods.

On UNIX-like systems such as Linux distributions, Solaris, Apple Mac OS X, and FreeBSD, you can download Connector/Python as a tar archive from http://dev.mysql.com/downloads/connector/python/.

To install Connector/Python from the .tar.gz file, download the latest version and follow these steps:

shell> gunzip mysql-connector-python-1.0.6b1.tar.gzshell> tar xf mysql-connector-python-1.0.6b1.tarshell> cd mysql-connector-python-1.0.6b1shell> sudo python setup.py install

On UNIX-like systems, Connector/Python gets installed in the default location /prefix/lib/pythonX.Y/site-packages/, where prefix is the location where Python was installed and X.Y is the version of Python. See How installation works in the Python manual.

If you are not sure where Connector/Python was installed, do the following to retrieve the location:

shell> python>>> from distutils.sysconfig import get_python_lib>>> print get_python_lib() # Python v2.x/Library/Python/2.7/site-packages>>> print(get_python_lib())   # Python v3.x/Library/Frameworks/Python.framework/Versions/3.1/lib/python3.1/site-packages

On Microsoft Windows systems, you can download Connector/Python as a zip archive from http://dev.mysql.com/downloads/connector/python/.

Make sure that the Python executable is available in the Windows %PATH% setting. For more information about installation and configuration of Python on Windows, see the section Using Python on Windows in the Python documentation.

To install Connector/Python from the .zip file, download the latest version and follow these steps:

On Windows, Connector/Python gets installed in the default location C:\PythonX.Y\Lib\site-packages\ where X.Y is the Python version you used to install the connector.

If you are not sure where Connector/Python ended up, do the following to retrieve the location where packages get installed:

shell> python>>> from distutils.sysconfig import get_python_lib>>> print get_python_lib() # Python v2.x>>> print(get_python_lib())   # Python v3.x

To test that your Connector/Python installation is working and is able to connect to a MySQL database server, you can run a very simple program where you substitute the login credentials and host information of the MySQL server. See Section 22.6.4.1, "Connecting to MySQL Using Connector/Python" for an example.

The connect() constructor is used for creating a connection to the MySQL server and returns a MySQLConnection object.

The following example shows how to connect to the MySQL server:

import mysql.connectorcnx = mysql.connector.connect(user='scott', password='tiger',  host='127.0.0.1',  database='employees')cnx.close()

See Section 22.6.6, "Connector/Python Connection Arguments" for all possible connection arguments.

It is also possible to create connection objects using the connection.MySQLConnection() class. Both methods, using the connect() constructor, or the class directly, are valid and functionally equal, but using connector() is preferred and will be used in most examples in this manual.

To handle connection errors, use the try statement and catch all errors using the errors.Error exception:

import mysql.connectorfrom mysql.connector import errorcodetry:  cnx = mysql.connector.connect(user='scott', database='testt')except mysql.connector.Error as err:  if err.errno == errorcode.ER_ACCESS_DENIED_ERROR: print("Something is wrong your username or password")  elif err.errno == errorcode.ER_BAD_DB_ERROR: print("Database does not exists")  else: print(err)else:  cnx.close()

If you have lots of connection arguments, it's best to keep them in a dictionary and use the **-operator. Here is an example:

import mysql.connectorconfig = {  'user': 'scott',  'password': 'tiger',  'host': '127.0.0.1',  'database': 'employees',  'raise_on_warnings': True,}cnx = mysql.connector.connect(**config)cnx.close()

All DDL (Data Definition Language) statements are executed using a handle structure known as a cursor. The following examples show how to create the tables of the employees database. You will need them for the other examples.

In a MySQL server, tables are very long-lived objects, and are often accessed by multiple applications written in different languages. You might typically work with tables that are already set up, rather than creating them within your own application. Avoid setting up and dropping tables over and over again, as that is an expensive operation. The exception is temporary tables, which can be created and dropped quickly within an application.

from __future__ import print_functionimport mysql.connectorfrom mysql.connector import errorcodeDB_NAME = 'employees'TABLES = {}TABLES['employees'] = ( "CREATE TABLE `employees` (" "  `emp_no` int(11) NOT NULL AUTO_INCREMENT," "  `birth_date` date NOT NULL," "  `first_name` varchar(14) NOT NULL," "  `last_name` varchar(16) NOT NULL," "  `gender` enum('M','F') NOT NULL," "  `hire_date` date NOT NULL," "  PRIMARY KEY (`emp_no`)" ") ENGINE=InnoDB")TABLES['departments'] = ( "CREATE TABLE `departments` (" "  `dept_no` char(4) NOT NULL," "  `dept_name` varchar(40) NOT NULL," "  PRIMARY KEY (`dept_no`), UNIQUE KEY `dept_name` (`dept_name`)" ") ENGINE=InnoDB")TABLES['salaries'] = ( "CREATE TABLE `salaries` (" "  `emp_no` int(11) NOT NULL," "  `salary` int(11) NOT NULL," "  `from_date` date NOT NULL," "  `to_date` date NOT NULL," "  PRIMARY KEY (`emp_no`,`from_date`), KEY `emp_no` (`emp_no`)," "  CONSTRAINT `salaries_ibfk_1` FOREIGN KEY (`emp_no`) " " REFERENCES `employees` (`emp_no`) ON DELETE CASCADE" ") ENGINE=InnoDB")TABLES['dept_emp'] = ( "CREATE TABLE `dept_emp` (" "  `emp_no` int(11) NOT NULL," "  `dept_no` char(4) NOT NULL," "  `from_date` date NOT NULL," "  `to_date` date NOT NULL," "  PRIMARY KEY (`emp_no`,`dept_no`), KEY `emp_no` (`emp_no`)," "  KEY `dept_no` (`dept_no`)," "  CONSTRAINT `dept_emp_ibfk_1` FOREIGN KEY (`emp_no`) " " REFERENCES `employees` (`emp_no`) ON DELETE CASCADE," "  CONSTRAINT `dept_emp_ibfk_2` FOREIGN KEY (`dept_no`) " " REFERENCES `departments` (`dept_no`) ON DELETE CASCADE" ") ENGINE=InnoDB")TABLES['dept_manager'] = ( "  CREATE TABLE `dept_manager` (" "  `dept_no` char(4) NOT NULL," "  `emp_no` int(11) NOT NULL," "  `from_date` date NOT NULL," "  `to_date` date NOT NULL," "  PRIMARY KEY (`emp_no`,`dept_no`)," "  KEY `emp_no` (`emp_no`)," "  KEY `dept_no` (`dept_no`)," "  CONSTRAINT `dept_manager_ibfk_1` FOREIGN KEY (`emp_no`) " " REFERENCES `employees` (`emp_no`) ON DELETE CASCADE," "  CONSTRAINT `dept_manager_ibfk_2` FOREIGN KEY (`dept_no`) " " REFERENCES `departments` (`dept_no`) ON DELETE CASCADE" ") ENGINE=InnoDB")TABLES['titles'] = ( "CREATE TABLE `titles` (" "  `emp_no` int(11) NOT NULL," "  `title` varchar(50) NOT NULL," "  `from_date` date NOT NULL," "  `to_date` date DEFAULT NULL," "  PRIMARY KEY (`emp_no`,`title`,`from_date`), KEY `emp_no` (`emp_no`)," "  CONSTRAINT `titles_ibfk_1` FOREIGN KEY (`emp_no`)" " REFERENCES `employees` (`emp_no`) ON DELETE CASCADE" ") ENGINE=InnoDB")

The above code shows how we are storing the CREATE statements in a Python dictionary called TABLES. We also define the database in a global variable called DB_NAME, which allows you to easily use a different schema.

cnx = mysql.connector.connect(user='scott')cursor = cnx.cursor()

A single MySQL server can contain multiple databases. Typically, you specify the database to switch to when connecting to the MySQL server. This example does not connect to the database upon connection, so that it can make sure the database exists, and create it if not.

def create_database(cursor): try: cursor.execute( "CREATE DATABASE {} DEFAULT CHARACTER SET 'utf8'".format(DB_NAME)) except mysql.connector.Error as err: print("Failed creating database: {}".format(err)) exit(1)try: cnx.database = DB_NAME except mysql.connector.Error as err: if err.errno == errorcode.ER_BAD_DB_ERROR: create_database(cursor) cnx.database = DB_NAME else: print(err) exit(1)

We first try to change to a particular database using the database property of the connection object cnx. If there is an error, we examine the error number to check if the database does not exist. If so, we call the create_database function to create it for us.

On any other error, the application exits and displays the error message.

for name, ddl in TABLES.iteritems(): try: print("Creating table {}: ".format(name), end='') cursor.execute(ddl) except mysql.connector.Error as err: if err.errno == errorcode.ER_TABLE_EXISTS_ERROR: print("already exists.") else: print(err.errmsg) else: print("OK")cursor.close()cnx.close()

After we succesfully created or changed to the target database, we create the tables by iterating over the items of the TABLES dictionary.

We handle the error when the table already exists by simply notifying the user that it was already there. Other errors are printed, but we simply continue creating tables. (We show how to handle the "table already exists" condition for illustration purposes. In a real application, we would typically avoid the error condition entirely by using the IF NOT EXISTS clause of the CREATE TABLE statement.)

The output would be something like this:

Creating table employees: already exists.Creating table salaries: already exists.Creating table titles: OKCreating table departments: already exists.Creating table dept_manager: already exists.Creating table dept_emp: already exists.

To populate the employees tables, use the dump files of the Employee Sample Database. Note that you only need the data dump files that you will find in an archive named like employees_db-dump-files-1.0.5.tar.bz2. After downloading the dump files, do the following from the command line, adding connection options to the mysql commands if necessary:

shell> tar xzf employees_db-dump-files-1.0.5.tar.bz2shell> cd employees_dbshell> mysql employees < load_employees.dumpshell> mysql employees < load_titles.dumpshell> mysql employees < load_departments.dumpshell> mysql employees < load_salaries.dumpshell> mysql employees < load_dept_emp.dumpshell> mysql employees < load_dept_manager.dump

Inserting or updating data is also done using the handler structure known as a cursor. When you use a transactional storage engine such as InnoDB (which is the default in MySQL 5.5 and later), you must commit the data after a sequence of INSERT, DELETE, and UPDATE statements.

In this example we show how to insert new data. The second INSERT depends on the value of the newly created primary key of the first. We are also demonstrating how to use extended formats. The task is to add a new employee starting to work tomorrow with a salary set to 50000.

from __future__ import print_functionfrom datetime import date, datetime, timedeltaimport mysql.connectorcnx = mysql.connector.connect(user='scott', database='employees')cursor = cnx.cursor()tomorrow = datetime.now().date() + timedelta(days=1)add_employee = ("INSERT INTO employees "   "(first_name, last_name, hire_date, gender, birth_date) "   "VALUES (%s, %s, %s, %s, %s)")add_salary = ("INSERT INTO salaries "  "(emp_no, salary, from_date, to_date) "  "VALUES (%(emp_no)s, %(salary)s, %(from_date)s, %(to_date)s)")data_employee = ('Geert', 'Vanderkelen', tomorrow, 'M', date(1977, 6, 14))# Insert new employeecursor.execute(add_employee, data_employee)emp_no = cursor.lastrowid# Insert salary informationdata_salary = {  'emp_no': emp_no,  'salary': 50000,  'from_date': tomorrow,  'to_date': date(9999, 1, 1),}cursor.execute(add_salary, data_salary)# Make sure data is committed to the databasecnx.commit()cursor.close()cnx.close()

We first open a connection to the MySQL server and store the connection object in the variable cnx. We then create a new cursor, by default a MySQLCursor object, using the connection's cursor() method.

We could calculate tomorrow by calling a database function, but for clarity we do it in Python using the datetime module.

Both INSERT statements are stored in the variables called add_employee and add_salary. Note that the second INSERT statement uses extended Python format codes.

The information of the new employee is stored in the tuple data_employee. The query to insert the new employee is executed and we retrieve the newly inserted value for the column emp_no using the lastrowid property of the cursor object.

Next, we insert the new salary for the new employee. We are using the emp_no variable in the directory holding the data. This directory is passed to the execute() method of the cursor object.

Since by default Connector/Python turns autocommit off, and MySQL 5.5 and later uses transactional InnoDB tables by default, it is necessary to commit your changes using the connection's commit() method. You could also roll back using the rollback() method.

The following example shows how to query data using a cursor created using the connection's cursor() method. The data returned is formatted and printed on the console.

The task is to select all employees hired in the year 1999 and print their names with their hire date to the console.

import datetimeimport mysql.connectorcnx = mysql.connector.connect(user='scott', database='employees')cursor = cnx.cursor()query = ("SELECT first_name, last_name, hire_date FROM employees " "WHERE hire_date BETWEEN %s AND %s")hire_start = datetime.date(1999, 1, 1)hire_end = datetime.date(1999, 12, 31)cursor.execute(query, (hire_start, hire_end))for (first_name, last_name, hire_date) in cursor:  print("{}, {} was hired on {:%d %b %Y}".format( last_name, first_name, hire_date))cursor.close()cnx.close()

We first open a connection to the MySQL server and store the connection object in the variable cnx. We then create a new cursor, by default a MySQLCursor object, using the connection's cursor() method.

In the preceding example, we store the SELECT statement in the variable query. Note that we are using unquoted %s-markers where dates should have been. Connector/Python converts hire_start and hire_end from Python types to a data type that MySQL understands and adds the required quotes. In this case, it replaces the first %s with '1999-01-01', and the second with '1999-12-31'.

We then execute the operation stored in the query variable using the execute() method. The data used to replace the %s-markers in the query is passed as a tuple: (hire_start, hire_end).

After executing the query, the MySQL server is ready to send the data. The result set could be zero rows, one row, or 100 million rows. Depending on the expected volume, you can use different techniques to process this result set. In this example, we use the cursor object as an iterator. The first column in the row will be stored in the variable first_name, the second in last_name, and the third in hire_date.

We print the result, formatting the output using Python's built-in format() function. Note that hire_date was converted automatically by Connector/Python to a Python datetime.date object. This means that we can easily format the date in a more human-readable form.

The output should be something like this:

..Wilharm, LiMin was hired on 16 Dec 1999Wielonsky, Lalit was hired on 16 Dec 1999Kamble, Dannz was hired on 18 Dec 1999DuBourdieux, Zhongwei was hired on 19 Dec 1999Fujisawa, Rosita was hired on 20 Dec 1999..

The following example script will give a long-overdue raise effective tomorrow to all employees who joined in the year 2000 and are still with the company.

We are using buffered cursors to iterate through the selected employees. This way we do not have to fetch the rows in a new variables, but can instead use the cursor as an iterator.

Note that the script is an example; there are other ways of doing this simple task.

from __future__ import print_functionfrom decimal import Decimalfrom datetime import datetime, date, timedeltaimport mysql.connector# Connect with the MySQL Servercnx = mysql.connector.connect(user='scott', database='employees')# Get two buffered cursorscurA = cnx.cursor(buffered=True)curB = cnx.cursor(buffered=True)# Query to get employees who joined in a period defined by two datesquery = (  "SELECT s.emp_no, salary, from_date, to_date FROM employees AS e "  "LEFT JOIN salaries AS s USING (emp_no) "  "WHERE to_date = DATE('9999-01-01')"  "AND e.hire_date BETWEEN DATE(%s) AND DATE(%s)")# UPDATE and INSERT statements for the old and new salaryupdate_old_salary = (  "UPDATE salaries SET to_date = %s "  "WHERE emp_no = %s AND from_date = %s")insert_new_salary = (  "INSERT INTO salaries (emp_no, from_date, to_date, salary) "  "VALUES (%s, %s, %s, %s)")# Select the employes getting a raisecurA.execute(query, (date(2000, 1, 1), date(2001, 1, 1)))# Iterate through the result of curAfor (emp_no, salary, from_date, to_date) in curA:  # Update the old and insert the new salary  new_salary = int(round(salary * Decimal('1.15')))  curB.execute(update_old_salary, (tomorrow, emp_no, from_date))  curB.execute(insert_new_salary,   (emp_no, tomorrow, date(9999, 1, 1,), new_salary))  # Commit the changes  cnx.commit()cnx.close()

The mysql.connector.errors module defines exception classes for errors and warnings raised by MySQL Connector/Python. Most classes defined in this module are available when you import mysql.connector.

The exception classes defined in this module follow mostly the Python Database Specification v2.0 (PEP-249). For some MySQL client or server errors it is not always clear which exception to raise. It is good to discuss whether an error should be reclassified by opening a bug report.

MySQL Server errors are mapped with Python exception based on their SQLState (see Section C.3, "Server Error Codes and Messages"). The following list shows the SQLState classes and the exception Connector/Python will raise. It is, however, possible to redefine which exception is raised for each server error. Note that the default exception is DatabaseError.

>>> from mysql.connector import errorcode>>> errorcode.ER_BAD_TABLE_ERROR1051

import mysql.connectortry:  cnx = mysql.connector.connect(user='scott', database='employees')  cursor = cnx.cursor()  cursor.execute("SELECT * FORM employees")   # Syntax error in query  cnx.close()except mysql.connector.Error as err:  print("Something went wrong: {}".format(err))

>>> from mysql.connector.errors import Error>>> str(Error())'Unknown error'>>> str(Error("Oops! There was an error."))'Oops! There was an error.'>>> str(Error(errno=2006))'2006: MySQL server has gone away'>>> str(Error(errno=2002, values=('/tmp/mysql.sock', 2)))"2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)">>> str(Error(errno=1146, sqlstate='42S02', msg="Table 'test.spam' doesn't exist"))"1146 (42S02): Table 'test.spam' doesn't exist"

import mysql.connectorfrom mysql.connector import errorcodecnx = mysql.connector.connect(user='scott', database='test')try:  cur.execute("DROP TABLE spam")except mysql.connector.Error as err:  if err.errno == errorcode.ER_BAD_TABLE_ERROR: print("Creating table spam")  else: raise

try:  cursor.execute("CREATE DESK t1 (id int, PRIMARY KEY (id))")except mysql.connector.ProgrammingError as err:  if err.errno == errorcode.ER_SYNTAX_ERROR: print("Check your syntax!")  else: print("Error: {}".format(err))

cursor.execute("CREATE TABLE t1 (id int, PRIMARY KEY (id))")try:  cursor.execute("INSERT INTO t1 (id) VALUES (1)")  cursor.execute("INSERT INTO t1 (id) VALUES (1)")except mysql.connector.IntegrityError as err:  print("Error: {}".format(err))

import mysql.connectorfrom mysql.connector import errorcode# Server error 1028 should raise a DatabaseErrormysql.connector.custom_error_exception(1028, mysql.connector.DatabaseError)# Or using a dictionary:mysql.connector.custom_error_exception({  1028: mysql.connector.DatabaseError,  1029: mysql.connector.OperationalError,})# To reset, pass an empty dictionary:mysql.connector.custom_error_exception({})

The MySQLConnection class is used to open and manage a connection to a MySQL server. It also used to send commands and SQL queries and read result.

cnx = MySQLConnection(user='joe', database='test')# Connected as 'joe'cnx.config(user='jane')cnx.reconnect()# Now connected as 'jane'

>>> cursor.execute("INSERT INTO employees (first_name) VALUES (%s)", ('Jane'))>>> cnx.commit()

>>> cnx.cmd_process_kill(123)>>> cnx.cmd_query('KILL 123')

statement = 'SELECT 1; INSERT INTO t1 VALUES (); SELECT 2'for result in cnx.cmd_query(statement, iterate=True):  if 'columns' in result: columns = result['columns'] rows = cnx.get_rows()  else: # do something useful with INSERT result

>>> from mysql.connector import RefreshOption>>> refresh = RefreshOption.LOG | RefreshOption.THREADS>>> cnx.cmd_refresh(refresh)

>>> cursor.execute("INSERT INTO employees (first_name) VALUES (%s)", ('Jane'))>>> cnx.rollback()

>>> cnx = mysql.connector.connect(user='scott')>>> cnx.set_charset('latin1')

>>> cnx = mysql.connector.connect(user='scott')>>> cnx.set_charset('latin1', 'latin1_general_ci')

>>> from mysql.connector.constants import ClientFlag>>> cnx.set_client_flags([ClientFlag.FOUND_ROWS, -ClientFlag.LONG_FLAG])>>> cnx.reconnect()

>>> cnx.autocommitFalse>>> cnx.autocommit = True>>> cnx.autocommitTrue

>>> cnx.database = 'test'>>> cnx.database = 'mysql'>>> cnx.databaseu'mysql'

>>> cnx.get_warnings = True>>> cursor.execute('SELECT "a"+1')>>> cursor.fetchall()[(1.0,)]>>> cursor.fetchwarnings()[(u'Warning', 1292, u"Truncated incorrect DOUBLE value: 'a'")]

>>> cnx.raise_on_warnings = True>>> cursor.execute('SELECT "a"+1')>>> cursor.fetchall()..mysql.connector.errors.DataError: 1292: Truncated incorrect DOUBLE value: 'a'

>>> cnx.sql_mode = 'TRADITIONAL,NO_ENGINE_SUBSTITUTION'>>> cnx.sql_mode.split(',')[u'STRICT_TRANS_TABLES', u'STRICT_ALL_TABLES', u'NO_ZERO_IN_DATE',u'NO_ZERO_DATE', u'ERROR_FOR_DIVISION_BY_ZERO', u'TRADITIONAL', u'NO_AUTO_CREATE_USER', u'NO_ENGINE_SUBSTITUTION']>>> from mysql.connector.constants import SQLMode>>> cnx.sql_mode = [ SQLMode.NO_ZERO_DATE, SQLMode.REAL_AS_FLOAT]>>> cnx.sql_modeu'REAL_AS_FLOAT,NO_ZERO_DATE'

>>> cnx.time_zone = '+00:00'>>> cur.execute('SELECT NOW()') ; cur.fetchone()(datetime.datetime(2012, 6, 15, 11, 24, 36),)>>> cnx.time_zone = '-09:00'>>> cur.execute('SELECT NOW()') ; cur.fetchone()(datetime.datetime(2012, 6, 15, 2, 24, 44),)>>> cnx.time_zoneu'-09:00'

The MySQLCursor class is used to instantiate object which can execute operation such as SQL queries. They interact with the MySQL server using a MySQLConnection object.

# Definition of the multiply stored procedure:# CREATE PROCEDURE multiply(IN pFac1 INT, IN pFac2 INT, OUT pProd INT)# BEGIN#  SET pProd := pFac1 * pFac2;# END>>> args = (5, 5, 0) # 0 is to hold value of the OUT parameter pProd>>> cursor.callproc('multiply', args)('5', '5', 25L)

insert = ("INSERT INTO employees (emp_no, first_name, last_name, hire_date) ""VALUES (%s, %s, %s, %s)")data = (2, 'Jane', 'Doe', datetime.date(2012, 3, 23))cursor.execute(insert, data)select = "SELECT * FROM employees WHERE emp_no = %(emp_no)s"cursor.execute(select, { 'emp_no': 2 })

operation = 'SELECT 1; INSERT INTO t1 VALUES (); SELECT 2'for result in cursor.execute(operation):  if result.with_rows: print("Statement '{}' has following rows:".format(  result.statement)) print(result.fetchall())  else: print("Affected row(s) by query '{}' was {}".format(  result.statement, result.rowcount))

data = [  ('Jane', date(2005, 2, 12)),  ('Joe', date(2006, 5, 23)),  ('John', date(2010, 10, 3)),]stmt = "INSERT INTO employees (first_name, hire_date) VALUES (%s, %s)"cursor.executemany(stmt, data)

>>> cursor.execute("SELECT * FROM employees ORDER BY emp_no")>>> head_rows = cursor.fetchmany(size=2)>>> remaining_rows = cursor.fetchall()

# Using a while-loopcursor.execute("SELECT * FROM employees")row = cursor.fetchone()while row is not None:  print(row)  row = cursor.fetchone()# Using the cursor as iterator cursor.execute("SELECT * FROM employees")for row in cursor:  print(row)

>>> cnx.get_warnings = True>>> cursor.execute('SELECT "a"+1')>>> cursor.fetchall()[(1.0,)]>>> cursor.fetchwarnings()[(u'Warning', 1292, u"Truncated incorrect DOUBLE value: 'a'")]

>>> cursor.callproc('sp1')()>>> for result in cursor.stored_results():... print result.fetchall()... [(1,)][(2,)]

cursor.execute("SELECT last_name, first_name, hire_date "  "FROM employees WHERE emp_no = %s", (123,))row = dict(zip(cursor.column_names, cursor.fetchone())print("{last_name}, {first_name}: {hire_date}".format(row))

import mysql.connectorcnx = mysql.connector.connect(user='scott', database='test')cursor = cnx.cursor()operation = 'SELECT 1; UPDATE t1 SET c1 = 2; SELECT 2'for result in cursor.execute(operation, multi=True):  if result.with_rows: result.fetchall()  else: print("Updated row(s): {}".format(result.rowcount))

This class is inheriting from cursor.MySQLCursor and if needed automatically retrieves rows after an operation has been executed.

MySQLCursorBuffered can be useful in situations where two queries, with small result sets, need to be combined or computed with each other.

You can either use the buffered argument when using the connection's cursor() method, or you can use the buffered connection option to make all created cursors by default buffering.

import mysql.connectorcnx = mysql.connector.connect()# Only this particular cursor will be buffering resultscursor.cursor(buffered=True)# All cursors by default bufferingcnx = mysql.connector.connect(buffered=True)

See Section 22.6.5.1, "Tutorial: Raise employee's salary using a buffering cursor" for a practical use case.

This class provides constants defining MySQL client flags which can be used upon connection to configure the session. The ClientFlag class is available when importing mysql.connector.

>>> import mysql.connector>>> mysql.connector.ClientFlag.FOUND_ROWS2

See Section 22.6.7.2.30, "Method MySQLConnection.set_client_flags(flags)" and the connection argument client_flag.

Note that the ClientFlag class can not be instantiated.

This class provides all supported MySQL field or data types. They can be useful when dealing with raw data or defining your own converters. The field type is stored with every cursor in the description for each column.

The following example shows how you can print the name of the data types for each of the columns in the result set.

from __future__ import print_functionimport mysql.connectorfrom mysql.connector import FieldTypecnx = mysql.connector.connect(user='scott', database='test')cursor = cnx.cursor()cursor.execute(  "SELECT DATE(NOW()) AS `c1`, TIME(NOW()) AS `c2`, "  "NOW() AS `c3`, 'a string' AS `c4`, 42 AS `c5`")rows = cursor.fetchall()for desc in cursor.description:  colname = desc[0]  coltype = desc[1]  print("Column {} has type {}".format( colname, FieldType.get_info(coltype)))cursor.close()cnx.close()

Note that the FieldType class can not be instantiated.

This class provides all known MySQL Server SQL Modes. It is mostly used when setting the SQL modes at connection time using the connection's property sql_mode. See Section 22.6.7.2.40, "Property MySQLConnection.sql_mode".

Note that the SQLMode class can not be instantiated.

This class provides all known MySQL characters sets and their default collations. See Section 22.6.7.2.29, "Method MySQLConnection.set_charset_collation(charset=None, collation=None)" for examples.

Note that the CharacterSet class can not be instantiated.