mysql-connector-python.html

/resources/lib/mysql-connector-python/docs/mysql-connector-python.html

Large files files are truncated, but you can click here to view the full file

<html lang="en"><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8"><title>MySQL Connector/Python</title><link rel="stylesheet" type="text/css" href="mysql-html.css"><meta name="generator" content="DocBook XSL Stylesheets V1.77.1">
<script language="javascript" type="text/javascript">
  function addOnload(theFunc)
  {
    var previous = window.onload;
    if (typeof window.onload != 'function')
    {
      window.onload = theFunc;
    }
    else
    {
      window.onload = function()
      {
        previous();
        theFunc();
      }
    }
  }

  addOnload(function()
  {
    var base = new Date(1355755772*1000);
    var now = new Date();
    var diff = ((now-base)/1000)/(24*3600);

    if (diff > 90) {
      var nodes = document.getElementsByClassName('titlepage');
      nodes[0].innerHTML = '<p style="border: 5px #ff0000 solid; padding: 5px; margin 5px">' +
        'This copy of the manual is more than 90 days old. We encourage you to download a ' +
        'new version from <a href="http://dev.mysql.com">dev.mysql.com/doc</a>.</p>' + nodes[0].innerHTML;
    }
  });
</script>
<noscript></noscript>
</head><body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div lang="en" class="book"><div class="titlepage"><div><div><h1 class="title"><a name="connectors"></a>MySQL Connector/Python</h1></div><div><div class="abstract"><p class="title"><b>Abstract</b></p><p>
        This manual describes how to install, configure, and develop
        database applications using MySQL Connector/Python, a
        self-contained Python driver for communicating with MySQL
        servers.
      </p><p>
        Document generated on:

        2012-12-17



        (revision: 33584)
      </p></div></div></div><hr></div><div class="toc"><p><b>Table of Contents</b></p><dl><dt><span class="preface"><a href="#preface">Preface and Legal Notices</a></span></dt><dt><span class="chapter"><a href="#connector-python">1. MySQL Connector/Python</a></span></dt><dt><span class="chapter"><a href="#connector-python-versions">2. Connector/Python Versions</a></span></dt><dt><span class="chapter"><a href="#connector-python-install">3. Connector/Python Installation</a></span></dt><dd><dl><dt><span class="section"><a href="#connector-python-installation-src-unix">3.1. Installing Connector/Python Source Distribution on Linux, UNIX, or OS X</a></span></dt><dt><span class="section"><a href="#connector-python-installation-src-windows">3.2. Installing Connector/Python Source Distribution on Microsoft Windows</a></span></dt><dt><span class="section"><a href="#connector-python-verification">3.3. Verifying Your Connector/Python Installation</a></span></dt></dl></dd><dt><span class="chapter"><a href="#connector-python-examples">4. Connector/Python Coding Examples</a></span></dt><dd><dl><dt><span class="section"><a href="#myconnpy_example_connecting">4.1. Connecting to MySQL Using Connector/Python</a></span></dt><dt><span class="section"><a href="#myconnpy_example_ddl">4.2. Creating Tables Using Connector/Python</a></span></dt><dt><span class="section"><a href="#myconnpy_example_cursor_transaction">4.3. Inserting Data Using Connector/Python</a></span></dt><dt><span class="section"><a href="#myconnpy_example_cursor_select">4.4. Querying Data Using Connector/Python</a></span></dt></dl></dd><dt><span class="chapter"><a href="#connector-python-tutorials">5. Connector/Python Tutorials</a></span></dt><dd><dl><dt><span class="section"><a href="#myconnpy_tutorial_CursorBuffered_GiveRaise">5.1. Tutorial: Raise employee's salary using a buffering cursor</a></span></dt></dl></dd><dt><span class="chapter"><a href="#connector-python-connectargs">6. Connector/Python Connection Arguments</a></span></dt><dt><span class="chapter"><a href="#connector-python-reference">7. Connector/Python API Reference</a></span></dt><dd><dl><dt><span class="section"><a href="#connector-python-apiref-errors">7.1. Errors and Exceptions</a></span></dt><dd><dl><dt><span class="section"><a href="#connector-python-apiref-errorcode">7.1.1. Module <code class="literal">errorcode</code></a></span></dt><dt><span class="section"><a href="#connector-python-apiref-errors-error">7.1.2. Exception <code class="literal">errors.Error</code></a></span></dt><dt><span class="section"><a href="#connector-python-apiref-errors-warning">7.1.3. Exception <code class="literal">errors.Warning</code></a></span></dt><dt><span class="section"><a href="#connector-python-apiref-errors-interfaceerror">7.1.4. Exception <code class="literal">errors.InterfaceError</code></a></span></dt><dt><span class="section"><a href="#connector-python-apiref-errors-databaseerror">7.1.5. Exception <code class="literal">errors.DatabaseError</code></a></span></dt><dt><span class="section"><a href="#connector-python-apiref-errors-internalerror">7.1.6. Exception <code class="literal">errors.InternalError</code></a></span></dt><dt><span class="section"><a href="#connector-python-apiref-errors-operationalerror">7.1.7. Exception <code class="literal">errors.OperationalError</code></a></span></dt><dt><span class="section"><a href="#connector-python-apiref-errors-programmingerror">7.1.8. Exception <code class="literal">errors.ProgrammingError</code></a></span></dt><dt><span class="section"><a href="#connector-python-apiref-errors-integrityerror">7.1.9. Exception <code class="literal">errors.IntegrityError</code></a></span></dt><dt><span class="section"><a href="#connector-python-apiref-errors-dataerror">7.1.10. Exception <code class="literal">errors.DataError</code></a></span></dt><dt><span class="section"><a href="#connector-python-apiref-errors-notsupportederror">7.1.11. Exception <code class="literal">errors.NotSupportedError</code></a></span></dt><dt><span class="section"><a href="#connector-python-apiref-errors-custom-error-exception">7.1.12. Function <code class="literal">errors.custom_error_exception(error=None,
        exception=None)</code></a></span></dt></dl></dd><dt><span class="section"><a href="#connector-python-apiref-MySQLConnection">7.2. Class <code class="literal">connection.MySQLConnection</code></a></span></dt><dd><dl><dt><span class="section"><a href="#myconnpy_MySQLConnection">7.2.1. Constructor <code class="literal">connection.MySQLConnection(**kwargs)</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLConnection_close">7.2.2. Method <code class="literal">MySQLConnection.close()</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLConnection_config">7.2.3. Method <code class="literal">MySQLConnection.config(**kwargs)</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLConnection_connect">7.2.4. Method <code class="literal">MySQLConnection.connect(**kwargs)</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLConnection_commit">7.2.5. Method <code class="literal">MySQLConnection.commit()</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLConnection_cursor">7.2.6. Method <code class="literal">MySQLConnection.cursor(buffered=None, raw=None,
        cursor_class=None)</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLConnection_cmd_change_user">7.2.7. Method <code class="literal">MySQLConnection.cmd_change_user(username='',
        password='', database='', charset=33)</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLConnection_cmd_debug">7.2.8. Method <code class="literal">MySQLConnection.cmd_debug()</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLConnection_cmd_init_db">7.2.9. Method <code class="literal">MySQLConnection.cmd_init_db(database)</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLConnection_cmd_ping">7.2.10. Method <code class="literal">MySQLConnection.cmd_ping()</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLConnection_cmd_process_info">7.2.11. Method <code class="literal">MySQLConnection.cmd_process_info()</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLConnection_cmd_process_kill">7.2.12. Method <code class="literal">MySQLConnection.cmd_process_kill(mysql_pid)</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLConnection_cmd_quit">7.2.13. Method <code class="literal">MySQLConnection.cmd_quit()</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLConnection_cmd_query">7.2.14. Method <code class="literal">MySQLConnection.cmd_query(statement)</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLConnection_cmd_query_iter">7.2.15. Method <code class="literal">MySQLConnection.cmd_query_iter(statement)</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLConnection_cmd_refresh">7.2.16. Method <code class="literal">MySQLConnection.cmd_refresh(options)</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLConnection_cmd_shutdown">7.2.17. Method <code class="literal">MySQLConnection.cmd_shutdown()</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLConnection_cmd_statistics">7.2.18. Method <code class="literal">MySQLConnection.cmd_statistics()</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLConnection_disconnect">7.2.19. Method <code class="literal">MySQLConnection.disconnect()</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLConnection_get_rows">7.2.20. Method <code class="literal">MySQLConnection.get_rows(count=None)</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLConnection_get_row">7.2.21. Method <code class="literal">MySQLConnection.get_row()</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLConnection_get_server_info">7.2.22. Method <code class="literal">MySQLConnection.get_server_info()</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLConnection_get_server_version">7.2.23. Method <code class="literal">MySQLConnection.get_server_version()</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLConnection_is_connected">7.2.24. Method <code class="literal">MySQLConnection.is_connected()</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLConnection_isset_client_flag">7.2.25. Method <code class="literal">MySQLConnection.isset_client_flag(flag)</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLConnection_ping">7.2.26. Method <code class="literal">MySQLConnection.ping(attempts=1, delay=0)</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLConnectoin_reconnect">7.2.27. Method <code class="literal">MySQLConnection.reconnect(attempts=1, delay=0)</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLConnection_rollback">7.2.28. Method <code class="literal">MySQLConnection.rollback()</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLConnection_set_charset_collation">7.2.29. Method <code class="literal">MySQLConnection.set_charset_collation(charset=None,
        collation=None)</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLConnection_set_client_flags">7.2.30. Method <code class="literal">MySQLConnection.set_client_flags(flags)</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLConnection_autocommit">7.2.31. Property <code class="literal">MySQLConnection.autocommit</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLConnection_charset_name">7.2.32. Property <code class="literal">MySQLConnection.charset_name</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLConnection_collation_name">7.2.33. Property <code class="literal">MySQLConnection.collation_name</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLConnection_connection_id">7.2.34. Property <code class="literal">MySQLConnection.connection_id</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLConnection_database">7.2.35. Property <code class="literal">MySQLConnection.database</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLConnection_get_warnings">7.2.36. Property <code class="literal">MySQLConnection.get_warnings</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLConnection_raise_on_warnings">7.2.37. Property <code class="literal">MySQLConnection.raise_on_warnings</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLConnection_server_host">7.2.38. Property <code class="literal">MySQLConnection.server_host</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLConnection_server_port">7.2.39. Property <code class="literal">MySQLConnection.server_port</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLConnection_sql_mode">7.2.40. Property <code class="literal">MySQLConnection.sql_mode</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLConnection_time_zone">7.2.41. Property <code class="literal">MySQLConnection.time_zone</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLConnection_unix_socket">7.2.42. Property <code class="literal">MySQLConnection.unix_socket</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLConnection_user">7.2.43. Property <code class="literal">MySQLConnection.user</code></a></span></dt></dl></dd><dt><span class="section"><a href="#connector-python-apiref-MySQLCursor">7.3. Class <code class="literal">cursor.MySQLCursor</code></a></span></dt><dd><dl><dt><span class="section"><a href="#myconnpy_MySQLCursor">7.3.1. Constructor <code class="literal">cursor.MySQLCursor</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLCursor_callproc">7.3.2. Method <code class="literal">MySQLCursor.callproc(procname, args=())</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLCursor_close">7.3.3. Method <code class="literal">MySQLCursor.close()</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLCursor_execute">7.3.4. Method <code class="literal">MySQLCursor.execute(operation, params=None,
        multi=False)</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLCursor_executemany">7.3.5. Method <code class="literal">MySQLCursor.executemany(operation, seq_params)</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLCursor_fetchall">7.3.6. Method <code class="literal">MySQLCursor.fetchall()</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLCursor_fetchmany">7.3.7. Method <code class="literal">MySQLCursor.fetchmany(size=1)</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLCursor_fetchone">7.3.8. Method <code class="literal">MySQLCursor.fetchone()</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLCursor_fetchwarnings">7.3.9. Method <code class="literal">MySQLCursor.fetchwarnings()</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLCursor_stored_results">7.3.10. Method <code class="literal">MySQLCursor.stored_results()</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLCursor_column_names">7.3.11. Property <code class="literal">MySQLCursor.column_names</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLCursor_statement">7.3.12. Property <code class="literal">MySQLCursor.statement</code></a></span></dt><dt><span class="section"><a href="#myconnpy_MySQLCursor_with_rows">7.3.13. Property <code class="literal">MySQLCursor.with_rows</code></a></span></dt></dl></dd><dt><span class="section"><a href="#connector-python-apiref-MySQLCursorBuffered">7.4. Class <code class="literal">cursor.MySQLCursorBuffered</code></a></span></dt><dt><span class="section"><a href="#connector-python-apiref-ClientFlag">7.5. Class <code class="literal">constants.ClientFlag</code></a></span></dt><dt><span class="section"><a href="#connector-python-apiref-FieldType">7.6. Class <code class="literal">constants.FieldType</code></a></span></dt><dt><span class="section"><a href="#connector-python-apiref-SQLMode">7.7. Class <code class="literal">constants.SQLMode</code></a></span></dt><dt><span class="section"><a href="#connector-python-apiref-CharacterSet">7.8. Class <code class="literal">constants.CharacterSet</code></a></span></dt><dt><span class="section"><a href="#connector-python-apiref-RefreshOption">7.9. Class <code class="literal">constants.RefreshOption</code></a></span></dt></dl></dd><dt><span class="chapter"><a href="#cpython-news">8. MySQL Connector/Python Change History</a></span></dt><dd><dl><dt><span class="section"><a href="#cpython-news-1-0-8">8.1. Changes in MySQL Connector/Python 1.0.8 (Not yet released, Generally Available)</a></span></dt><dt><span class="section"><a href="#cpython-news-1-0-7">8.2. Changes in MySQL Connector/Python 1.0.7 (29 September 2012, Generally Available)</a></span></dt><dt><span class="section"><a href="#cpython-news-1-0-6">8.3. Changes in MySQL Connector/Python 1.0.6 (30 August 2012, beta)</a></span></dt><dt><span class="section"><a href="#cpython-news-1-0-5">8.4. Changes in MySQL Connector/Python 1.0.5 (17 July 2012, beta)</a></span></dt><dt><span class="section"><a href="#cpython-news-1-0-4">8.5. Changes in MySQL Connector/Python 1.0.4 (07 July 2012, alpha)</a></span></dt><dt><span class="section"><a href="#cpython-news-1-0-3">8.6. Changes in MySQL Connector/Python 1.0.3 (08 June 2012, alpha)</a></span></dt><dt><span class="section"><a href="#cpython-news-1-0-2">8.7. Changes in MySQL Connector/Python 1.0.2 (19 May 2012, alpha)</a></span></dt><dt><span class="section"><a href="#cpython-news-1-0-1">8.8. Changes in MySQL Connector/Python 1.0.1 (26 April 2012, alpha)</a></span></dt><dt><span class="section"><a href="#cpython-news-1-0-0">8.9. Changes in MySQL Connector/Python 1.0.0 (22 April 2012, alpha)</a></span></dt></dl></dd></dl></div><div class="preface"><div class="titlepage"><div><div><h1 class="title"><a name="preface"></a>Preface and Legal Notices</h1></div></div></div><p>
      This manual describes how to install, configure, and develop
      database applications using MySQL Connector/Python, the a
      self-contained Python driver for communicating with MySQL servers.
    </p><h2><a name="legalnotice"></a>Legal Notices</h2><p>
    Copyright © 2012, Oracle and/or its affiliates. All rights
    reserved.
  </p><p>
    This software and related documentation are provided under a license
    agreement containing restrictions on use and disclosure and are
    protected by intellectual property laws. Except as expressly
    permitted in your license agreement or allowed by law, you may not
    use, copy, reproduce, translate, broadcast, modify, license,
    transmit, distribute, exhibit, perform, publish, or display any
    part, in any form, or by any means. Reverse engineering,
    disassembly, or decompilation of this software, unless required by
    law for interoperability, is prohibited.
  </p><p>
    The information contained herein is subject to change without notice
    and is not warranted to be error-free. If you find any errors,
    please report them to us in writing.
  </p><p>
    If this software or related documentation is delivered to the U.S.
    Government or anyone licensing it on behalf of the U.S. Government,
    the following notice is applicable:
  </p><p>
    U.S. GOVERNMENT RIGHTS Programs, software, databases, and related
    documentation and technical data delivered to U.S. Government
    customers are "commercial computer software" or "commercial
    technical data" pursuant to the applicable Federal Acquisition
    Regulation and agency-specific supplemental regulations. As such,
    the use, duplication, disclosure, modification, and adaptation shall
    be subject to the restrictions and license terms set forth in the
    applicable Government contract, and, to the extent applicable by the
    terms of the Government contract, the additional rights set forth in
    FAR 52.227-19, Commercial Computer Software License (December 2007).
    Oracle USA, Inc., 500 Oracle Parkway, Redwood City, CA 94065.
  </p><p>
    This software is developed for general use in a variety of
    information management applications. It is not developed or intended
    for use in any inherently dangerous applications, including
    applications which may create a risk of personal injury. If you use
    this software in dangerous applications, then you shall be
    responsible to take all appropriate fail-safe, backup, redundancy,
    and other measures to ensure the safe use of this software. Oracle
    Corporation and its affiliates disclaim any liability for any
    damages caused by use of this software in dangerous applications.
  </p><p>
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. MySQL is a trademark of Oracle Corporation and/or its
    affiliates, and shall not be used without Oracle's express written
    authorization. Other names may be trademarks of their respective
    owners.
  </p><p>
    This software and documentation may provide access to or information
    on content, products, and services from third parties. Oracle
    Corporation and its affiliates are not responsible for and expressly
    disclaim all warranties of any kind with respect to third-party
    content, products, and services. Oracle Corporation and its
    affiliates will not be responsible for any loss, costs, or damages
    incurred due to your access to or use of third-party content,
    products, or services.
  </p><p>
    This documentation is in prerelease status and is intended for
    demonstration and preliminary use only. It may not be specific to
    the hardware on which you are using the software. Oracle Corporation
    and its affiliates are not responsible for and expressly disclaim
    all warranties of any kind with respect to this documentation and
    will not be responsible for any loss, costs, or damages incurred due
    to the use of this documentation.
  </p><p>
    The information contained in this document is for informational
    sharing purposes only and should be considered in your capacity as a
    customer advisory board member or pursuant to your beta trial
    agreement only. It is not a commitment to deliver any material,
    code, or functionality, and should not be relied upon in making
    purchasing decisions. The development, release, and timing of any
    features or functionality described in this document remains at the
    sole discretion of Oracle.
  </p><p>
    This document in any form, software or printed matter, contains
    proprietary information that is the exclusive property of Oracle.
    Your access to and use of this material is subject to the terms and
    conditions of your Oracle Software License and Service Agreement,
    which has been executed and with which you agree to comply. This
    document and information contained herein may not be disclosed,
    copied, reproduced, or distributed to anyone outside Oracle without
    prior written consent of Oracle or as specifically provided below.
    This document is not part of your license agreement nor can it be
    incorporated into any contractual agreement with Oracle or its
    subsidiaries or affiliates.
  </p><p>
    This documentation is NOT distributed under a GPL license. Use of
    this documentation is subject to the following terms:
  </p><p>
    You may create a printed copy of this documentation solely for your
    own personal use. Conversion to other formats is allowed as long as
    the actual content is not altered or edited in any way. You shall
    not publish or distribute this documentation in any form or on any
    media, except if you distribute the documentation in a manner
    similar to how Oracle disseminates it (that is, electronically for
    download on a Web site with the software) or on a CD-ROM or similar
    medium, provided however that the documentation is disseminated
    together with the software on the same medium. Any other use, such
    as any dissemination of printed copies or use of this documentation,
    in whole or in part, in another publication, requires the prior
    written consent from an authorized representative of Oracle. Oracle
    and/or its affiliates reserve any and all rights to this
    documentation not expressly granted above.
  </p><p>
    For more information on the terms of this license, or for details on
    how the MySQL documentation is built and produced, please visit
    <a class="ulink" href="http://dev.mysql.com/contact/" target="_top">MySQL Contact &amp;
    Questions</a>.
  </p><p>
    For additional licensing information, including licenses for
    third-party libraries used by MySQL products, see
    <a class="xref" href="#preface" title="Preface and Legal Notices">Preface and Legal Notices</a>.
  </p><p>
    For help with using MySQL, please visit either the
    <a class="ulink" href="http://forums.mysql.com" target="_top">MySQL Forums</a> or
    <a class="ulink" href="http://lists.mysql.com" target="_top">MySQL Mailing Lists</a>
    where you can discuss your issues with other MySQL users.
  </p><p>
    For additional documentation on MySQL products, including
    translations of the documentation into other languages, and
    downloadable versions in variety of formats, including HTML and PDF
    formats, see the <a class="ulink" href="http://dev.mysql.com/doc" target="_top">MySQL
    Documentation Library</a>.
  </p></div><div class="chapter"><div class="titlepage"><div><div><h1 class="title"><a name="connector-python"></a>Chapter 1. MySQL Connector/Python</h1></div></div></div><a class="indexterm" name="id498227"></a><a class="indexterm" name="id498233"></a><a class="indexterm" name="id498240"></a><p>
    MySQL Connector/Python allows Python programs to access MySQL
    databases, using an API that is compliant with the
    <a class="ulink" href="http://www.python.org/dev/peps/pep-0249/" target="_top">Python DB API
    version 2.0</a>. It is written in pure Python and does not have
    any dependencies except for the
    <a class="ulink" href="http://docs.python.org/library/" target="_top">Python Standard
    Library</a>.
  </p><p>
    MySQL Connector/Python includes support for:
  </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
        Almost all features provided by MySQL Server up to and including
        MySQL Server version 5.5.
      </p></li><li class="listitem"><p>
        Converting parameter values back and forth between Python and
        MySQL data types, for example Python <code class="literal">datetime</code>
        and MySQL <code class="literal">DATETIME</code>. You can turn automatic
        conversion on for convenience, or off for optimal performance.
      </p></li><li class="listitem"><p>
        All MySQL extensions to standard SQL syntax.
      </p></li><li class="listitem"><p>
        Protocol compression, which enables compressing the data stream
        between the client and server.
      </p></li><li class="listitem"><p>
        Connections using TCP/IP sockets and on Unix using Unix sockets.
      </p></li><li class="listitem"><p>
        Secure TCP/IP connections using SSL.
      </p></li><li class="listitem"><p>
        Self-contained driver. Connector/Python does not require the
        MySQL client library or any Python modules outside the standard
        library.
      </p></li></ul></div><p>
    MySQL Connector/Python supports from Python version 2.4 through 2.7,
    and Python 3.1 and later. Note that Connector/Python does not
    support the old MySQL Server authentication methods, which means
    that MySQL versions prior to 4.1 will not work.
  </p></div><div class="chapter"><div class="titlepage"><div><div><h1 class="title"><a name="connector-python-versions"></a>Chapter 2. Connector/Python Versions</h1></div></div></div><p>
    MySQL Connector/Python v1.0.x series went going through a series of
    beta releases, leading to the first generally available (GA) version
    1.0.7. Any development releases prior to general availability are
    not supported now that the GA version is released.
  </p><p>
    The following table summarizes the available Connector/Python
    versions:
  </p><div class="table"><a name="id498371"></a><p class="title"><b>Table 2.1. Connector/Python Version Reference</b></p><div class="table-contents"><table summary=" For each Connector/Python version, this table lists the
      corresponding MySQL Server versions that it works with, the
      corresponding versions of Python that it works with, and whether
      it is pre-release, generally available (supported), or obsolete.
      MySQL server and Python versions within brackets are known to work
      with Connector/Python, but are not officially supported. Bugs
      might not get fixed for those versions. " border="1"><colgroup><col><col><col><col></colgroup><thead><tr><th scope="col">Connector/Python Version</th><th scope="col">MySQL Server Versions</th><th scope="col">Python Versions</th><th scope="col">Support Status for Connector</th></tr></thead><tbody><tr><td scope="row">1.0</td><td>5.6, 5.5 (5.1, 5.0, 4.1)</td><td>2.7, 2.6 (2.5, 2.4); 3.1 and later</td><td>Recommended version</td></tr></tbody></table></div></div><br class="table-break"><div xmlns="http://www.w3.org/1999/xhtml" class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><div class="admon-title">Note</div><p xmlns="">
      MySQL server and Python versions within brackets are known to work
      with Connector/Python, but are not officially supported. Bugs
      might not get fixed for those versions.
    </p></div></div><div class="chapter"><div class="titlepage"><div><div><h1 class="title"><a name="connector-python-install"></a>Chapter 3. Connector/Python Installation</h1></div></div></div><div class="toc"><p><b>Table of Contents</b></p><dl><dt><span class="section"><a href="#connector-python-installation-src-unix">3.1. Installing Connector/Python Source Distribution on Linux, UNIX, or OS X</a></span></dt><dt><span class="section"><a href="#connector-python-installation-src-windows">3.2. Installing Connector/Python Source Distribution on Microsoft Windows</a></span></dt><dt><span class="section"><a href="#connector-python-verification">3.3. Verifying Your Connector/Python Installation</a></span></dt></dl></div><p>
    Connector/Python runs on any platform where Python is installed.
    Python comes pre-installed on almost any Linux distribution or
    UNIX-like system such as Apple Mac OS X and FreeBSD. On Microsoft
    Windows systems, you can install Python using the installer found on
    the <a class="ulink" href="http://python.org/download/" target="_top">Python Download
    website</a>.
  </p><p>
    Connector/Python is a pure Python implementation of the MySQL
    Client/Server protocol, meaning it does not require any other MySQL
    client libraries or other components. It also has no third-party
    dependencies. If you need SSL support, verify that your Python
    installation has been compiled using the
    <a class="ulink" href="http://www.openssl.org/" target="_top">OpenSSL</a> libraries.
  </p><p>
    The installation of Connector/Python is similar on every platform
    and follows the standard
    <a class="ulink" href="http://docs.python.org/install/index.html#install-index" target="_top">Python
    Distribution Utilities</a> or
    <a class="ulink" href="http://docs.python.org/distutils/" target="_top">Distutils</a>.
    Some platforms have specific packaging, for example RPM, and, when
    made available, the installation of these will be covered in this
    manual.
  </p><p>
    Python terminology regarding distributions:
  </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
        <span class="bold"><strong>Source Distribution</strong></span> is a
        distribution that contains only source files and is generally
        platform independent.
      </p></li><li class="listitem"><p>
        <span class="bold"><strong>Built Distribution</strong></span> can be
        regarded as a binary package. It contains both sources and
        platform-independent bytecode.
      </p></li></ul></div><div class="section"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="connector-python-installation-src-unix"></a>3.1. Installing Connector/Python Source Distribution on Linux, UNIX, or OS X</h2></div></div></div><p>
      On UNIX-like systems such as Linux distributions, Solaris, Apple
      Mac OS X, and FreeBSD, you can download Connector/Python as a
      <code class="literal">tar</code> archive from
      <a class="ulink" href="http://dev.mysql.com/downloads/connector/python/" target="_top">http://dev.mysql.com/downloads/connector/python/</a>.
    </p><p>
      To install Connector/Python from the <code class="literal">.tar.gz</code>
      file, download the latest version and follow these steps:
    </p><pre class="programlisting">shell&gt; <strong class="userinput"><code>gunzip mysql-connector-python-1.0.6b1.tar.gz</code></strong>
