Python's ORM Framework SQLAlchemy Getting Started tutorial _python

The idea of sqlalchemy is that the scale and performance of SQL databases are important to the collection of objects, while the abstraction of object collections is important for tables and rows.

One installation SQLAlchemy

Copy Code code as follows:

Pip Install SQLAlchemy

Import is successful if no error is made

Copy Code code as follows:

>>> Import SQLAlchemy
>>> sqlalchemy.__version__
' 0.9.1 '
>>>

Two uses SQLAlchemy to the database operation

1. Define meta information, bind to engine

Copy Code code as follows:

(env) ghost@ghost-h61m-s2v-b3:~/project/flask/fsql$ python
Python 2.7.3 (Default, APR 10 2013, 05:13:16)
[GCC 4.7.2] on linux2
Type ' help ', ' copyright ', ' credits ' or ' license ' for the more information.
>>> from sqlalchemy Import *
>>> from sqlalchemy.orm Import *
>>> engine = create_engine (' sqlite:///./sqlalchemy.db ', echo=true) # define Engine
>>> metadata = metadata (engine) # binding meta information
>>>

2. Create a table, initialize the database

Copy Code code as follows:

>>> users_table = Table (' Users ', metadata,
... Column (' id ', Integer, Primary_key=true),
... Column (' name ', String (40)),
... Column (' email ', String (120))
>>>
>>> Users_table.create ()
2014-01-09 10:03:32,436 INFO Sqlalchemy.engine.base.Engine
CREATE TABLE Users (
ID INTEGER not NULL,
Name VARCHAR (40),
Email VARCHAR (120),
PRIMARY KEY (ID)
)


2014-01-09 10:03:32,436 INFO sqlalchemy.engine.base.Engine ()
2014-01-09 10:03:32,575 INFO sqlalchemy.engine.base.Engine COMMIT
>>>

Execute the above code, we will create a users table, have ID, name, email three fields

Copy Code code as follows:

(env) ghost@ghost-h61m-s2v-b3:~/project/flask/fsql$ sqlite3 sqlalchemy.db
SQLite version 3.7.13 2012-06-11 02:05:22
Enter '. Help ' for instructions
Enter SQL statements terminated with a ";"
Sqlite>. Tables
Users
Sqlite>

3. Basic operation, insert

If the table already exists, the second run is not allowed to create, using AutoLoad settings

Copy Code code as follows:

>>> from sqlalchemy Import *
>>> from sqlalchemy.orm Import *
>>> engine = create_engine (' sqlite:///./sqlalchemy.db ', echo=true)
>>> metadata = metadata (engine)
>>> users_table = Table (' Users ', metadata, autoload=true)
2014-01-09 10:20:01,580 INFO sqlalchemy.engine.base.Engine PRAGMA table_info ("users")
2014-01-09 10:20:01,581 INFO sqlalchemy.engine.base.Engine ()
2014-01-09 10:20:01,582 INFO sqlalchemy.engine.base.Engine PRAGMA foreign_key_list ("users")
2014-01-09 10:20:01,583 INFO sqlalchemy.engine.base.Engine ()
2014-01-09 10:20:01,583 INFO sqlalchemy.engine.base.Engine PRAGMA index_list ("users")
2014-01-09 10:20:01,583 INFO sqlalchemy.engine.base.Engine ()
>>> users_table
Table (' Users ', MetaData (Bind=engine (sqlite:///./sqlalchemy.db)), Column (' id ', INTEGER (), Table=<users> Primary_key=true, Nullable=false), column (' Name ', VARCHAR (length=40), table=<users>), column (' Email ', VARCHAR ( length=120), table=<users>), Schema=none)
>>>

Instantiate an INSERT handle

Copy Code code as follows:

>>> i = Users_table.insert ()
>>> I
<sqlalchemy.sql.dml.insert Object at 0x31bc850>
>>> Print I
INSERT into users (ID, name, email) VALUES (?,?,?)
>>> I.execute (name= ' rsj217 ', email= ' rsj21@gmail.com ')
2014-01-09 10:24:02,250 INFO sqlalchemy.engine.base.Engine INSERT into users (name, email) VALUES (?,?)
2014-01-09 10:24:02,250 INFO sqlalchemy.engine.base.Engine (' rsj217 ', ' rsj21@gmail.com ')
2014-01-09 10:24:02,251 INFO sqlalchemy.engine.base.Engine COMMIT
<sqlalchemy.engine.result.resultproxy Object at 0x31bce10>
>>> I.execute ({' Name ': ' Ghost '},{' name ': ' Test '})
2014-01-09 10:24:57,537 INFO sqlalchemy.engine.base.Engine INSERT into users (name) VALUES (?)
2014-01-09 10:24:57,537 INFO sqlalchemy.engine.base.Engine (' ghost ',), (' Test ',)
2014-01-09 10:24:57,537 INFO sqlalchemy.engine.base.Engine COMMIT
<sqlalchemy.engine.result.resultproxy Object at 0x31bcd50>
>>>

Database content is

Copy Code code as follows:

Sqlite> select * from users;
1|rsj217|rsj21@gmail.com
2|ghost|
3|test|
Sqlite>

Query Delete and insert similar all need to first instance a Sqlalchemy.sql.dml object

Three uses ORM

Using ORM is to map the Python class to the database table, eliminating direct write SQL statements

Creating mappings

Copy Code code as follows:

>>> class User (object):
... def __repr__ (self):
... return '%s (%r,%r) '% (self.__class__.__name__, self.name, Self.email)
...
>>> Mapper (User, users_table) # Create Mappings
<mapper at 0x31bcfd0; User>
>>> ul = User ()
>>> Ul.name
>>> Print ul
User (None, none)
>>> Print Ul.name
None
>>>

Establish a session

Inquire

Copy Code code as follows:

>>> session = Create_session ()
>>> session
<sqlalchemy.orm.session.session Object at 0x31bef10>
>>> query = Session.query (User)
>>> Query
<sqlalchemy.orm.query.query Object at 0x31bee50>
>>> u = query.filter_by (name= ' rsj217 ').
2014-01-09 10:44:23,809 INFO sqlalchemy.engine.base.Engine SELECT users.id as users_id, users.name as Users_name, USERS.E Mail as Users_email
From users
WHERE users.name =?
LIMIT? OFFSET?
2014-01-09 10:44:23,809 INFO sqlalchemy.engine.base.Engine (' rsj217 ', 1, 0)
>>> U.name
U ' rsj217 '
>>>

Insert

Copy Code code as follows:

>>> from sqlalchemy Import *
>>> from sqlalchemy.orm Import *
>>> engine = create_engine (' sqlite:///./sqlalchemy.db ')
>>> metadata = metadata (engine)
>>> users_table = Table (' Users ', metadata, autoload=true)
>>> class User (object): Pass
...
>>> Mapper (User, users_table)
<mapper at 0x18185d0; User>
>>> session = Sessionmaker (Bind=engine)
>>> session = Session ()
>>> u = User ()
>>> u.name = ' new '
>>> session.add (U)
>>> Session.flush ()
>>> Session.commit ()
>>>

Note the way to establish the session, SQLAlchemy version of the different Sessionmaker way better

Other advanced operations, such as deleting relationships, refer to the official documentation.