Skip to main content

Thin wrapper around mysqlclient. Provides Python iterator for queries. Abstracts away cursor.

Project description

The pymysql_utils module

The pymysql_utils package makes interaction with MySQL from Python more pythonic than its underlying package mysqlclient (formerly MySQL-python), or the alternative underlying package pymysql. Either mysqlclient, or pymysql may be chosen as the foundation of pymysql_utils.

Convenience methods for common MySQL operations, such as managing tables, insertion, updates, and querying are also available. Query results are iterators with next() and nextall() methods

Tested on:

OS MySQL Python
macos mysql 8.0 3.7
macos mysql 8.0 2.7
ubuntu 16.04 Xenial mysql 5.7 3.6
ubuntu 16.04 Xenial mysql 5.7 2.7


from pymysql_utils.pymysql_utils import MySQLDB

# Create a database instance. For one approach to
# dealing with password-protected databases, see
# the Tips Section below.

db = MySQLDB(user='myName', db='myDb')
mySchema = {
  'col1' : 'INT',
  'col2' : 'varchar(255)',

db.createTable('myTable', mySchema)

# Fill the table:
colNames  = ['col1','col2']
colValues = [(10, 'row1'),
             (20, 'row2'),
             (30, 'row3'),             

db.bulkInsert('myTable', colNames, colValues)

# Result objects are iterators:
for result in db.query('SELECT col2 FROM myTable ORDER BY col1'):

# row1
# row2
# row3

A Bit More Detail

The database connection is encapsulated in an instance of MySQLDB. This instance can maintain multiple queries simulataneously. Each query result is an iterator object from which result tuples can be retrieved one by one, using next(), or all at once using nextall(). Here is an example of multiple queries interleaved. Assume the above table myTable is populated in the database.

query_str1 = '''
             SELECT col2
               FROM myTable
              ORDER BY col1

query_str2 = '''
             SELECT col2
               FROM myTable
              WHERE col1 = 20
                 OR col1 = 30
           ORDER BY col1
results1   = db.query(query_str1)
results2   = db.query(query_str2)

# Result objects know their total result count:
# --> 3

# --> 2

# The db object can retrieve the result count
# by the query string:
# --> 3
# --> 'row1'
# --> 'row2'
# --> 'row2'
# --> 'row3'
# --> 'row3'
# --> raises StopIteration

# --> raises ValueError: query exhausted.


  • Many methods return a two-tuple that includes a list of warnings, and a list of errors.

  • Check the in-code documentation for all available methods.

  • A number of frequent SQL operations can conveniently be accomplised via dedicated methods: close, createTable, dropTable, insert, bulkInsert, truncateTable, and update.

    These, or other operations can also be accomplished by using execute() to submit arbitrary SQL

  • A useful idiom for queries known to return a single result, such as a count:


  • The underlying mysqlclient package does not expose the MySQL 5.7+ login-path option. So the MySQLDB() call needs to include the password if one is required. One way to avoid putting passwords into your code is to place the password into a file in a well protected directory, such as ~/.ssh/mysql. Then read the password from there.


# Possibly in a virtual environment:

pip install pymysql_utils
python install

# Testing requires a bit of prep in the local MySQL:
# a database 'unittest' must be created, and a user
# 'unittest' without password must have permissions:
# CREATE DATABASE unittest;   
# CREATE USER unittest@localhost;
#    ON `unittest`.* TO 'unittest'@'localhost';

# The unittests give these instructions as well.

# python test

Selecting Python-only or C-Python

By default pymysql_utils uses mysqlclient, and therefore a C-based API to MySQL servers. Occasionally it may be desirable to use a Python only solution. You may force pymysql_utils to use the pymysql library instead of mysqlclient.

One reason for forcing Python only is a known incompatibility between openssl 1.1.1[a,b,c] and mysqlclient (as of Jul 29, 2017).

To have pymysql_utils use the Python-only pymysql library, do this:

  1. Copy pymysql_utils/pymysql_utils_SAMPLE.cnf to pymysql_utils/pymysql_utils.cnf
  2. Inside this new config file, change

Project details

Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Source Distribution

pymysql_utils-2.1.5.tar.gz (26.6 kB view hashes)

Uploaded source

Built Distribution

pymysql_utils-2.1.5-py3-none-any.whl (26.3 kB view hashes)

Uploaded py3

Supported by

AWS AWS Cloud computing Datadog Datadog Monitoring Facebook / Instagram Facebook / Instagram PSF Sponsor Fastly Fastly CDN Google Google Object Storage and Download Analytics Huawei Huawei PSF Sponsor Microsoft Microsoft PSF Sponsor NVIDIA NVIDIA PSF Sponsor Pingdom Pingdom Monitoring Salesforce Salesforce PSF Sponsor Sentry Sentry Error logging StatusPage StatusPage Status page