From the toolkit website, “SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that gives application developers the full power and flexibility of SQL.
It provides a full suite of well known enterprise-level persistence patterns, designed for efficient and high-performing database access, adapted into a simple and
Pythonic domain language.”
The SQLAlchemy used for this tutorial is 0.5.5 (with python 2.6, sqlite included). To find out what version do you have, open a python console and type…
$ python Python 2.6.1 (r261:67515, Mar 29 2009, 14:48:32) [GCC 4.1.2 20071124 (Red Hat 4.1.2-42)] on linux2 Type "help", "copyright", "credits" or "license" for more information. >>> import sqlalchemy >>> sqlalchemy.__version__ '0.5.5'
If you don't have it, take a look at the Howto install section from the bottom of this page.
For this first step, we'll be using a separate file, in which we'll include the main stuff, later used in the other files. We'll call this common.py.
Create a table in a database, a class and map these two.
from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey # -------------------------------------------------------- # create a table in a database, a class and map these two Base = declarative_base() class Customers(Base): '''inherits the Base class''' __tablename__ = 'customers' # table fields rec_number = Column(Integer, primary_key = True) name = Column(String) phone = Column(String) def __init__(self, rec_number, name, phone): self.rec_number = rec_number self.name = name self.phone = phone def __repr__(self): return "<Metadata('%d','%s','%s')>" % (self.rec_number,self.name,self.phone)
We've created a table called Customers with three fields (rec_number, name, phone). Because we'll be using as a database engine, sqlite, we don't need to specify the length for String type, but in other engines this can be mandatory (e.g. mysql, postgresql), so just put there String(50) or whatever length do you need.
This method is called declarative, and it should be used preferably from now on.
Next, we'll create a StartDB class, where we're gonna initialize the database engine and a session. Read the next code to find out how:
class StartDB(): engine = None session = None # -------------------------------------------------------- # define what database engine SQLAlchemy will use # we choose for now, sqlite3 def __init__(self): self.engine = create_engine('sqlite:///customers.db', echo = False) Base.metadata.create_all(self.engine) # -------------------------------------------------------- # create a session (handle to the database) # establish Session type, just once for all sessions (configured class) Session = sessionmaker(bind=self.engine) # create a session self.session = Session() def reset(self): # start over if you changed your mind, w/out deleting the db file Base.metadata.drop_all(self.engine)
You can modify echo = False to True and you'll have a very verbose screen ;).
You must also include in the file header:
from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker
You can grab the full python sources from here.
Ok, let's try now to populate our table with some records. For that, we'll be using another file, called main.py where we'll put the rest of the code. In this tutorial page you're gonna see pieces of code, for the whole picture just download all the files and play with them.
# create one customer onecust = Customers(784, "somename", "12334234") # or several at once as list customers = [ Customers(1323, "Ronnie Ab PFA", "+4072312233"), Customers(1324, "Metalex SRL", "+40022312233"), Customers(1254, "Crystal SA", "+4073231977468") ] # work with sessions, add customers to our table session.add(onecust) session.add_all(customers)
If you set echo=True in the common.py file, you're gonna see something like this:
2009-07-15 17:44:36,346 INFO sqlalchemy.engine.base.Engine.0x...88ec BEGIN 2009-07-15 17:44:36,349 INFO sqlalchemy.engine.base.Engine.0x...88ec INSERT INTO customers (rec_number, name, phone) VALUES (?, ?, ?) 2009-07-15 17:44:36,350 INFO sqlalchemy.engine.base.Engine.0x...88ec [784, 'somename', '12334234'] 2009-07-15 17:44:36,440 INFO sqlalchemy.engine.base.Engine.0x...88ec INSERT INTO customers (rec_number, name, phone) VALUES (?, ?, ?) 2009-07-15 17:44:36,440 INFO sqlalchemy.engine.base.Engine.0x...88ec [1323, 'Ronnie Ab PFA', '+4072312233'] 2009-07-15 17:44:36,441 INFO sqlalchemy.engine.base.Engine.0x...88ec INSERT INTO customers (rec_number, name, phone) VALUES (?, ?, ?) 2009-07-15 17:44:36,441 INFO sqlalchemy.engine.base.Engine.0x...88ec [1324, 'Metalex SRL', '+40022312233'] 2009-07-15 17:44:36,442 INFO sqlalchemy.engine.base.Engine.0x...88ec INSERT INTO customers (rec_number, name, phone) VALUES (?, ?, ?) 2009-07-15 17:44:36,442 INFO sqlalchemy.engine.base.Engine.0x...88ec [1254, 'Crystal SA', '+4073231977468'] 2009-07-15 17:44:36,443 INFO sqlalchemy.engine.base.Engine.0x...88ec COMMIT
Get all the customers
all_customers = session.query(Customers).order_by(Customers.name) print "RecNo\tName\t\tPhone" for customer in all_customers: print "%s\t%s\t\t%s" % (customer.rec_number,customer.name,customer.phone)
Get only desired fields
all_customers = session.query(Customers.name) for customer in all_customers: print customer.name
Aliased and label
from sqlalchemy.orm import aliased cust_alias = aliased(Customers, name="ca") # all(), one(), first() immediately issue SQL and return a non-iterator value for row in session.query(cust_alias, cust_alias.name.label("label_for_name_field")).all(): print row.ca, row.label_for_name_field # row.ca will return Customers.__repr__ result
Equals: query.filter(Customers.name == 'testname')
Not Equals: query.filter(Customers.name != 'testname')
IN: query.filter(Customers.name in_(['testname1', 'testname2']))
Is NULL: query.filter(Customers.name == None)
AND/OR: from sqlalchemy import and_, or_
query.filter(and_(Customers.name == None, Customer.phone == None))
query.filter(or_(Customers.name == None, Customer.phone == None))
for customer in session.query(Customers).filter(Customers.name.like("Ronnie%")): print customer.rec_number
You can define your own filter:
for customer in session.query(Customers).filter("rec_number < 1400").order_by("rec_number"): print customer.name
You can grab the full python sources from here.
Couldn't be much simpler. Go to Download section from SQLAlchemy Site, grab the archive, unpack, and in that folder run python setup.py install.