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

Quickstart

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'):
    print(result)

# 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:
results1.result_count()
# --> 3

results2.result_count()
# --> 2

# The db object can retrieve the result count
# by the query string:
db.result_count(query_str1)
# --> 3

results1.next()
# --> 'row1'

results2.next()
# --> 'row2'

results1.next()
# --> 'row2'
results2.next()
# --> 'row3'

results1.next()
# --> 'row3'

results2.next()
# --> raises StopIteration

results2.result_count()
# --> raises ValueError: query exhausted.

Tips:

  • 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:

    db.query('...').next()

  • 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.

Installation

# Possibly in a virtual environment:

pip install pymysql_utils
python setup.py 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;
# GRANT SELECT, INSERT, UPDATE, DELETE,
#       CREATE, DROP, ALTER
#    ON `unittest`.* TO 'unittest'@'localhost';

# The unittests give these instructions as well.

# python setup.py 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
       FORCE_PYTHON_NATIVE = False
   to
       FORCE_PYTHON_NATIVE = True

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 details)

Uploaded Source

Built Distribution

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

Uploaded Python 3

File details

Details for the file pymysql_utils-2.1.5.tar.gz.

File metadata

  • Download URL: pymysql_utils-2.1.5.tar.gz
  • Upload date:
  • Size: 26.6 kB
  • Tags: Source
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/1.12.1 pkginfo/1.4.2 requests/2.21.0 setuptools/41.0.1 requests-toolbelt/0.8.0 tqdm/4.28.1 CPython/3.7.3

File hashes

Hashes for pymysql_utils-2.1.5.tar.gz
Algorithm Hash digest
SHA256 1775fe1a5ac7169cd742090c8137f0f158301359491e42630d0eb4b68d0e3870
MD5 03bdb70649c54f7c9aba28ad1cba7371
BLAKE2b-256 a70472be54147bc6a78969950b5028f1ef5fd052a5fe708e7acb1a221a7760ec

See more details on using hashes here.

File details

Details for the file pymysql_utils-2.1.5-py3-none-any.whl.

File metadata

  • Download URL: pymysql_utils-2.1.5-py3-none-any.whl
  • Upload date:
  • Size: 26.3 kB
  • Tags: Python 3
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/1.12.1 pkginfo/1.4.2 requests/2.21.0 setuptools/41.0.1 requests-toolbelt/0.8.0 tqdm/4.28.1 CPython/3.7.3

File hashes

Hashes for pymysql_utils-2.1.5-py3-none-any.whl
Algorithm Hash digest
SHA256 61394473358bfed2286ba97574e6067a5a2f5b7d2841509ae4b5c04813490af1
MD5 19186d4b00fcf3ba6f6273a4b16be15d
BLAKE2b-256 cc4888c2e1f8cee06a486d5abe8230b627d224bf7ab12556e083e159432efa4c

See more details on using hashes here.

Supported by

AWS Cloud computing and Security Sponsor Datadog Monitoring Fastly CDN Google Download Analytics Pingdom Monitoring Sentry Error logging StatusPage Status page