User Tools

Site Tools


python:sqlalchemytut

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

python:sqlalchemytut [2013/03/16 17:41] (current)
Line 1: Line 1:
 +==== SQL Alchemy - short tutorial ====
 +15.07.2009
  
 +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...
 +
 +<code python>
 +$ 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'​
 +</​code>​
 +
 +If you don't have it, take a look at the //Howto install// section from the bottom of this page.
 +
 +
 +=== First steps: setup the database engine, database and create a session ===
 +
 +
 +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.
 +
 +<code python>
 +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)
 +
 +</​code>​
 +
 +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:
 +
 +<code python>
 +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)
 +</​code>​
 +
 +You can modify echo = False to True and you'll have a very verbose screen ;).
 +
 +You must also include in the file header:
 +
 +<code python>
 +from sqlalchemy import create_engine
 +from sqlalchemy.orm import sessionmaker
 +</​code>​
 +
 +You can grab the full python sources from {{:​pytut:​sa_tutorial_files.zip|here}}.
 +
 +
 +=== Insert some data into the table ===
 +
 +
 +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 {{:​pytut:​sa_tutorial_files.zip|download}} all the files and play with them. 
 +
 +<code python>
 +# 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)
 +</​code>​
 +
 +If you set echo=True in the common.py file, you're gonna see something like this:
 +
 +<​code>​
 +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
 +</​code>​
 +
 +
 +=== Queries - selecting data ===
 +
 +__Get all the customers__
 +<code python>
 +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)
 +</​code>​
 +
 +
 +__Get only desired fields__
 +<code python>
 +all_customers = session.query(Customers.name)
 +for customer in all_customers:​
 +    print customer.name
 +</​code> ​   ​
 +
 +
 +__Aliased and label__
 +<code python>
 +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
 +</​code>​
 +
 +
 +__Filters__
 +
 +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))\\ ​
 +
 +
 +<code python>
 +for customer in session.query(Customers).filter(Customers.name.like("​Ronnie%"​)):​
 +    print customer.rec_number
 +</​code>​
 +
 +You can define your own filter:
 +<code python>
 +for customer in session.query(Customers).filter("​rec_number < 1400"​).order_by("​rec_number"​):​
 +    print customer.name
 +</​code>​
 +
 +You can grab the full python sources from {{:​pytut:​sa_tutorial_files.zip|here}}.
 +
 +
 +=== Howto install ===
 +
 +
 +Couldn'​t be much simpler. Go to Download section from [[ http://​www.sqlalchemy.org/​download.html | SQLAlchemy Site]], grab the archive, unpack, and in that folder run **python setup.py install.**
python/sqlalchemytut.txt ยท Last modified: 2013/03/16 17:41 (external edit)