How to use SQL databases in python without writing…

I’m looking for a way to use a SQL database (such as MySQL, SQLite) in python without actually writing SQL. Example would be something like this (pseudocode):

# INSERT INTO table (firstname, lastname) VALUES ('John', 'Smith')
table.insert({'firstname':'John', 'lastname':'Smith'})

# SELECT * FROM table WHERE name='John'
results = table.select({'firstname': 'John'})
print results
# [ {'firstname':'John', 'lastname':'Smith'} ]

A light wrapper around python’s DB-API, or possibly a very lightweight ORM, would do the job for this. If it’s an ORM, it should allow mapping namedtuples to the DB, since that’s pretty much the only kind of object I’d want to use. I’m sure something like this already exists, but I have trouble finding it 🙂

EDIT Some proposed solutions which aren’t what I had in mind:

SQL Alchemy

good: the insert and select are fairly concise

bad: the objects that are stored and retrieved are not plain python dicts/namedtuples/whatever (possibly they could be made to be namedtuples, but it’s not obvious how)

ugly: having to create a class for each table explicitly makes it just too heavyweight

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, Sequence('user_id_seq'), primary_key=True)
    name = Column(String(50))
    fullname = Column(String(50))
    ...

ed_user = User(name='ed', fullname='Ed Jones', password='edspassword')
session.add(ed_user)

our_user = session.query(User).filter_by(name='ed').first() 
our_user
<User(name='ed', fullname='Ed Jones', password='edspassword')>

PonyORM

good: the way to write queries as generators is pure genius

bad: still using custom objects, not builtin python data types

ugly: same as SQL Alchemy, need to create a class for each table

from pony.orm import *

db = Database()
class Person(db.Entity):
    name = Required(str)
    age = Required(int)

p1 = Person(name='John', age=20)
commit()

persons = select(p for p in Person if p.age > 20)

Answer

You just need to do some more work – by reading the documentation. SQLAlchemy has perhaps the most simplest form of “reverse engineering of the database” (called reflection), detailed here.

The most simple example:

from sqlalchemy import *

metadata = MetaData()
engine = create_engine('mysql+mysqldb://scott:tiger@localhost/foo')
person_table = Table('person', metadata, autoload=True, autoload_with=engine)
q = person_table.insert().values(name='John', age=20)
connection = engine.connect()
connection.execute(q)

How to use SQL databases in python without writing SQL?

Advertisement

Answer