Use the SQLAlchemy tutorial in the Python program and Flask framework, flasksqlalchemy

Use the SQLAlchemy tutorial in the Python program and Flask framework, flasksqlalchemy

ORM
Once upon a time, when programmers were developing SQL statements with fear, they always had to worry about it. What should they do if the SQL statements were wrong and the database was broken? Or to obtain some data, internal and external connections, function stored procedures, and so on. Without a doubt, if I don't understand this, I think it's a twist. Maybe I jumped into the trap one day, so I don't want to say that I don't want to answer it.

The emergence of ORM makes SQL-fearing developers see the ropes in the pitfalls, as if the sky is not so dark, at least dark, and we have eyes. As the name suggests, ORM Object Relational ing, in short, is to map the tables of a database to the class (class) of the programming language ).

There are many well-known ORM frameworks in python. The top-name SQLAlchemy is a well-known ORM framework in the python world. Peewee, strom, pyorm, and SQLObject In the Jianghu region are all cool, but SQLAlchemy is the best choice in the end.

SQLAlchemy Introduction
SQLAlchemy is divided into two parts, which are used for object ing of ORM, and the other is the core SQL expression. First, it is easy to understand. Pure ORM. This is not an ORM, but an encapsulation of DBAPI. Of course, it also provides many methods to avoid writing SQL directly, but through some SQL expressions. SQLAlchemy can be used in three ways.

• Use SQL expressions to write SQL expressions through SQLAlchemy.
• Use raw SQL to directly write SQL
• Avoid writing SQL Directly Using ORM

This article first discusses the usage of SQL expresstion in SQLAlchemy. Follow the official SQL Expression Language Tutorial.

Why Learning SQL expresstion instead of directly connecting to ORM? The latter two are the basis of the orm. In addition, we do not use the orm, and the later two can also work well, and the code is more readable. Use SQLAlchemy as a dbapi. First, the SQLAlchemy built-in database connection pool solves the cumbersome handling of connection operations. Secondly, it provides convenient and powerful log functions. Finally, it is difficult to implement complicated query statements by simply using ORM.

Practice
Connect to database
First, import the sqlalchemy database and establish a database connection. mysql is used here. Using the create_engine Method

from sqlalchemy import create_engineengine = create_engine("mysql://root:@localhost:3306/webpy?charset=utf8",encoding="utf-8", echo=True)

The create_engine method connects to the database and returns a db object. Parameter Representation in

Database Type: // User name: Password (blank if there is no password) @ Database Host address/Database Name? Encoding
Echo = True is used to facilitate logging on the console to output some SQL information. The default value is False.
This engine object can be used to directly execute the query. For example, engine.exe cute ("SELECT * FROM user") can also obtain connections in the query through the engine, for example, conn = engine. connect () is queried using the conn.exe cute () method. What is the difference between the two?

Execute SQL directly using the engine, called connnectionless execution,
Get conn using engine. connect () and execute SQL using conn, called connection execution
The main difference lies in whether the transaction mode is used. If transaction is not involved, the two methods have the same effect. The latter is recommended on the official website.
Define table
Only when a data table is defined can the SQL expression be operated. After all, the SQL expression table is determined by sqlalchemy. Do I need to define a data table if the database already exists? Of course, this is actually a ing relationship. If it is not specified, the query expression does not know the operation that is appended to the table. Of course, when defining it, pay attention to the table name and field name, the code and data must be consistent. After the data table is defined, the data table can be created. Once the data table is created, the created code is run again, and the database will not be created.

#-*-Coding: UTF-8-*-_ author _ = 'ghost' from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData, foreignKey # connect to the Database engine = create_engine ("mysql: // root: @ localhost: 3306/webpy? Charset = utf8 ", encoding =" UTF-8 ", echo = True) # obtain metadata MetaData = metadata () # Define Table user = Table ('user', metadata, column ('id', Integer, primary_key = True), Column ('name', String (20), Column ('fullname', String (40 )),) address = Table ('address', metadata, Column ('id', Integer, primary_key = True), Column ('user _ id', None, ForeignKey ('user. id '), Column ('email', String (60), nullable = False) # create a data table. If the data table exists, ignore metadata. create_all (engine) # obtain the database connection conn = engine. connect ()

