Python's ORM Framework SQLAlchemy Getting Started tutorial

Source: Internet
Author: User
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.

  • Contact Us

    The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

    If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

    A Free Trial That Lets You Build Big!

    Start building with 50+ products and up to 12 months usage for Elastic Compute Service

    • Sales Support

      1 on 1 presale consultation

    • After-Sales Support

      24/7 Technical Support 6 Free Tickets per Quarter Faster Response

    • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.