shell&gt; <strong class="userinput"><code>tar xf mysql-connector-python-1.0.6b1.tar</code></strong>
shell&gt; <strong class="userinput"><code>cd mysql-connector-python-1.0.6b1</code></strong>
shell&gt; <strong class="userinput"><code>sudo python setup.py install</code></strong>
</pre><p>
      On UNIX-like systems, Connector/Python gets installed in the
      default location
      <code class="filename">/<em class="replaceable"><code>prefix</code></em>/lib/python<em class="replaceable"><code>X.Y</code></em>/site-packages/</code>,
      where <em class="replaceable"><code>prefix</code></em> is the location where
      Python was installed and <em class="replaceable"><code>X.Y</code></em> is the
      version of Python. See
      <a class="ulink" href="http://docs.python.org/install/index.html#how-installation-works" target="_top">How
      installation works</a> in the Python manual.
    </p><p>
      If you are not sure where Connector/Python was installed, do the
      following to retrieve the location:
    </p><pre class="programlisting">shell&gt; python
&gt;&gt;&gt; from distutils.sysconfig import get_python_lib
&gt;&gt;&gt; print get_python_lib()            # Python v2.x
/Library/Python/2.7/site-packages
&gt;&gt;&gt; print(get_python_lib())           # Python v3.x
/Library/Frameworks/Python.framework/Versions/3.1/lib/python3.1/site-packages</pre><div xmlns="http://www.w3.org/1999/xhtml" class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><div class="admon-title">Note</div><p xmlns="">
        The above example shows the default installation location on Mac
        OS X 10.7.
      </p></div></div><div class="section"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="connector-python-installation-src-windows"></a>3.2. Installing Connector/Python Source Distribution on Microsoft Windows</h2></div></div></div><p>
      On Microsoft Windows systems, you can download Connector/Python as
      a <code class="literal">zip</code> archive from
      <a class="ulink" href="http://dev.mysql.com/downloads/connector/python/" target="_top">http://dev.mysql.com/downloads/connector/python/</a>.
    </p><p>
      Make sure that the Python executable is available in the Windows
      <code class="literal">%PATH%</code> setting. For more information about
      installation and configuration of Python on Windows, see the
      section
      <a class="ulink" href="http://docs.python.org/using/windows.html" target="_top">Using
      Python on Windows</a> in the Python documentation.
    </p><p>
      To install Connector/Python from the <code class="literal">.zip</code> file,
      download the latest version and follow these steps:
    </p><div class="orderedlist"><ol class="orderedlist" type="1"><li class="listitem"><p>
          Unpack the downloaded <code class="literal">zip</code> archive into a
          directory of your choice. For example, into the folder
          <code class="filename">C:\mysql-connector\</code>. Use the appropriate
          unzip command for your system, for example,
          <code class="literal">unzip</code>, <code class="literal">pkunzip</code>, and so
          on.
        </p></li><li class="listitem"><p>
          Start a console window (or a DOS window) and change to the
          folder where you unpacked the Connector/Python
          <code class="literal">zip</code> archive.
        </p><pre class="programlisting">shell&gt; <strong class="userinput"><code>cd C:\mysql-connector\</code></strong>
