Turn: Sqlaichem Tutorial

SQLAlchemy Guide (Tutorial)

Corresponding version:	0.3.4

Directory

• 1 installation
  • 1.1 Installing SQLAlchemy
  • 1.2 Installing a database API
• 2 Quick Start
  • 2.1 Import
  • 2.2 Connecting to a database
• 3 SQLAlchemy is a two-library package
• 4 Manipulating database objects
  • 4.1 Defining meta information, binding to engine
  • 4.2 Creating a Table
  • 4.3 Inserting records
  • 4.4 Queries
  • 4.5 Operation Record
  • 4.6 Inter-table relationships
• 5 Working with ORM
  • 5.1 Creating a Map
  • 5.2 Getting Sessions (session)
  • 5.3 Querying objects
  • 5.4 Modifying data
  • 5.5 Save
  • 5.6 Relationship
  • 5.7 Business
  • 5.8 Next

This introductory guide is used for the quick start of sqlalchemy and facilitates the simple functions of sqlalchemy. If you can skip this part of the entry into the main document will involve more content. The example below is all done in Python interactive mode, and all passes the doctest test.

1 Installation 1.1 Installation SQLAlchemy

Installing from Setuptools is very simple, just run the following command:

# Easy_install SQLAlchemy

This will get the latest version of SQLAlchemy and install it in the Python Cheese shop. Or you can use setup.py to install a release package:

# python setup.py Install

1.2 Installing a database API

SQLAlchemy is designed to operate on a DBAPI implementation, including most common databases. If you have an implementation that supports DBAPI, you can jump into the next section. In addition SQLite is an easy-to-use database that can be started quickly, and he can use the in-memory database. If you want to use SQLite, you will need:

• Pysqlite-sqlite's Python interface
• SQLite function Library

Note that the SQLite library is not required under Windows because the Windows version of Pysqlite is already included. Pysqlite and SQLite can be installed on Linux or FreeBSD by pre-compiling or installing from the source.

The address of the precompiled package is:

Http://initd.org/tracker/pysqlite/wiki/PysqlitePackages

2 Quick Start 2.1 Import

SQLAlchemy provides a complete namespace, as long as the sqlalchemy is imported without its child packages. To facilitate the use of this tutorial, we import all named to the local namespace:

>>> from sqlalchemy Import *

2.2 Connecting to a database

After the import, the next step is to connect to the desired database, represented as (represent) as an engine object. This object handles the management of the connection and the operation of the specific database. Below, we connect the SQLite file-based database "Tutorial.db"

>>> db=create_engine ("sqlite:///tutorial.db")

For more information on creating the database engine, see "db Engines".

3 SQLAlchemy is a two-library package

Now that you have finished installing and connecting to the database, you can start doing something practical. But first, some explanation is needed.

The core of SQLAlchemy has two completely different functions, one working on top of the other. One is the SQL language constructor and the other is ORM . The SQL language constructor allows you to call clauseelements to construct an SQL expression. These clauseelements can be used to execute after being compiled into a string and bound to a database, and return an object called Resultproxy , similar to a result set object, but more like a dbapi high version of the The cursor object.

ORM is a toolset built on the SQL language constructor that maps Python objects to rows in a database and provides a range of interfaces for accessing objects (rows) from the database. When the ORM is working, the SQL language constructor API is called at the bottom, and these common operations are somewhat different. The difference is that you no longer use the rows, but instead use the objects of the custom classes to manipulate them. In addition, the database query method is also different, ORM can generate most of the SQL query, in addition to the class can also define more operations.

The SA is powerful and unmatched, but there are two ways to blend together in a somewhat complex way. The effective way to use SA is to first understand the two different toolsets, which are two different concepts, and people often mixed with SQL language constructors and ORM. The key difference is that the SQL language constructor is used when using a result set in the form of a cursor, whereas an ORM is used when managing a class instance.

