4

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)
Improve this question
5
  • I think SQL Alchemy is what you're looking for. Commented Dec 29, 2015 at 4:29
  • 1
    This list will help you - Link. Commented Dec 29, 2015 at 4:29
  • 1
    @pushkin: Yeah, I know about SQLAlchemy, but... look at the crazy amount of boilerplate code needed to do anything: docs.sqlalchemy.org/en/latest/orm/tutorial.html This is a lot more heavy-weight than anything I had in mind. What I'd like is (a) the table is already created, figure out the schema from it, I don't want to write it out in code, and (b) the objects that I store or retrieve or use for lookup are just dicts, namedtuples, or whatever, definitely not any kind of object I have to explicitly create a class for. Commented Dec 29, 2015 at 4:38
  • 1
    I think what you should use is a NoSQL database instead of a relational database like MySQL. Try something like Redis or MongoDB. The ORMs are provided in the list provided in the previous comment. Commented Dec 29, 2015 at 5:02
  • @JasonEstibeiro: This does sound a lot like MongoDB, doesn't it? :) Thank you. Commented Dec 29, 2015 at 5:35

2 Answers 2

Reset to default
4

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)
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks, guy, but to make this work I had to change "autoload_with_engine" -> "autoload_with"
2

dataset library is good answer to your question

example:

import dataset

db = dataset.connect('sqlite:///:memory:')

table = db['sometable']
table.insert(dict(name='John Doe', age=37))
table.insert(dict(name='Jane Doe', age=34, gender='female'))

john = table.find_one(name='John Doe')
Improve this answer

1 Comment

dataset is cool, but it doesn't support many important SQL concepts, such as foreign keys between tables and joins.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.