A library that allows testing of code using pymysql without a running MySQL server
Project description
pymysqlite
pymysqlite
acts as a simple compatibility layer that translates pymysql
calls to sqlite3
calls, allowing you to test MySQL code without having a running MySQL server on hand. Hopefully.
Come again?
Ever had the bright idea to add tests to your database-adjacent Python scripts only to find out that, despite having superficially similar API, you can't just use Sqlite as a drop-in replacement fixture in your tests? No? Well, pretend with me for a while.
Should I use this in my project?
Probably not. This only makes sense for simple projects that don't use an abstraction layer like SQLAlchemy already.
Key differences in pymysql
and sqlite3
The first and most glaring difference is in how each library treats its SQL placeholders - where pymysql
uses %s
and %(name)s
for SQL arguments, sqlite3
uses ?
and :name
instead. You do use placeholders, right?
sqlite_cursor.execute("SELECT * FROM users where id=?", (1, )) # sqlite3
pymysql_cursor.execute("SELECT * FROM users where id=%d", (1, )) # pymysql
Another difference is that while pymysql
allows instantiating a cursor via a context manager, sqlite3
does not:
# pymysql
conn = pymysql.connect(**config)
with conn.cursor() as cursor:
cursor.execute("SELECT * FROM users")
# sqlite3
conn = sqlite3.connect(":memory:")
with conn.cursor() as cursor: # fails
...
cursor = conn.cursor() # works ok
cursor.execute("SELECT * FROM users")
How many rows?
A common usage pattern in pymysql
is as follows:
# pymysql
num_rows = cursor.execute("SELECT * FROM users WHERE id=%s", (id,))
if num_rows:
user = cursor.fetchone()
Unfortunately, sqlite3
has a... difficult relationship when it comes to knowing how many rows a cursor returns. For one, the .execute()
method does not return the number of rows affected, instead returning the cursor instance:
# sqlite3
res = cursor.execute("SELECT * FROM users")
res is cursor # True
res.rowcount == -1 # True
Furthermore, the .rowcount
attribute only returns correct values for INSERT
, UPDATE
, DELETE
, and REPLACE
statements (docs). This means that if num_rows: ...
in the example above would not work as expected.
One possible workaround would be to fetchall()
results a variable and return its __len__
, however this would require us to run the SELECT query twice, and modify the behavior of fetch*
methods. This is a TODO. For now, try to avoid this usage pattern.
# sqlite3
cursor.execute("SELECT * FROM users WHERE id=1")
if row := cursor.fetchone(): # this works fine
...
Example use with pytest
import pytest
import pymysqlite
@pytest.fixture
def mymodule(): # import tested module as a fixture
import mymodule
return mymodule
@pytest.fixture
def fake_db():
connection = pymysqlite.connect(":memory:")
return connection
@pytest.fixture
def load_test_data(fake_db)
fake_db.executescript("import_test_data.sql")
fake_db.commit()
return fake_db
def test_my_method(mymodule, fake_db, load_test_data):
"""testing a method using dependency injection"""
mymodule._my_method(conn=fake_db)
@pytest.fixture
def patch_conn(monkeypatch, mymodule)
monkeypatch.setattr(mymodule, "connect", return_value=fake_db)
def test_without_di(mymodule, patch_conn):
"""testing a method without dependency injection"""
mymodule.my_method()
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 Distributions
Built Distribution
Hashes for pymysqlite-0.0.2-py2.py3-none-any.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | d4bb7b1bd014e1016d9c190cf6471c56cc21ae1cab46a08de96b72e1e892677a |
|
MD5 | b8add46320ce73b709bbc1812f8c546b |
|
BLAKE2b-256 | d8374ac9ea0fb25c30c6764dcf431d035cf1d5068f806640d01858871aa55ec7 |