Python ORM framework SQLAlchemy getting started tutorial

SQLAlchemy's philosophy is that the magnitude and performance of SQL databases are important to object sets, while the abstraction of object sets is important to tables and rows.

1. Install SQLAlchemy

Copy codeThe Code is as follows: pip install sqlalchemy
If no import error is reported, the installation is successful.
Copy codeThe Code is as follows: >>> import sqlalchemy
>>> Sqlalchemy. _ version __
'0. 100'
>>>

2. Use sqlalchemy to operate databases

1. define metadata and bind it to the engine
Copy codeThe Code is 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 more information.
>>> From sqlalchemy import *
>>> From sqlalchemy. orm import *
>>> Engine = create_engine ('sqlite: //./sqlalchemy. db', echo = True) # define the engine
>>> Metadata = MetaData (engine) # bind metadata
>>>

2. Create a table and initialize the database.

Copy codeThe Code is as follows:
>>> Users_table = Table ('users', metadata,
... Column ('id', Integer, primary_key = True ),
... Column ('name', String (40 )),
... Column ('email ', String (120 )))
>>>
>>> Users_table.create ()
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)
)


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

Run the preceding code to create a users table with three fields: id, name, and email.

Copy codeThe Code is 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 ";"
Sqlite>. tables
Users
Sqlite>

3. Basic operation, insert

If the table already exists, create is not allowed for the second operation. Use autoload to set
Copy codeThe Code is 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)
10:20:01, 580 INFO sqlalchemy. engine. base. Engine PRAGMA table_info ("users ")
10:20:01, 581 INFO sqlalchemy. engine. base. Engine ()
10:20:01, 582 INFO sqlalchemy. engine. base. Engine PRAGMA foreign_key_list ("users ")
10:20:01, 583 INFO sqlalchemy. engine. base. Engine ()
10:20:01, 583 INFO sqlalchemy. engine. base. Engine PRAGMA index_list ("users ")
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 codeThe Code is as follows:
>>> I = users_table.insert ()
>>> I
<Sqlalchemy. SQL. dml. Insert object at 0x31bc850>
>>> Print I
Insert into users (id, name, email) VALUES (?, ?, ?)
>>> I .exe cute (name = 'rsj217', email = 'rsj21 @ gmail.com ')
10:24:02, 250 INFO sqlalchemy. engine. base. Engine insert into users (name, email) VALUES (?, ?)
10:24:02, 250 INFO sqlalchemy. engine. base. Engine ('rsj217', 'rsj21 @ gmail.com ')
10:24:02, 251 INFO sqlalchemy. engine. base. Engine COMMIT
<Sqlalchemy. engine. result. ResultProxy object at 0x31bce10>
>>> I .exe cute ({'name': 'ghost'}, {'name': 'test '})
10:24:57, 537 INFO sqlalchemy. engine. base. Engine insert into users (name) VALUES (?)
10:24:57, 537 INFO sqlalchemy. engine. base. Engine ('ghost',), ('test ',))
10:24:57, 537 INFO sqlalchemy. engine. base. Engine COMMIT
<Sqlalchemy. engine. result. ResultProxy object at 0x31bcd50>
>>>

The database content is

Copy codeThe Code is as follows:
Sqlite> select * from users;
1 | rsj217 | rsj21@gmail.com
2 | ghost |
3 | test |
Sqlite>

For query deletion and insertion, you must first create an sqlalchemy. SQL. dml object for the instance.

3. Use ORM

Using orm is to map the python class to the table of the database, eliminating the need to write SQL statements directly.

Create ing

Copy codeThe Code is as follows:
>>> Class User (object ):
... Def _ repr _ (self ):
... Return '% s (% r, % r)' % (self. _ class _. _ name __, self. name, self. email)
...
>>> Mapper (User, users_table) # create a ing
<Mapper at 0x31bcfd0; User>
>>> Ul = User ()
>>> Ul. name
>>> Print ul
User (None, None)
>>> Print ul. name
None
>>>

Establish a session

Query

Copy codeThe Code is 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'). first ()
10:44:23, 809 INFO sqlalchemy. engine. base. Engine SELECT users. id AS users_id, users. name AS users_name, users. email AS users_email
FROM users
WHERE users. name =?
LIMIT? OFFSET?
10:44:23, 809 INFO sqlalchemy. engine. base. Engine ('rsj217', 1, 0)
>>> U. name
U'rsj217'
>>>

Insert

Copy codeThe Code is 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 that the session creation method is better for sqlalchemy versions with different sessionmakers.

For more information about how to delete link things, see the official documentation.