</pre></li><li class="listitem"><p>
          Once inside the Connector/Python folder, do the following:
        </p><pre class="programlisting">shell&gt; <strong class="userinput"><code>python setup.py install</code></strong>
</pre></li></ol></div><p>
      On Windows, Connector/Python gets installed in the default
      location
      <code class="filename">C:\Python<em class="replaceable"><code>X.Y</code></em>\Lib\site-packages\</code>
      where <em class="replaceable"><code>X.Y</code></em> is the Python version you
      used to install the connector.
    </p><p>
      If you are not sure where Connector/Python ended up, do the
      following to retrieve the location where packages get installed:
    </p><pre class="programlisting">shell&gt; python
&gt;&gt;&gt; from distutils.sysconfig import get_python_lib
&gt;&gt;&gt; print get_python_lib()            # Python v2.x
&gt;&gt;&gt; print(get_python_lib())           # Python v3.x</pre></div><div class="section"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="connector-python-verification"></a>3.3. Verifying Your Connector/Python Installation</h2></div></div></div><p>
      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
      <a class="xref" href="#myconnpy_example_connecting" title="4.1. Connecting to MySQL Using Connector/Python">Section 4.1, “Connecting to MySQL Using Connector/Python”</a> for an example.
    </p></div></div><div class="chapter"><div class="titlepage"><div><div><h1 class="title"><a name="connector-python-examples"></a>Chapter 4. Connector/Python Coding Examples</h1></div></div></div><div class="toc"><p><b>Table of Contents</b></p><dl><dt><span class="section"><a href="#myconnpy_example_connecting">4.1. Connecting to MySQL Using Connector/Python</a></span></dt><dt><span class="section"><a href="#myconnpy_example_ddl">4.2. Creating Tables Using Connector/Python</a></span></dt><dt><span class="section"><a href="#myconnpy_example_cursor_transaction">4.3. Inserting Data Using Connector/Python</a></span></dt><dt><span class="section"><a href="#myconnpy_example_cursor_select">4.4. Querying Data Using Connector/Python</a></span></dt></dl></div><p>
    These coding examples illustrate how to develop Python applications
    and scripts which connect to a MySQL Server using MySQL
    Connector/Python.
  </p><div class="section"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="myconnpy_example_connecting"></a>4.1. Connecting to MySQL Using Connector/Python</h2></div></div></div><p>
      The <code class="literal">connect()</code> constructor is used for creating
      a connection to the MySQL server and returns a
      <code class="literal">MySQLConnection</code> object.
    </p><p>
      The following example shows how to connect to the MySQL server:
    </p><pre class="programlisting">import mysql.connector