Insert
With data tables and connection objects, the corresponding database operations are simple.

>>> I = user. insert () # Use query> I <sqlalchemy. SQL. dml. insert object at 0x0000000002637748 >>>> print I # SQL statement of the internal component INSERT INTO "user" (id, name, fullname) VALUES (: id,: name ,: fullname) >>> u = dict (name = 'jack', fullname = 'Jack jone') >>>> r = conn.exe cute (I, ** u) # execute the query, the first parameter is the query object, and the second parameter is the inserted data dictionary. If multiple objects are inserted, put the object dictionary in the list >>> r <sqlalchemy. engine. result. resultProxy object at 0x0000000002EF9390 >>> r. inserted_primary_key # Return the primary key id of the inserted row [4L] >>> addresses [{'user _ id': 1, 'email ': 'Jack @ yahoo.com '}, {'user _ id': 1, 'email ': 'Jack @ msn.com'}, {'user _ id': 2, 'email ': 'www @ www.org '}, {'user _ id': 2, 'email ': 'wendy @ aol.com'}] >>> I = address. insert () >>> r = conn.exe cute (I, addresses) # insert multiple records >>> r <sqlalchemy. engine. result. resultProxy object at 0x0000000002EB5080 >>> r. rowcount # The number of affected rows is returned. 4L >>> I = user. insert (). values (name = 'Tom ', fullname = 'Tom Jim') >>> I. compile () <sqlalchemy. SQL. compiler. SQLCompiler object at 0x0000000002F6F390 >>> print I. compile () insert into "user" (name, fullname) VALUES (: name,: fullname) >>>> print I. compile (). params {'fullname': 'Tom Jim ', 'name': 'Tom'} >>> r = conn.exe cute (I) >>> r. rowcount1L

Query select
The query method is flexible. Most of the time, the select method under sqlalchemy. SQL is used.

>>> S = select ([user]) # query the user table >>> s <sqlalchemy. SQL. selectable. select at 0x25a7748; Select object >>> print sSELECT "user ". id, "user ". name, "user ". fullname FROM "user"

If you want to query custom fields, but use the user's cloumn object, for example

>>> User. c # column object of the user field <sqlalchemy. SQL. base. immutableColumnCollection object at 0x0000000002E804A8 >>> print user. c ['user. id', 'user. name', 'user. fullname '] >>> s = select ([user. c. name, user. c. fullname]) >>> r = conn.exe cute (s) >>> r <sqlalchemy. engine. result. resultProxy object at 0x00000000025A7748 >>> r. rowcount # Number of affected rows 5L >>> ru = r. fetchall () >>> ru [(u 'hello', u 'Hello World'), (u 'jack', u 'Jack jone'), (u 'jack ', u 'Jack jone'), (u 'jack', u 'Jack jone'), (u 'Tom ', u 'Tom Jim')]> r <sqlalchemy. engine. result. resultProxy object at 0x00000000025A7748 >>> r. closed # As long as r. after fetchall (), the ResultProxy object True is automatically disabled.

Query two tables at the same time

>>> S = select ([user. c. name, address. c. user_id]). where (user. c. id = address. c. user_id) # Use the conditions for comparing fields> s <sqlalchemy. SQL. selectable. select at 0x2f03390; Select object >>> print sSELECT "user ". name, address. user_id FROM "user", address WHERE "user ". id = address. user_id

Operator

>>> Print user. c. id = address. c. user_id # returns a compiled string "user ". id = address. user_id >>> print user. c. id = 7 "user ". id =: id_1 # compile the SQL statement segment string with parameters >>> print user. c. id! = 7 "user". id! =: Id_1 >>> print user. c. id> 7 "user ". id >:id_1 >>> print user. c. id = None "user ". id is null >>> print user. c. id + address. c. id # convert two integers to + "user ". id + address. id >>> print user. c. name + address. c. email # Use two strings to convert | "user ". name | address. email

Operation connection
The connection here refers to the logical operator connection during condition query, that is, and or and not

>>> print and_(    user.c.name.like('j%'),    user.c.id == address.c.user_id,    or_(      address.c.email == 'wendy@aol.com',      address.c.email == 'jack@yahoo.com'    ),    not_(user.c.id>5))"user".name LIKE :name_1 AND "user".id = address.user_id AND (address.email = :email_1 OR address.email = :email_2) AND "user".id <= :id_1>>> 