This guide first describes the SQL language constructor, which first requires declaring the database information called table metadata . This guide contains examples of SQL constructs, including examples of how to use the SQL language builder effectively.

4 Manipulating database objects

In the core philosophy of SQLAlchemy, tables and classes are different. Because of this, SQLAlchemy provides a way to construct a table (using the table's meta-information table metadata). So we start by constructing the meta-information objects of the tables and customizing the objects that manipulate them. Later we can see the SQLAlchemy ORM, which provides a high-level package of tabular meta information that allows us to load and save Python classes as we wish.

4.1 Defining meta information, binding to engine

First, your table must already be in the metadata collection. We are going to create a simple (handy) table of the metadata and automatically connect to the engine (a schema object is connected to the engine to become bound to the binding binding):

>>> Metadata=boundmetadata (DB)

An equivalent method of constructing a Boundmetadata object is to use the engine URL directly, which will help us call Create_engine

>>> metadata=boundmetadata ("sqlite:///tutorial.db")

Now that we tell metadata about the tables in the database, we can use the (issue) create statement to make the tables and create and execute the SQL statements through them, unless you need to open and close any connections. This is all done automatically. Note that this feature is recommended for use. SQLAlchemy contains all the functions of using patterns for connection management and SQL construction, and can be manipulated on any engine.

The purpose of this tutorial is to teach you to use the "Bound" object, which makes the code simple and easy to read.

4.2 Creating a Table

Using metadata as a basic connection, we can create a table:

>>> users_table=table (' user ', metadata,
...     Column (' user_id ', integer,primary_key=true),
...     Column (' user_name ', String (40)),
...     Column (' Password ', String (10))
... )

As you can see, we've just defined a table called Users and have 3 columns: USER_ID as the primary key, user_name and password. It is now just an object and does not necessarily relate to tables in the database. In order for the table to take effect, we use the Create () method. Interestingly, we can have sqlalchemy send SQL statements to the database while the SQL statements are displayed, as long as you set the Echo option for the boundmetadata associated engine:

>>> Metadata.engine.echo=true
>>> Users_table.create ()
CREATE TABLE Users (
    user_id INTEGER not NULL,
    User_name VARCHAR (40),
    Password VARCHAR (10),
    PRIMARY KEY (user_id)
)
...

Or, if the users table already exists (for example, you run the example for the second time), in which case you can skip the call to the Create () method. You set up to skip the column definition, but instead let sqlalchemy automatically load the definition from the database:

>>> users_table=table (' users ', metadata,autoload=true)
>>> list (users_table.columns) [0].name
' user_id '

The document about tabular meta information is in "Database Meda Data".

4.3 Inserting records

Inserting a record is implemented through the Insert () method of the Table object, which will define a clause object (clause object) (that is, cluseelement) to proxy the INSERT statement:

>>> I=users_table.insert ()
>>> I
<sqlalchemy.sql._insert Object at 0x...>
>>> Print I
INSERT into Users (User_id,user_name,password) VALUES (?,?,?)

When we create this INSERT statement object, the statement itself is bound to the engine and is ready to execute. The Execute () method of the Clause object compiles the object into a specific engine's SQL dialect, and executes the statement:

>>> I.execute (user_name= ' Mary ', password= ' secure ')
INSERT into Users (User_name,password) VALUES (?,?)
[' Mary ', ' secure ']
COMMIT
<sqlalchemy.engine.base.resultproxy Object at 0x...>
>>> I.execute ({' user_name ': ' Tom '},{' user_name ': ' Fred '},{' user_name ': ' Harry '})
INSERT into Users (user_name) VALUES (?)
[[' Tom '],[' Fred '],[' Harry ']
COMMIT
<sqlalchemy.engine.base.resultproxy Object at 0x...>

Note The VALUES clause automatically adjusts the number of parameters. This is because Clauseelement's compilation steps do not depend on a particular database, and so are the parameters that are executed.

When a clause object is constructed, SQLAlchemy binds all values to the parameter. At construction time, parameter bindings always rely on key-value pairs. At compile time, SQLAlchemy will convert them to the appropriate format, based on the Dbapi style of the parameters. This works equally well in the parameter position bindings described in Dbapi.

These documents are inherited from "inserts".

4.4 Queries

We can examine the data that already exists in the users table. Method with the insert example, just the Select () method you need to invoke the table:

>>> S=users_table.select ()
>>> Print S
SELECT Users.user_id,users.user_name,users.password
From users
>>> R=s.execute ()
SELECT Users.user_id,users.user_name,users.password
From users
[]

At this point, we do not ignore the return value of execute (). He is a resultproxy instance that saves the result, and behaves very similar to the cursor object in DBAPI:

>>> R
<sqlalchemy.engine.base.resultproxy Object at 0x...>
>>> R.fetchone ()
(1,u ' Mary ', U ' secure ')
>>> R.fetchall ()
[(2,u ' Tom ', none), (3,u ' Fred ', none), (4,u ' Harry ', none)]

The query condition is the same as the Python expression, using the Column object. The column objects in all expressions are instances of clauseelements, such as the Select, Insert, and table objects themselves:

>>> r=users_table.select (users_table.c.user_name== ' Harry '). Execute ()
SELECT Users.user_id,users.user_name,users.password
From users
WHERE users.user_name=?
[' Harry ']
>>> Row=r.fetchone ()
>>> Print row
(4,u ' Harry ', None)

Fortunately, all standard SQL operations can be constructed with Python expressions, including joins, sort (order), grouping (group), Functions, subqueries (correlated subquery), Unions (union), and so on. The document about the query "simple select".

4.5 Operation Record

You can see that when we print the record, the executable object is returned, and it prints the records in tuples. These records actually support both list and dictionary (dict) interfaces. The dictionary interface allows you to locate a field through the column name of a string, or through the Columns object:

>>> Row.keys ()
[' user_id ', ' user_name ', ' password ']
>>> row[' user_id '],row[1],row[users_table.c.password]
(4,u ' Harry ', None)

It is convenient to locate by using the Column object, because it avoids the way that you can use it.

The result set is also supported for sequence operations. However, there is a slight difference in comparison to select, which allows you to specify the selected columns:

>>> for row in select ([User_table.c.user_id,users_table.c.user_name]). Execute ():
...     Print row
SELECT Users.user_id,users.user_name
From users
[]
(1,u ' Mary ')
... ...

4.6 Inter-table relationships

We can create a second table, email_addresses, which references the users table. Defines the association between tables, using ForeignKey constructs. We will also consider using the form's create statement in the future:

>>> email_addresses_table=table (' email_addresses ', metadata,
...     Column (' address_id ', integer,primary_key=true),
...     Column (' email_address ', String (+), nullable=false),
...     Column (' user_id ', Integer,foreignkey (' users.user_id ')))
>>> Email_addresses_table.create ()
CREATE TABLE email_addresses (
    address_id INTEGER not NULL,
    Email_address VARCHAR (+) not NULL,
    USER_ID INTEGER,
    PRIMARY KEY (address_id),
    FOREIGN KEY (user_id) REFERENCES users (USER_ID)
)
...

The email_addresses table above is associated with the table users through ForeignKey (' users.user_id '). The ForeignKey constructor requires a column object, or a string representing the indicated and column names. When a string argument is used, the referenced table must already exist in the same metadata object, and, of course, it can be another table.

You can try inserting the data below:

>>> Email_addresses_table.insert (). Execute (
...     {' email_address ': ' [email protected] ', ' user_id ': 2},
...     {' email_address ': ' [email protected] ', ' user_id ': 1})
INSERT into Email_addresses (email_address,user_id) VALUES (?,?)
[[' [Email protected] ', 2],[' [email protected] ', 1]]
COMMIT
<sqlalchemy.engine.base.resultproxy Object at 0x...>

Between two tables, we can construct a connection using the join method:

>>> R=users_table.join (email_addresses_table). Select (). Execute ()
SELECT users.user_id, Users.user_name, Users.password,
email_addresses.address_id, Email_addresses.email_address,
email_addresses.user_id
From the users JOIN email_addresses on users.user_id=email_addresses.user_id
[]
>>> print [row for row in R]
[(1, U ' Mary ', U ' secure ', 2, u ' [email protected] ', 1),
(2,u ' Tom ', None, 1, u ' [email protected] ', 2)]

The join method is also a separate function in the sqlalchemy namespace. The join condition indicates the foreign key given by the Table object. Conditions (condition), which can also be called an "on clause", can be explicitly specified, such as this example we query all users who use the email address as the password:

>>> print Join (users_table, email_addresses_table,
...     And_ (users_table.c.user_id==email_addresses_table.c.user_id,
...     users_table.c.password==email_addresses_table.c.email_address)
...     )
Users JOIN email_addresses on users.user_id=email_addresses.user_id and
Users.password=email_address.email_address

5 Working with ORM

Now that we have some knowledge of tables and SQL operations, let's take a look at SQLAlchemy's ORM (Object relational Mapper). With ORM, you can associate a table (and other objects you can query) with Python and put it into a mapping set (mappers). You can then execute the query and return the object instance list instead of the result set. Object Instances are also linked to an object called Session , ensuring that objects are automatically tracked for changes, and that flush can be used to save the results immediately.

5.1 Creating a Map

A mapping typically corresponds to a Python class whose core intent is that "the object of this class is stored as a row of this table." Let's create a class called User , which describes an object and saves it to the Users table. :

>>> class User (object):
...     def __repr__ (self):
...         Return "%s (%r,%r)"% (
...             Self.__class__.__name__,self.user_name,self.password)

This class is a new style class (inherited from object ) and does not require a constructor (provided by default when needed). We have only implemented a __repr__ method that displays basic information about the User object. Note that the __repr__ method applies instance variables user_name and password , which are not yet defined. We can optionally define these properties and handle them, and the SQLAlchemy Mapper constructor automatically manages these and automatically coordinates the column names of the users table. Let's create the mappings and observe the definitions of these properties:

>>> Usermapper=mapper (user,users_table)
>>> Ul=user ()
>>> Print Ul.user_name
None
>>> Print Ul.password
None

The function Mapper returns the newly created mapper instance. This is also the first map we created for the User class, that is, the primary mapping of the class. In general, you do not need to save usermapper variables; the SA ORM automatically manages this mapping.

5.2 Getting Sessions (session)

Once a map has been created, all operations on the map require an important object called the Session . All objects must be loaded into memory via the Session object, as in the object's workspace, through the loading and saving of the mappings. A specific object can only be associated to a Session at a specific time.

By default, the Session object needs to be explicitly created before loading and saving the object. There are several ways to manage a session, but the most concise method is to call create_session ()

>>> session=create_session ()
>>> session
<sqlalchemy.orm.session.session Object at 0x...>

5.3 Querying objects

Session objects have all the methods to load and store objects, and they can also view their status. The session also provides a convenient interface for querying the database, and you can get a query object:

>>> Query=session.query (User)
>>> Print query.select_by (user_name= ' Harry ')
SELECT Users.user_name as Users_user_name, Users.password as Users_password,
users.user_id as users_user_id
From users
WHERE users.user_name=? ORDER by Users.oid
[' Harry ']
[User (U ' Harry ', None)]

All of the query operations of the object are actually through query. The multiple Select methods of the Mapper object are also secretly using the Query object to perform the operation. A Query is always linked to a specific session.

Let's temporarily turn off database Echo and try several methods of Query . The end of the _by method is primarily used for object key parameters. Other methods allow the clauseelement object to be accepted, using the Python expression of the Column object, and the same method we used in the previous section. Using the clauseelement structure to query more verbose, but more flexible:

>>> Metadata.engine.echo=false
>>> print Query.select (user.c.user_id==3)
[User (U ' Fred ', None)]
>>> print Query.get (2)
User (U ' Tom ', None)
>>> Print query.get_by (user_name= ' Mary ')
User (U ' Mary ', U ' secure ')
>>> print Query.selectfirst (user.c.password==none)
User (U ' Tom ', None)
>>> Print Query.count ()
4

Note

The user class has a special attribute, C , which describes the columns of the User Mapping Table object.

user.c.user_name is equivalent to users_table.c.user_name , remember that User is a Python object, and users are The Table object.

5.4 Modifying data

As a small experience, let's look at how to make changes. First, create a new user, "Ed," and then join the session:

>>> Ed=user ()
>>> ed.user_name= ' Ed '
>>> ed.password= ' Edspassword '
>>> Session.save (ed)
>>> Ed in session
True

You can also modify other objects in the database. Use the Query object to load, and then change:

>>> mary=query.get_by (user_name= ' Mary ')
>>> harry=query.get_by (user_name= ' Harry ')
>>> mary.password= ' Marysnewpassword '
>>> harry.password= ' Harrysnewpassword '

At this point, nothing is saved to the database; All of our modifications are in memory. What happens if the other part of the program tries to modify ' Mary '? Because the same session is used, loading ' Mary ' is actually locating the same primary key ' Mary ' and returning the same object instance . This behavior is used to ensure consistency of the database in the session:

>>> mary2=query.get_by (user_name= ' Mary ')
>>> Mary is Mary2
True

In a unique mapping, a single session ensures safe operation of the object.

If two different sessions operate on an object at the same time, concurrency is detected, and the SA uses simple concurrency control to save the object, optionally using a stronger check with IDs. See Mapper Arguments for more details.

5.5 Save

After the user "Ed" was created and the "Mary" and "Harry" were modified, we removed "Fred"

>>> fred=query.get_by (user_name= ' Fred ')
>>> Session.delete (Fred)

Then send the change to the database using the flush () method of the session. Turn on Echo to view the process:

>>> Metadata.engine.echo=true
>>> Session.flush ()
BEGIN
UPDATE users SET password=? WHERE users.user_id=?
[' Marysnewpassword ', 1]
UPDATE users SET password=? WHERE users.user_id=?
[' Harrysnewpassword ', 4]
INSERT into Users (User_name,password) VALUES (?,?)
[' Ed ', ' Edspassword ']
DELETE from users WHERE users.user_id=?
[3]
COMMIT

5.6 Relationship

If a relationship contains additional information, such as a list that contains a mailing address, we can declare it when you create Mapper with relation () . Of course, you can do a lot of things with this relationship, and we'll give you a few simple examples. First, use the users table, which has a foreign key relationship connected to the email_addresses table. Each row in the email_addresses table has a column user_id to refer to a row in the Users table, and multiple rows in the email_addresses table can be referenced The same row in the Users table, which is called a one-to-many relationship.

First, the email_addresses table is processed. We create a new class Address that describes a row in the email_addresses table and also creates a mapping object for the Address class:

>>> class Address (object):
...     def __init__ (self,email_address):
...         Self.email_address=email_address
...     def __repr__ (self):
...         Return "%s (%r)"% (
...             self.__class__.__name__,self.email_address)
>>> Mapper (Address, email_addresses_table)
<sqlalchemy.orm.mapper.mapper Object at 0x...>

We then connect the user and Address classes by creating a relationship using relation () , and add a connection to the user mapping, using add_property< /c9> function:

>>> usermapper.add_property (' Addresses ', Relation (Address))

The function relation () requires a class or map as the first parameter, and there are many options to control the behavior. The user map now adds a property called addresses to each user instance. The SA will automatically detect this one-to-many relationship. And then create a addresses list. When the new User object is created, the list is empty.

Let's see what the database does. When we modify the mapped configuration, it is best to clean up the session so that all loaded User objects can be re-loaded:

>>> Session.clear ()

We can then use the addresses property of the User object to handle the same as a list:

>>> mary=query.get_by (user_name= ' Mary ')
SELECT Users.user_name as Users_user_name, Users.password as Users_password,
users.user_id as users_user_id
From users
WHERE users.user_name=? ORDER by Users.oid
LIMIT 1 OFFSET 0
[' Mary ']
>>> Print [A for a in mary.address]
SELECT email_addresses.user_id as email_address_user_id,
email_addresses.address_id as email_addresses_address_id,
Email_addresses.email_address as Email_addresses_email_address
From email_addresses
WHERE? = email_addresses.user_id ORDER by email_addresses.oid
[1]
[Address (u ' [email protected] ')]

Adding elements to a list is also straightforward. The new Address object will be saved when the session's flush () is called:

>>> mary.addresses.append (Address (' [email protected] '))
>>> Session.flush ()
BEGIN
INSERT into Email_addresses (email_address,user_id) VALUEs (?,?)
[' [Email protected] ', 1]
COMMIT

The parts of the main document regarding the use of mappings are at the following address:

Http://www.sqlalchemy.org/docs/datamapping.myt#datamapping

5.7 Business

You may have noticed that in the example above Session.flush () , SQLAlchemy uses the BEGIN and COMMIT to use the database transaction. The Flush () method uses transactions to perform a series of instructions on some records. If you want to use a larger-scale transaction outside of flush () , you can use the sessiontransaction object, whose build uses session.create_transaction () . The following will execute a very complex SELECT statement, make a lot of changes, and then create a user with two mailboxes, all in the transaction. It will be saved in the middle using flush () and then all changes will be written to the database when the last commit () is executed. We encapsulate transactions in a TRY/EXCEPT statement block to ensure the secure release of resources:

>>> transaction=session.create_transaction ()
>>> Try:
... (ed,harry,mary) =session.query (User). Select (
... User.c.user_name.in_ (' Ed ', ' Harry ', ' Mary '),
... order_by=user.c.user_name
...     )
... del mary.address[1]
... harry.addresses.append (Address (' [email protected] '))
... Session.flush ()
... print "***flushed the session***"
... fred=user ()
... fred.user_name= ' Fred_again '
... fred.addresses.append (Address (' [email protected] '))
... fred.addresses.append (Address (' [email protected] '))
... Session.save (Fred)
... transaction.commit ()
... except:
... transaction.rollback ()
... raise
BEGIN
SELECT Users.user_name as Users_user_name,
Users.password as Users_password,
users.user_id as users_user_id
From users
WHERE users.user_name in (?,?,?) ORDER by Users.user_name
[' Ed ', ' Harry ', ' Mary ']
SELECT email_addresses.user_id as email_addresses_user_id,
email_addresses.address_id as email_addresses_address_id,
Email_addresses.email_address as Email_addresses_email_address
From email_addresses
WHERE? = email_addresses.user_id ORDER by email_addresses.oid
[4]
UPDATE email_addresses SET user_id=? WHERE email_addresses.address_id =?
[None, 3]
INSERT into Email_addresses (email_address, user_id) VALUES (?,?)
[' [Email protected] ', 4]
Flushed the session***
INSERT into users (user_name, password) VALUES (?,?)
[' Fred_again ', None]
INSERT into Email_addresses (email_address, user_id) VALUES (?,?)
[' [Email protected] ', 6]
INSERT into Email_addresses (email_address, user_id) VALUES (?,?)
[' [Email protected] ', 6]
COMMIT

The corresponding main document:

Http://www.sqlalchemy.org/docs/unitofwork.myt#unitofwork

5.8 Next

The SQLAlchemy has been introduced as above. But different people may have different ways of doing things, such as defining the relationship of different styles of mapping, so it is still allowed to use the original SQL to define the table, as well as engine, SQL statement, database connection and so on.

Links: http://gashero.yeax.com/?p=6#id9

Turn: Sqlaichem Tutorial