Shulou(Shulou.com)06/02 Report--

Today, I will talk to you about how to install and use the Python database ORM tool sqlalchemy, which may not be well understood by many people. in order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

SQLAlchemy is a database ORM tool of python, which provides powerful transformation between object models, can meet the needs of most database operations, and supports a variety of database engines (sqlite,mysql,postgres, mongodb, etc.), where basic usage and learning notes are recorded.

I. installation

Install via pip

$pip install SQLAlchemy

2. Use

The first is to connect to the database. SQLALchemy supports multiple database engines. Different database engines have different connection strings. The common ones are

Mysql://username:password@hostname/database postgresql://username:password@hostname/database sqlite:////absolute/path/to/database sqlite:///c:/absolute/path/to/database

For more information on connection strings, see here.

Here is an example of connecting to and using a sqlite database

1. Connection

Use traditional connection to connect and operate the database

From sqlalchemy import create_engine # database connection string DB_CONNECT_STRING = 'sqlite:///:memory:' # create database engine, echo is True, and all sql statements engine = create_engine (DB_CONNECT_STRING, echo=True) # are printed to create a connection. The use here is similar to that of sqlite included in python, which is similar to with engine.connect () as con: # execute the sql statement, if it is to add, delete or modify Takes effect directly and does not require commit rs = con.execute ('SELECT 5') data = rs.fetchone () [0] print "Data:% s"% data

Unlike sqlite, which comes with python, there is no need for Cursor cursors and no commit to execute sql statements

2. Connection transaction

Transactions can be used for batch commit and rollback

From sqlalchemy import create_engine # database connection string DB_CONNECT_STRING = 'sqlite:////Users/zhengxiankai/Desktop/Document/db.sqlite' engine = create_engine (DB_CONNECT_STRING, echo=True) with engine.connect () as connection: trans = connection.begin () try: R1 = connection.execute ("select * from User") R2 = connection.execute ("insert into User (name, age) values (?)",' bomo' 24) trans.commit () except: trans.rollback () raise

3. Session

Connection is a general way to use a database. Sqlalchemy also provides another way to manipulate the database. Through session objects, session can record and track changes in data, submit at the appropriate time, and support powerful ORM functions. Here are the basic

From sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker # Database connection string DB_CONNECT_STRING = 'sqlite:////Users/zhengxiankai/Desktop/Document/db.sqlite' # create database engine, echo is True, and all sql statements engine = create_engine (DB_CONNECT_STRING) will be printed Echo=True) # create session class DB_Session = sessionmaker (bind=engine) # create session object session = DB_Session () # dosomething with session # close after use You can also use with session.close ()

A session object has been created above, and then you can manipulate the database. Session also supports manipulating the database through sql statements.

Session.execute ('select * from User') session.execute ("insert into User (name, age) values (' bomo', 13)") session.execute ("insert into User (name, age) values (: name,: age)", {'name':' bomo', 'age':12}) # commit session.commit () is required for additions and deletions

Note that the parameter uses dict and uses: key in the sql statement.

4. ORM

The above briefly introduces the simple use of sql. Since it is the ORM framework, we first define two model classes, User and Role,sqlalchemy, whose model classes inherit from a class generated by the declarative_base () method. We first define a module Models.py to generate the Base class.

From sqlalchemy.ext.declarative import declarative_base Base = declarative_base ()

User.py

From sqlalchemy import Column, Integer, String from Models import Base class User (Base): _ _ tablename__ = 'User' id = Column (' id', Integer, primary_key=True, autoincrement=True) name = Column ('name', String (50)) age = Column (' age', Integer)

Role.py

From sqlalchemy import Column, Integer, String from Models import Base class Role (Base): _ _ tablename__ = 'Role' id = Column (' id', Integer, primary_key=True, autoincrement=True) name = Column ('name', String (50))

It is easy to see from the above that the model here corresponds to the tables in the database. The types supported by the model are Integer, String, Boolean, Date, DateTime, Float. For more types, including the Python corresponding to the type, see here.

Column constructor related settings

Name: name

Type_: column type

Autoincrement: self-increasing

Default: default

Index: index

Nullable: empty

Primary_key: foreign key

For more information, see here.

