SQLAlchemy's philosophy is that the magnitude and performance of SQL databases is important to object collections, and the abstraction of object collections is important for tables and rows.
One installation SQLAlchemy
Copy CodeThe code is as follows:
Pip Install SQLAlchemy
Import if there is no error, the installation succeeds
Copy CodeThe code is as follows:
>>> Import SQLAlchemy
>>> sqlalchemy.__version__
' 0.9.1 '
>>>
Two using SQLAlchemy for database operations
1. Define meta-information, bind to engine
Copy the 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 more information.
>>> from sqlalchemy Import *
>>> from sqlalchemy.orm Import *
>>> engine = create_engine (' sqlite:///./sqlalchemy.db ', echo=true) # define Engines
>>> metadata = metadata (engine) # binding meta information
>>>
2. Create a table, initialize the database
Copy the 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
>>>
Executing the above code, we create a users table with ID, name, email three fields
Copy the 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, inserting
If the table already exists, the second run is not allowed to create, use autoload settings
Copy the 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= , Primary_key=true, Nullable=false), Column (' name ', VARCHAR (length=40), table= ), Column (' email ', VARCHAR (length=120), table= ), Schema=none)
>>>
Instantiate an INSERT handle
Copy the Code code as follows:
>>> i = Users_table.insert ()
>>> I
>>> 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
>>> I.execute ({' Name ': ' Ghost '},{' name ': ' Test '})
2014-01-09 10:24:57,537 INFO sqlalchemy.engine.base.Engine INSERT into users 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
>>>
Database content is
Copy the 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 one Sqlalchemy.sql.dml object
Three use ORM
Using ORM is to map the Python class to the database table, eliminating the direct write SQL statement
Creating mappings
Copy the 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
>>> ul = User ()
>>> Ul.name
>>> Print ul
User (None, none)
>>> Print Ul.name
None
>>>
Establish a session
Inquire
Copy the Code code as follows:
>>> session = Create_session ()
>>> session
>>> query = Session.query (User)
>>> Query
>>> u = query.filter_by (name= ' rsj217 '). First ()
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 the 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)
>>> session = Sessionmaker (bind=engine)
>>> session = Session () br>>>> u = User ()
>>> u.name = ' new '
>>> session.add (u)
>>> session.flu SH ()
>>> session.commit ()
>>>
Pay attention to the way the sessions are established, SQLAlchemy versions are different sessionm Aker Way Better
The rest of the advanced operations, such as deleting relational things, refer to the official documentation.