cnx = mysql.connector.connect(user='scott', password='tiger',
                              host='127.0.0.1',
                              database='employees')
cnx.close()</pre><p>
      See <a class="xref" href="#connector-python-connectargs" title="Chapter 6. Connector/Python Connection Arguments">Chapter 6, <i>Connector/Python Connection Arguments</i></a> for all
      possible connection arguments.
    </p><p>
      It is also possible to create connection objects using the
      <a class="link" href="#connector-python-apiref-MySQLConnection" title="7.2. Class connection.MySQLConnection">connection.MySQLConnection()</a>
      class. Both methods, using the <code class="literal">connect()</code>
      constructor, or the class directly, are valid and functionally
      equal, but using <code class="literal">connector()</code> is preferred and
      will be used in most examples in this manual.
    </p><p>
      To handle connection errors, use the <code class="literal">try</code>
      statement and catch all errors using the
      <a class="link" href="#connector-python-apiref-errors-error" title="7.1.2. Exception errors.Error">errors.Error</a>
      exception:
    </p><pre class="programlisting">import mysql.connector
from mysql.connector import errorcode
try:
  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()</pre><p>
      If you have lots of connection arguments, it's best to keep them
      in a dictionary and use the <code class="literal">**</code>-operator. Here
      is an example:
    </p><pre class="programlisting">import mysql.connector