Next, add, delete, modify and check through session.

From sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker from User import User from Role import Role from Models import Base DB_CONNECT_STRING = 'sqlite:////Users/zhengxiankai/Desktop/Document/db.sqlite' engine = create_engine (DB_CONNECT_STRING, echo=True) DB_Session = sessionmaker (bind=engine) session = DB_Session () # 1. Create a table (not if it already exists) Base.metadata.create_all (engine) # 2. Insert data u = User (name = 'tobi', age = 200) r = Role (name =' user') # 2.1Use add, if it already exists, it will report an error session.add (u) session.add (r) session.commit () print r.id # 3 modify data # 3.1use the merge method, if it exists, modify it If it does not exist, insert r.name = 'admin' session.merge (r) # 3.2 you can also modify session.query (Role) .filter (Role.id = = 1). Update ({' name': 'admin'}) # 4 in this way. Delete data session.query (Role). Filter (Role.id = = 1). Delete () # 5. Query data # 5.1 returns the second item user = session.query (User) .get (2) # 5.2 returns item 2-3 in the result set users = session.query (User) [1:3] # 5.3 query condition user = session.query (User) .filter (User.id < 6). First () # 5.4 sort users = session.query (User). Order_by (User.name) # 5.5 Descending (need to import desc method) from sqlalchemy import desc users = session.query (User). Order_by (desc (User.name)) # 5.6query only some attributes users = session.query (User.name) .order_by (desc (User.name)) for user in users: print user.name # 5.7.An alias users = session.query (User.name.label ('user_name')) .all () for user in for the column of the result set Users: print user.user_name # 5.8Deduplicate query (need to import distinct method) from sqlalchemy import distinct users = session.query (distinct (User.name) .label ('name')) .all () # 5.9Statistical query user_count = session.query (User.name) .order _ by (User.name). Count () age_avg = session.query (func.avg (User.age)). First () age_sum = session.query (func) .sum (User.age) .first () # 5.10 grouping query users = session.query (func.count (User.name) .label ('count')) User.age) .group_by (User.age) for user in users: print 'age: {0}, count: {1}' .format (user.age, user.count) session.close ()

5. Multi-table relation

All of the above operations are based on single table operations, the following is the use of multiple tables and relationships, we modify the above two tables to add foreign key associations (one-to-many and many-to-one)

User model

From sqlalchemy import Column, Integer, String from sqlalchemy import ForeignKey from sqlalchemy.orm import relationship from Models import Base class User (Base): _ _ tablename__ = 'users' id = Column (' id', Integer, primary_key=True, autoincrement=True) name = Column ('name', String (50)) age = Column (' age', Integer) # add role id foreign key (associated with id attribute of Role table) role_id = Column ('role_id', Integer) ForeignKey ('roles.id')) # add the same foreign key second_role_id = Column (' second_role_id', Integer, ForeignKey ('roles.id')) # add relation attribute Role = relationship ('Role', foreign_keys='User.role_id', backref='User_role_id') # add relation attribute on role_id foreign key, and second_role = relationship (' Role', foreign_keys='User.second_role_id', backref='User_second_role_id') on second_role_id foreign key

Role model

From sqlalchemy import Column, Integer, String from sqlalchemy.orm import relationship from Models import Base class Role (Base): _ _ tablename__ = 'roles' id = Column (' id', Integer, primary_key=True, autoincrement=True) name = Column ('name', String (50)) # add relational attributes, associate to User.role_id attributes users = relationship ("User", foreign_keys='User.role_id', backref= "Role_users") # add relational attributes Associate to second_users = relationship ("User", foreign_keys='User.second_role_id', backref= "Role_second_users") on the User.second_role_id attribute

One thing to note here is that when setting foreign keys, ForeignKey ('roles.id') uses table names and table columns, and when setting associated properties, relationship (' Role', foreign_keys='User.role_id', backref='User_role_id'), where foreign_keys uses class names and property names

And then you can use it.

U = User (name='tobi', age=200) R1 = Role (name='admin') R2 = Role (name='user') u.role = R1 u.second_role = R2 session.add (u) session.commit () # query (for relational attributes associated with foreign keys can be accessed directly Session will query the database when needed) roles = session.query (Role) .all () for role in roles: print 'role: {0} users' for user in role.users: print'\ t {0} '.format (user.name) print' role: {0} second_users' for user in role.second_users: print'\ t {0} '.format (user.name)