The result is a compiled SQL statement segment. The following is a complete example.

>>> se_sql = [(user.c.fullname +", " + address.c.email).label('title')]>>> wh_sql = and_(       user.c.id == address.c.user_id,       user.c.name.between('m', 'z'),       or_(         address.c.email.like('%@aol.com'),         address.c.email.like('%@msn.com')       )     )>>> print wh_sql"user".id = address.user_id AND "user".name BETWEEN :name_1 AND :name_2 AND (address.email LIKE :email_1 OR address.email LIKE :email_2)>>> s = select(se_sql).where(wh_sql)>>> print sSELECT "user".fullname || :fullname_1 || address.email AS title FROM "user", address WHERE "user".id = address.user_id AND "user".name BETWEEN :name_1 AND :name_2 AND (address.email LIKE :email_1 OR address.email LIKE :email_2)>>> r = conn.execute(s)>>> r.fetchall()

Raw SQL

When encountering the SQL statement in charge, you can use the text function below sqlalchemy. SQL. Pack the SQL statement of the string and compile it into the SQL object required for execute. Example :,

>>> Text_ SQL = "SELECT id, name, fullname FROM user WHERE id =: id" # original SQL statement, parameter (: value) >>> s = text (text_ SQL) >>> print sSELECT id, name, fullname FROM user WHERE id =: id >>>> s <sqlalchemy. SQL. elements. textClause object at 0x0000000002587668 >>>> conn.exe cute (s, id = 3 ). fetchall () # id = 3 pass: id parameter [(3L, u 'jack', u 'Jack jone')]

Join
Join has two methods: join and outejoin. join has two parameters. The first is the join table, and the second is the on condition. After joing, you must use the select_from method:

>>> Print user. join (address) "user" JOIN address ON "user ". id = address. user_id # Because foreign keys are enabled, join can only recognize on conditions> print user. join (address, address. c. user_id = user. c. id) # manually specify the on condition "user" JOIN address ON address. user_id = "user ". id >>> s = select ([user. c. name, address. c. email]). select_from (user. join (address, user. c. id = address. c. user_id) # The SQL statement of jion must be used with the select_from method >>> s <sqlalchemy. SQL. selectable. select at 0x2eb63c8; Select object >>> print sSELECT "user ". name, address. email FROM "user" JOIN address ON "user ". id = address. user_id >>> conn.exe cute (s ). fetchall () [(u 'hello', u'jack @ yahoo.com '), (u 'hello', u'jack @ msn.com'), (u 'hello ', u'jack @ yahoo.com '), (u 'hello', u'jack @ msn.com'), (u 'jack', u'www @ www.org '), (u 'jack', u 'wendy @ aol.com '), (u 'jack', u 'www @ www.org'), (u 'jack ', u'wendy @ aol.com ')]

Sort group page
The order_by method is used for sorting, the group is group_by, And the paging is naturally the combination of the limit and offset methods.

>>> s = select([user.c.name]).order_by(user.c.name) # order_by>>> print sSELECT "user".name FROM "user" ORDER BY "user".name>>> s = select([user]).order_by(user.c.name.desc())>>> print sSELECT "user".id, "user".name, "user".fullname FROM "user" ORDER BY "user".name DESC>>> s = select([user]).group_by(user.c.name)    # group_by>>> print sSELECT "user".id, "user".name, "user".fullname FROM "user" GROUP BY "user".name>>> s = select([user]).order_by(user.c.name.desc()).limit(1).offset(3) # limit(1).offset(3)>>> print sSELECT "user".id, "user".name, "user".fullname FROM "user" ORDER BY "user".name DESC LIMIT :param_1 OFFSET :param_2[(4L, u'jack', u'jack Jone')]

Update
There are some queries in the front. The update and insert methods are very similar. They are all the methods in the table. The difference is that the update method has a where Method for filtering.

>>> S = user. update () >>> print sUPDATE "user" SET id =: id, name =: name, fullname =: fullname >>> s = user. update (). values (fullname = user. c. name) # values specifies the updated field> print sUPDATE "user" SET fullname = "user ". name >>> s = user. update (). where (user. c. name = 'jack '). values (name = 'ed') # where to select and filter >>> print s UPDATE "user" SET name =: name WHERE "user ". name =: name_1 >>> r = conn.exe cute (s) >>> print r. rowcount # Number of affected rows 3