config = {
  'user': 'scott',
  'password': 'tiger',
  'host': '127.0.0.1',
  'database': 'employees',
  'raise_on_warnings': True,
}
cnx = mysql.connector.connect(**config)
cnx.close()</pre></div><div class="section"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="myconnpy_example_ddl"></a>4.2. Creating Tables Using Connector/Python</h2></div></div></div><p>
      All <a class="ulink" href="http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_ddl" target="_top">DDL</a> (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 <code class="literal">employees</code> database. You will need them for
      the other examples.
    </p><p>
      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
      <a class="ulink" href="http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_temporary_table" target="_top">temporary tables</a>,
      which can be created and dropped quickly within an application.
    </p><pre class="programlisting">from __future__ import print_function
import mysql.connector
from mysql.connector import errorcode
DB_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")</pre><p>
      The above code shows how we are storing the
      <code class="literal">CREATE</code> statements in a Python dictionary called
      <code class="literal">TABLES</code>. We also define the database in a global
      variable called <code class="literal">DB_NAME</code>, which allows you to
      easily use a different schema.
    </p><pre class="programlisting">cnx = mysql.connector.connect(user='scott')
cursor = cnx.cursor()</pre><p>
      A single MySQL server can contain multiple
      <a class="ulink" href="http://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_database" target="_top">databases</a>. 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.
    </p><pre class="programlisting">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)</pre><p>
      We first try to change to a particular database using the
      <code class="literal">database</code> property of the connection object
      <code class="literal">cnx</code>. If there is an error, we examine the error
      number to check if the database does not exist. If so, we call the
      <code class="literal">create_database</code> function to create it for us.
    </p><p>
      On any other error, the application exits and displays the error
      message.
    </p><pre class="programlisting">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()</pre><p>
      After we succesfully created or changed to the target database, we
      create the tables by iterating over the items of the
      <code class="literal">TABLES</code> dictionary.
    </p><p>
      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 <span class="quote">“<span class="quote">table already exists</span>”</span> condition for
      illustration purposes. In a real application, we would typically
      avoid the error condition entirely by using the <code class="literal">IF NOT
      EXISTS</code> clause of the <a class="ulink" href="http://dev.mysql.com/doc/refman/5.5/en/create-table.html" target="_top"><code class="literal">CREATE
      TABLE</code></a> statement.)
    </p><p>
      The output would be something like this:
    </p><pre class="programlisting">Creating table employees: already exists.
Creating table salaries: already exists.
Creating table titles: OK
Creating table departments: already exists.
Creating table dept_manager: already exists.
Creating table dept_emp: already exists.</pre><p>
      To populate the employees tables, use the dump files of the
      <a class="ulink" href="http://dev.mysql.com/doc/employee/en/index.html" target="_top">Employee
      Sample Database</a>. Note that you only need the data dump
      files that you will find in an archive named like
      <code class="literal">employees_db-dump-files-1.0.5.tar.bz2</code>. After
      downloading the dump files, do the following from the command
      line, adding connection options…
Large files files are truncated, but you can click here to view the full file