The above shows an one-to-many (many-to-one) relationship, and an one-to-one, many-to-many relationship. If you want to represent an one-to-one relationship, set uselist to False when defining relationship (default is True), as in Role

Class Role (Base):... User = relationship ("User", uselist=False, foreign_keys='User.role_id', backref= "Role_user")

6. Multi-table query

Multi-table queries usually use join for table join. * the parameter is the table name, and the second parameter is the condition, for example

Users = db.session.query (User) .join (Role, Role.id = = User.role_id) for u in users: print u.name

Join is an inner join, and there is a left join outerjoin, which is similar to join. Right join and full outer link are not supported on version 1.0. generally speaking, these two methods of combining query are basically sufficient. Version 1.1 seems to add support for right join and full outer join, but it is only a preview version at present.

You can also query multiple tables directly, as follows

Result = db.session.query (User, Role) .filter (User.role_id = Role.id) # two tables are selected here, and use tuples to get data for u, rin result: print u.name

III. Database migration

Sqlalchemy database migration / upgrade has two libraries that support alembic and sqlalchemy-migrate

Since sqlalchemy-migrate released version 0.7.2 in 2011, it has stopped updating, is no longer maintained, and has accumulated a lot of bug, while alembic appeared later, was developed by the authors of sqlalchemy, and has good community support, so I only learn alembic here.

Alembic implements version management control similar to git/svn, and we can maintain the version of each upgrade database through alembic.

1. Installation

With pip installation, pip automatically installs related dependencies

$pip install alembic

two。 Initialization

Run in the project root directory after the installation is complete

Yourproject/ alembic.ini YOUR_ALEMBIC_DIR/ env.py README script.py.mako versions/ 3512b954651e_add_account.py 2b1ae634e5cd_add_order_id.py 3adcc9a56557_rename_username_field.py

Among them

Alembic.ini provides some basic configurations

Each time env.py executes Alembic, it loads this module, mainly providing a connection to the project Sqlalchemy Model.

Script.py.mako migration script generation template

Versions stores the generated migration script directory

By default, the creation is based on a single database. If you need to support multiple databases or others, you can view the supported templates through alembic list_templates.

$alembic list_templates Available templates: generic-Generic single-database configuration. Multidb-Rudimentary multi-database configuration. Pylons-Configuration that reads from a Pylons project environment. Templates are used via the 'init' command, e.g.: alembic init-- template generic. / scripts

3. Configuration

Before you use it, you need to configure the link string, open the alembic.ini file, and set the sqlalchemy.url connection string, for example

Sqlalchemy.url = sqlite:////Users/zhengxiankai/Desktop/database.db

For other parameters, please see the official website description: http://alembic.zzzcomputing.com/en/latest/tutorial.html

4. Create a database version

Next we create a database version and create two new tables

$alembic revision-m'create table'

Create a version (a python file 1a8a0d799b33_create_table.py will be created in the yourproject/YOUR_ALEMBIC_DIR/versions/ folder)

The python module contains two methods: upgrade and downgrade. Add some logic to the new table here.

"" from alembic import op import sqlalchemy as sa # revision identifiers, used by Alembic. "" create table Revision ID: 4fd533a56b34 Revises: Create Date: 2016-09-18 1715 20 Create Date 27.667100 "" Revision = '4fd533a56b34' down_revision = None branch_labels = None depends_on = None def upgrade (): # add table op.create_table (' account', sa.Column ('id', sa.Integer, primary_key=True), sa.Column (' name', sa.String (50), nullable=False), sa.Column ('description', sa.Unicode ) # add column # op.add_column ('account', sa.Column (' last_transaction_date', sa.DateTime)) def downgrade (): # delete table op.drop_table ('account') # delete column # op.drop_column (' account', 'last_transaction_date')

The op object is used here. For more API use of the op object, see here

5. Upgrade the database

Just implemented the method of upgrade and downgrade, upgrade the database to the * version with the following command

$alembic upgrade head

At this time, you can see that there are two more tables in the database, alembic_version and account,alembic_version, to store the database version.

