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
, andupdate
.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 theMySQLDB()
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:
- Copy
pymysql_utils/pymysql_utils_SAMPLE.cnf
topymysql_utils/pymysql_utils.cnf
- Inside this new config file, change
FORCE_PYTHON_NATIVE = False
to
FORCE_PYTHON_NATIVE = True
Project details
Release history Release notifications | RSS feed
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distribution
Built Distribution
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
Algorithm | Hash digest | |
---|---|---|
SHA256 |
1775fe1a5ac7169cd742090c8137f0f158301359491e42630d0eb4b68d0e3870
|
|
MD5 |
03bdb70649c54f7c9aba28ad1cba7371
|
|
BLAKE2b-256 |
a70472be54147bc6a78969950b5028f1ef5fd052a5fe708e7acb1a221a7760ec
|
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
Algorithm | Hash digest | |
---|---|---|
SHA256 |
61394473358bfed2286ba97574e6067a5a2f5b7d2841509ae4b5c04813490af1
|
|
MD5 |
19186d4b00fcf3ba6f6273a4b16be15d
|
|
BLAKE2b-256 |
cc4888c2e1f8cee06a486d5abe8230b627d224bf7ab12556e083e159432efa4c
|