Another advanced usage is to execute multiple record updates at a time using the bindparam method.

>>> S = user. update (). where (user. c. name = bindparam ('oldname ')). values (name = bindparam ('newname') # The oldname is bound to the slave book passed in below. The same is true for newname >>> print sUPDATE "user" SET name =: newname WHERE "user ". name =: oldname >>> u = [{'oldname': 'hello', 'newname': 'edd'}, {'oldname': 'ed ', 'newname': 'Mary '}, {'oldname': 'Tom', 'newname': 'jar'}] >>> r = conn.exe cute (s, u) >>> r. rowcount5L

Delete
It is easy to delete. You can call the delete method. If the where filter is not added, all data is deleted, but the table is not dropped. The data table is cleared.

>>> R = conn.exe cute (address. delete () # Clear the table >>> print r <sqlalchemy. engine. result. resultProxy object at 0x0000000002EAF550 >>> r. rowcount8L >>> r = conn.exe cute (users. delete (). where (users. c. name> 'M') # Delete record> r. rowcount3L

Flask-sqlalchemy
SQLAlchemy has become the orm standard in the python world. flask is a lightweight web framework that allows you to Use orm freely. flask-sqlalchemy is a plug-in specially designated for flask.

Install flask-sqlalchemy

pip install flask-sqlalchemy

Initialize sqlalchemy

From flask import Flaskfrom flask. ext. sqlalchemy import SQLAlchemyapp = Flask (_ name _) # dialect + driver: // username: password @ host: port/database? Charset = utf8 # configure sqlalchemy database DRIVER: // database username: password @ host address: Port/database? Code app. config ['sqlalchemy _ DATABASE_URI '] = 'mysql: // root: @ localhost: 3306/SQLALCHEMY? Charset = utf8' # initialize db = SQLAlchemy (app)

Define model

Class User (db. model): "defines three fields. The database table name is model name lower case" "id = db. column (db. integer, primary_key = True) username = db. column (db. string (80), unique = True) email = db. column (db. string (120), unique = True) def _ init _ (self, username, email): self. username = username self. email = email def _ repr _ (self): return '<User % r>' % self. username def save (self): db. session. add (self) db. session. commit ()

Create a data table
Sqlalchemy app is used to create data packets. If the table already exists, ignore it. If not, create a data packet.

>>> From yourapp import db, User >>> u = User (username = 'admin', email = 'admin @ example.com ') # create an instance >>> db. session. add (u) # add session >>> db. session. commit () # submit the query >>> users = User. query. all () # Query

Note that if you want to insert a Chinese character, you must insert a unicode string.

>>> U = User (username = u 'World', email = 'rsj @ example.com ') >>> u. save ()

Define Link
Relational databases, the most important thing is the relationship. Generally, the relationship is divided into one-to-one (for example, unlimited columns), one-to-many (articles and tags), and many-to-many (articles and tags)

One to multiple:
We define a Category and Post. The relationship between the two is one-to-many. One topic has many articles and one article belongs to one topic.

Class Category (db. model): id = db. column (db. integer, primary_key = True) name = db. column (db. string (50) def _ init _ (self, name): self. name = name def _ repr _ (self): return '<Category % r>' % self. nameclass Post (db. model): "defines five fields: id, title, body, pub_date, category_id" id = db. column (db. integer, primary_key = True) title = db. column (db. string (80) body = db. column (db. text) pub_date = db. column (db. string (20) # The Field category_id for the foreign key is db. column (db. integer, db. foreignKey ('category. id ') # The foreign key object does not generate the actual database field # backref indicates reverse reference, that is, the foreign key Category is queried through backref (post_set) Post category = db. relationship ('category ', backref = db. backref ('Post _ set', lazy = 'dynamic ') def _ init _ (self, title, body, category, pub_date = None): self. title = title self. body = body if pub_date is None: pub_date = time. time () self. pub_date = pub_date self. category = category def _ repr _ (self): return '<Post % r>' % self. title def save (self): db. session. add (self) db. session. commit ()

How to Use query?

>>> C = Category (name = 'python') >>> c <Category 'python' >>> c. post_set <sqlalchemy. orm. dynamic. appenderBaseQuery object at 0x0000000003B58F60 >>> c. post_set.all () [] >>> p = Post (title = 'Hello python', body = 'python is cool ', category = c) >>> p. save () >>> c. post_set <sqlalchemy. orm. dynamic. appenderBaseQuery object at 0x0000000003B73710 >>> c. post_set.all () # reverse Query [<Post u 'Hello python'>]> p <Post u 'Hello python' >>>> p. category <Category u 'python'> # You can also use the category_id field to add >>> p = Post (title = 'Hello flask ', body = 'flask is cool ', category_id = 1) >>> p. save ()

Comment to comment (the comment has already pointed out that such a practice cannot be deleted in association, and the short book does not have a strikethrough format. many examples are voided, so this prompt is not misleading)
For many-to-many relationships, it is often to define another table with two model IDs. For example, there are many-to-many relations between Post and Tag, and a Post_Tag table needs to be defined.

Post_tag = db. table ('Post _ tag', db. column ('Post _ id', db. integer, db. foreignKey ('Post. id '), db. column ('tag _ id', db. integer, db. foreignKey ('tag. id') class Post (db. model): id = db. column (db. integer, primary_key = True )#... omitted # define a reverse reference. The tag can be queried through post_set to the post Collection tags = db. relationship ('tag', secondary = post_tag, backref = db. backref ('Post _ set', lazy = 'dynamic ') class Tag (db. model): id = db. column (db. integer, primary_key = True) content = db. column (db. string (10), unique = True) # define reverse query posts = db. relationship ('post', secondary = post_tag, backref = db. backref ('tag _ set', lazy = 'dynamic ') def _ init _ (self, content): self. content = content def save (self): db. session. add (self) db. session. commit ()

Query:

>>> Tag_list = [] >>> tags = ['python', 'flask ', 'Ruby', 'rails '] >>> for tag in tags: t = Tag (tag) tag_list.append (t) >>> tag_list [<f_sqlalchemy.Tag object at tags>, <f_sqlalchemy.Tag object at 0x0000000003B7CF98>, <f_sqlalchemy.Tag object at tags>, <f_sqlalchemy.Tag object at 0x0000000003B7CE80>]> p <Post u 'Hello python' >>> p. tags []> p. tags = tag_list # add multiple-to-many data >>> p. save () >>> p. tags [<f_sqlalchemy.Tag object at least>, <f_sqlalchemy.Tag object at 0x0000000003B7CE80>]> p. tag_set # reverse query <sqlalchemy. orm. dynamic. appenderBaseQuery object at 0x0000000003B7C080 >>> p. tag_set.all () [<f_sqlalchemy.Tag object at tags>, <f_sqlalchemy.Tag object at tags>]> t = Tag. query. all () [1] >>> t <f_sqlalchemy.Tag object at 0x0000000003B7CF28 >>>> t. contentu 'python'> t. posts [<Post u 'Hello python'>]> t. post_set <sqlalchemy. orm. dynamic. appenderBaseQuery object at 0x0000000003B7C358 >>> t. post_set.all () [<Post u 'Hello python'>] self one to one

One-to-one is also a common requirement, such as unlimited classification columns.

Class Category (db. model): id = db. column (db. integer, primary_key = True) name = db. column (db. string (50) # parent id pid = db. column (db. integer, db. foreignKey ('category. id ') # parent topic object pcategory = db. relationship ('category ', uselist = False, remote_side = [id], backref = db. backref ('scategory ', uselist = False) def _ init _ (self, name, pcategory = None): self. name = name self. pcategory = pcategory def _ repr _ (self): return '<Category % r>' % self. name def save (self): db. session. add (self) db. session. commit ()

Query:

>>> P = Category ('python') >>> p <Category 'python' >>> p. pid >>> p. pcategory # query the parent topic> p. scategory # query subtopics >>> f = Category ('flask ', p) >>> f. save () >>> f <Category u 'flask '>>>> f. pid1L >>> f. pcategory # query the parent topic <Category u 'python' >>> f. scategory # query the parent topic> p. scategory # query subtopics <Category u 'flask '>

There are so many simple applications that flask-sqlalchemy defines models. More tips are how to query.