Other commands for upgrades and demotions include the following

# upgrade to * version $alembic upgrade head # down to initial version $alembic downgrade base # up two levels $alembic upgrade + 2 # downgrade $alembic downgrade-1 # upgrade to established version $alembic upgrade e93b8d488143 # View current version $alembic current # check historical version details $alembic history-- verbose # View historical version (- r parameter) similar slice $alembic history-r1975ea:ae1027 $alembic history-r-3:current $alembic history-r1975ea:

6. Upgrade the database through metadata

Above we upgrade and downgrade through API, we can also update the database directly through metadata, that is, automatically generate upgrade code, first define two Model (User,Role), here I define three files

Yourproject/ YOUR_ALEMBIC_DIR/ tutorial/Db Models.py User.py Role.py

The code is put together.

From sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String Base = declarative_base () class User (Base): _ _ tablename__ = 'users' id = Column (' id', Integer, primary_key=True, autoincrement=True) name = Column ('name', String) class Role (Base): _ _ tablename__ =' roles' id = Column ('id', Integer, primary_key=True, autoincrement=True) name = Column (' name', String)

Configure metadata in YOUR_ALEMBIC_DIR/env.py

Target_metadata = None

Change to

Import os import sys # here you need to add a relative path to sys.path, otherwise the reference will fail. You have tried to use a relative path, but it doesn't work well, so you should use this method to rely on sys.path.append (os.path.abspath (os.path.join (os.getcwd (), ".. / yourproject/tutorial/Db")) from User import User from Role import Role from Models import Base target_metadata = Base.metadata.

The address obtained by os.path.join (os.getcwd ()) is not the path of env.py, but the root directory.

Adding the-- autogenerate parameter when creating the database version generates a script from the Base.metadata metadata

$alembic revision-autogenerate-m "add user table"

At this time, the upgrade code will be generated.

"" from alembic import op import sqlalchemy as sa # revision identifiers, used by Alembic. "" add user table Revision ID: 97de1533584a Revises: 8678ab6d48c1 Create Date: 2016-09-19 21 Fringe 58 purl 00.758410 "" Revision = '97de1533584a' down_revision = '8678ab6d48c1' branch_labels = None depends_on = None def upgrade (): # commands auto generated by Alembic-please adjust! # op.create_table (' roles', sa.Column ('id', sa.Integer (), nullable=False), sa.Column (' name', sa.String (), nullable=True), sa.PrimaryKeyConstraint ('id')) op.create_table (' users', sa.Column ('id') Sa.Integer (), nullable=False), sa.Column ('name', sa.String (), nullable=True), sa.PrimaryKeyConstraint (' id')) op.drop_table ('account') # # end Alembic commands # def downgrade (): # commands auto generated by Alembic-please adjust! # op.create_table (' account', sa.Column ('id', sa.INTEGER (), nullable=False), sa.Column (' name') Sa.VARCHAR (length=50), nullable=False), sa.Column ('description', sa.VARCHAR (length=200), nullable=True), sa.Column (' last_transaction_date', sa.DATETIME (), nullable=True), sa.PrimaryKeyConstraint ('id') op.drop_table (' users') op.drop_table ('roles') # # end Alembic commands # #

Since I do not define the account model, it will be recognized as deletion, and if the column declaration of model is deleted, it will be recognized as delete column. We can also modify the automatically generated version ourselves, and then execute the upgrade command to upgrade alembic upgrade head.

It is important to note that

The classes declared by Base.metadata must correspond to one by one in the database. If there is a table in the database but not in the metadata, it will be recognized as deleting the table.

Revision needs to be upgraded to a * version before it can be executed.

Before configuring Base, you need to make sure that all Model has been executed (that is, imported) once, otherwise it cannot be read, that is, all Model needs to be import in.

Database upgrade is risky. Check the upgrade function before upgrading, and make a backup if possible.

IV. Frequently asked questions

1. String length problem

If you use a mysql database, the String type corresponds to the VARCHAR type, and the length needs to be specified, otherwise the following error will be reported, but will not appear in sqlite

(in table 'user', column' name'): after reading the above, do you have any further understanding of how to install and use the Python database ORM tool sqlalchemy? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.