Skip to main content

SQL for the enhanced.

Project description

Latest Version Build Coverage License

ezrecords is a very simple, but powerful, library for making raw SQL queries to most relational databases.

ezrecords = Kenneth Reitz’s records + Justin Vincent’s ezsql + WordPress’ wpdb - SQLAlchemy.

Just write SQL. No bells, no whistles. This common task can be surprisingly difficult with the standard tools available. This library strives to make this workflow as simple as possible, while providing an elegant interface to work with your query results.

Database support includes SQLite, Postgres, and MySQL (drivers not included).


  • records is awesome

  • ezsql and wpdb have very nice API, so it makes for an easy transition from PHP to Python

  • Our love for crafting well written and performant SQL queries is not questionable, but I think a few helpers for some basic DML and recurring queries would help

  • “The ORM takes two brilliant ideas and incapacitates them both.”, said a very wise man. So, as long as possible I want to keep away from sqlalchemy or the like.



import logging
from ezrecords.mysqldb import MySQLDb

logger = logging.getLogger()

# connect
db = MySQLDb(db_url="mysql://root:passwd@", logger=logger) # logger is optional

# enable debugging - optional
db.save_queries = True  # save queries and execution time
db.show_sql = True  # show SQL code being executed. logger above is required for logging to work
db.show_errors = True  # show errors

create_user_table = """
CREATE TABLE test_user (
    username varchar(255) UNIQUE,
    password varchar(255),
    created_at TIMESTAMP,
    created_at_gmt TIMESTAMP,
db.query(create_table) # run generic SQL

create_numbers_table = """
    ints int,
    floats float

insert_numbers_sql = "INSERT INTO numbers (ints, floats) VALUES (%d, %f)" # DB API only accepts %s, so we replace %d and %f by %s internally
db.query(insert_numbers_sql, 3, 3.14) # run generic queries with params

# insert records
db.insert('test_user', username='scott', password='tiger',
db.insert('test_user', {'username': 'JONES', 'password': 'STEEL'})

# bulk_insert records
db.bulk_insert('test_user', ('username', 'password'), [('scott', 'tiger'), ('JONES', 'STEEL')])

# Update records
db.update('test_user', {'password': 'shepard'}, {'username': 'scott'})

# Delete records
db.delete('test_user', {'username': None}) # None is converted to NULL

# Sanitize query
db.prepare("""INSERT INTO postmeta (post_id, meta_key, meta_value) VALUES ( '%d', "%s", %%s )')""", 10, "Harriet's Adages", "WordPress' database interface is like Sunday Morning: Easy.")

# Call stored procedures
db.call_procedure('adds', 1, 2)

# Get single variable/value
db.get_var('SELECT version()')

# Get specific row from many results
db.get_row('SELECT * FROM test_user', row_offset=1) # if offset not given the first row is returned

# Get specific column from many results
db.get_col('SELECT username, password FROM test_user', column_offset='password')  # offset can be numeric too

# Get results in specific format
db.get_results('SELECT username, password FROM test_user', 'json')
# Get last inserted ID from AUTO_INCREMENT/SERIAL fields
db.insert('test_user', username='scott', password='tiger',

# Get number of affected rows from previus query

# Switch to another database

# Check query timing
# execute long running query

# Transactions
# ---
db.commit() # or db.rollback()

# Data export
rows = db.query('SELECT * FROM table')
rows.export('csv') # yaml, json, xls, xlsx

# Goodies
db.db_version() # get server version
db.exists('table') # check if table exists
db.get_table_names() # get list of tables in database
db.flush() # clear cache results


ezrecords -h
ezrecords "SELECT version() AS version" "json" --url="mysql://root:passwd@"
ezrecords "SELECT version() AS version" "json" --url="postgres://postgres:passwd@"
ezrecords "SELECT sqlite_version() AS version" "json" --url="sqlite:///:memory:"

Thank you

Thanks for checking this library out! I hope you find it useful.

Of course, there’s always room for improvement. Feel free to open an issue so we can make ezrecords better, faster, and stronger.

Download and Install

Until the module is made available on pypi, you can install this module directly from github with:

pip install -e git+

ezrecords runs with Python 2.7 and 3.5.

Documentation Generation

# edit documentation in _docs
cd _docs
make singlehtml
cd ..
cp -fR _docs/_build/singlehtml/* docs/

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

ezrecords-0.3.0.tar.gz (182.3 kB view hashes)

Uploaded source

Supported by

AWS AWS Cloud computing Datadog Datadog Monitoring Fastly Fastly CDN Google Google Object Storage and Download Analytics Microsoft Microsoft PSF Sponsor Pingdom Pingdom Monitoring Sentry Sentry Error logging StatusPage StatusPage Status page