ORM Introduce

orm English full name object relational mapping, It's the object mapping program , In short, we are similar to python For this kind of object-oriented program, everything is an object , But the databases we use are all relational , To ensure consistent usage , adopt orm Mapping between the object model of programming language and the relational model of database , In this way, when we use the programming language to operate the database, we can directly use the object model of the programming language to operate , Instead of using it directly sql Language .

orm The advantages of :

  1. Hide data access details ,“ closed ” General database interaction of ,ORM At the heart of . It makes our general database interaction easy , And don't even think about the damned SQL sentence . Rapid development of , From this came .
  2. ORM It makes it easy for us to construct solid data structure .

shortcoming :

  1. Inevitable , Automation means mapping and association management , The price is to sacrifice performance ( In the early , It's all not like ORM What people have in common ). Now all kinds of ORM Frameworks are trying to use a variety of ways to mitigate this (LazyLoad,Cache), The effect is still remarkable .

2. sqlalchemy install

stay Python in , The most famous ORM The frame is SQLAlchemy. Users include openstack\Dropbox And other well-known companies or applications , List of major users http://www.sqlalchemy.org/organizations.html#openstack

Dialect For and data API To communicate , Call different databases according to different configuration files API, So as to realize the operation of database , Such as :

 
1
2
3
4
5
6
7
8
9
10
11
12
13
MySQL-Python
     mysql+mysqldb: // <user>:<password>@<host>[:<port>]/<dbname>
   
pymysql
     mysql+pymysql: // <username>:<password>@<host>/<dbname>[?<options>]
   
MySQL-Connector
     mysql+mysqlconnector: // <user>:<password>@<host>[:<port>]/<dbname>
   
cx_Oracle
     oracle+cx_oracle: //user :pass@host:port /dbname [?key=value&key=value...]
   
More on :http: //docs .sqlalchemy.org /en/latest/dialects/index .html

install sqlalchemy

1
pip  install  SQLAlchemy
pip  install  pymysql   # because mysqldb Still don't support py3, So here we use pymysql And sqlalchemy Interaction

Import SQLAlchemy, And initialization DBSession:

# Import :
from sqlalchemy import Column, String, create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base # Create the base class of the object :
Base = declarative_base() # Definition User object :
class User(Base):
# Name of table :
__tablename__ = 'user' # The structure of the table :
id = Column(String(20), primary_key=True)
name = Column(String(20)) # Initialize database connection :
engine = create_engine('mysql+mysqlconnector://root:password@localhost:3306/test')
# establish DBSession type :
DBSession = sessionmaker(bind=engine)

The above code is complete SQLAlchemy Initialization and specific to each table class Definition . If there are more than one watch , Just go ahead and define the others class, for example School:

class School(Base):
__tablename__ = 'school'
id = ...
name = ...

create_engine() Used to initialize a database connection .SQLAlchemy Use a string to represent connection information :

Database type + Database driver name :// user name : password @ Machine address : Port number / Database name '

You just need to replace the user name as needed 、 Password and other information .

below , Let's see how to add a row of records to a database table .


Because of ORM, Let's add a row of records to the database table , It can be thought of as adding a User object :


# establish session object :
session = DBSession()
# Create a new User object :
new_user = User(id='', name='Bob')
# Add to session:
session.add(new_user)
# Submit and save to database :
session.commit()
# close session:
session.close()

so , The key is to get session, Then add the object to session, Finally submit and close .DBSession Object as the current database connection .

How to query data from database tables ? With ORM, The query can no longer be tuple, It is User object .SQLAlchemy The query interface provided is as follows :


# establish Session:
session = DBSession()
# establish Query Inquire about ,filter yes where Conditions , Last call one() Back to the only line , If the all() Then return to all lines :
user = session.query(User).filter(User.id=='').one()
# Print type and object of name attribute :
print('type:', type(user))
print('name:', user.name)
# close Session:
session.close()

The operation results are as follows :


type: <class '__main__.User'>
name: Bob

so ,ORM It is to associate the rows of the database table with the corresponding objects , Interconversion .


Because many tables of relational database can also use foreign key to realize one to many 、 Many to many, etc , Accordingly ,ORM The framework can also provide one to many between two objects 、 Many to many functions .


In addition to the creation above , There is also a way to create tables , I don't know how to use it , But let's see


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
from  sqlalchemy  import  Table, MetaData, Column, Integer, String, ForeignKey
from  sqlalchemy.orm  import  mapper
 
metadata  =  MetaData()
 
user  =  Table( 'user' , metadata,
             Column( 'id' , Integer, primary_key = True ),
             Column( 'name' , String( 50 )),
             Column( 'fullname' , String( 50 )),
             Column( 'password' , String( 12 ))
         )
 
class  User( object ):
     def  __init__( self , name, fullname, password):
         self .name  =  name
         self .fullname  =  fullname
         self .password  =  password
 
mapper(User, user)  #the table metadata is created separately with the Table construct, then associated with the User class via the mapper() function

We have created the most basic table , So we start using orm Try creating a piece of data


1
2
3
4
5
6
7
8
9
10
11
Session_class  =  sessionmaker(bind = engine)  # Create a session with the database session class , Be careful , Here we go back to session The is a class, Not the instance
Session  =  Session_class()  # Generate session example
 
 
user_obj  =  User(name = "alex" ,password = "alex3714" # Generate the data object you want to create
print (user_obj.name,user_obj. id )   # The object has not been created yet , If you don't believe it, print it id Discover or None
 
Session.add(user_obj)  # Add the data object you want to create to this session in , I'll create it later
print (user_obj.name,user_obj. id # It's still not created yet
 
Session.commit()  # Now we have to submit , Create data

I wipe , Write so much code to create a piece of data , You said it was too tm It's really hard , About to turn and leave , I'll hold your hand , It's not the climax yet ..


Inquire about


1
2
my_user  =  Session.query(User).filter_by(name = "alex" ).first()
print (my_user)

The output you see at this point is like this


1
<__main__.User  object  at  0x105b4ba90 >

I wipe , What is it? ? This is the data you want , It's just sqlalchemy Help you map the returned data into an object , In this way, you can call each field just as you call object properties ,like this..


1
2
3
4
print (my_user. id ,my_user.name,my_user.password)
 
Output
1  alex alex3714

But just now the memory object address shown above, you can't distinguish what data is returned , Unless you print specific fields, take a look , If you want to make it readable , Just add this code under the class that defines the table


1
2
3
def  __repr__( self ):
     return  "<User(name='%s',  password='%s')>"  %  (
         self .name,  self .password)


modify


1
2
3
4
5
my_user  =  Session.query(User).filter_by(name = "alex" ).first()
 
my_user.name  =  "Alex Li"
 
Session.commit()

Roll back


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
my_user  =  Session.query(User).filter_by( id = 1 ).first()
my_user.name  =  "Jack"
 
 
fake_user  =  User(name = 'Rain' , password = '12345' )
Session.add(fake_user)
 
print (Session.query(User). filter (User.name.in_([ 'Jack' , 'rain' ])). all () )   # Now look session There's the data you just added and modified
 
Session.rollback()  # Now you rollback once
 
print (Session.query(User). filter (User.name.in_([ 'Jack' , 'rain' ])). all () )  # Check again and you will find that the data you just added is not available .
 
# Session
# Session.commit()


Get all the data


1
print (Session.query(User.name,User. id ). all () )


Multiconditional query


1
objs  =  Session.query(User). filter (User. id > 0 ). filter (User. id < 7 ). all ()

above 2 individual filter Is equivalent to user.id >1 AND user.id <7 The effect of


Statistics and grouping


1
Session.query(User). filter (User.name.like( "Ra%" )).count()

grouping


1
2
from  sqlalchemy  import  func
print (Session.query(func.count(User.name),User.name).group_by(User.name). all () )

Equivalent to native sql by


1
2
SELECT count(user.name) AS count_1, user.name AS user_name
FROM user GROUP BY user.name

Output is


[(1, 'Jack'), (2, 'Rain')]

Foreign key link

We create a addresses surface , Follow user Table correlation

1
2
3
4
5
6
7
8
9
10
11
12
13
from  sqlalchemy  import  ForeignKey
from  sqlalchemy.orm  import  relationship
 
class  Address(Base):
     __tablename__  =  'addresses'
     id  =  Column(Integer, primary_key = True )
     email_address  =  Column(String( 32 ), nullable = False )
     user_id  =  Column(Integer, ForeignKey( 'user.id' ))
 
     user  =  relationship( "User" , backref = "addresses" # This nb, Allow you to be in user From the outside to the inside backref The field reverses to find out all it's in addresses The associated items in the table
 
     def  __repr__( self ):
         return  "<Address(email_address='%s')>"  %  self .email_address

The relationship.back_populates parameter is a newer version of a very common SQLAlchemy feature calledrelationship.backref. The relationship.backref parameter hasn’t gone anywhere and will always remain available! The relationship.back_populates is the same thing, except a little more verbose and easier to manipulate. For an overview of the entire topic, see the section Linking Relationships with Backref.

After the table is created , We can reverse check like this

1
2
3
4
5
6
obj  =  Session.query(User).first()
for  in  obj.addresses:  # adopt user Object backcheck associated addresses Record
     print (i)
 
addr_obj  =  Session.query(Address).first()
print (addr_obj.user.name)   # stay addr_obj I'm going to check the connection directly user surface

Create associated objects

1
2
3
4
5
6
7
8
obj  =  Session.query(User). filter (User.name = = 'rain' ). all ()[ 0 ]
print (obj.addresses)
 
obj.addresses  =  [Address(email_address = "r1@126.com" ),  # Add associated object
                  Address(email_address = "r2@126.com" )]
 
 
Session.commit()

Common query syntax

Common Filter Operators

Here’s a rundown of some of the most common operators used in filter():

  • equals:

     query.filter(User.name == 'ed')
    
  • not equals:

     query.filter(User.name != 'ed')
    
  • LIKE:

    query.filter(User.name.like('%ed%'))

  • IN:

  • NOT IN:
    query.filter(~User.name.in_(['ed', 'wendy', 'jack']))

  • IS NULL:

  • IS NOT NULL:

  • AND:
    2.1. ObjectRelationalTutorial 17

query.filter(User.name.in_(['ed', 'wendy', 'jack']))
# works with query objects too:

query.filter(User.name.in_( session.query(User.name).filter(User.name.like('%ed%'))

))

query.filter(User.name == None)
# alternatively, if pep8/linters are a concern
query.filter(User.name.is_(None))
query.filter(User.name != None)
# alternatively, if pep8/linters are a concern
query.filter(User.name.isnot(None))

SQLAlchemy Documentation, Release 1.1.0b1

# use and_()

from sqlalchemy import and_
query.filter(and_(User.name == 'ed', User.fullname == 'Ed Jones'))

# or send multiple expressions to .filter()
query.filter(User.name == 'ed', User.fullname == 'Ed Jones')
# or chain multiple filter()/filter_by() calls
query.filter(User.name == 'ed').filter(User.fullname == 'Ed Jones')

Note: Makesureyouuseand_()andnotthePythonandoperator! • OR:

Note: Makesureyouuseor_()andnotthePythonoroperator! • MATCH:

query.filter(User.name.match('wendy'))
Note: match() uses a database-specific MATCH or CONTAINS f

4. Multiple foreign key associations

One of the most common situations to deal with is when there are more than one foreign key path between two tables.

Consider a Customer class that contains two foreign keys to an Address class:

In the following table ,Customer Table has 2 All the fields are associated Address surface

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
from  sqlalchemy  import  Integer, ForeignKey, String, Column
from  sqlalchemy.ext.declarative  import  declarative_base
from  sqlalchemy.orm  import  relationship
 
Base  =  declarative_base()
 
class  Customer(Base):
     __tablename__  =  'customer'
     id  =  Column(Integer, primary_key = True )
     name  =  Column(String)
 
     billing_address_id  =  Column(Integer, ForeignKey( "address.id" ))
     shipping_address_id  =  Column(Integer, ForeignKey( "address.id" ))
 
     billing_address  =  relationship( "Address"
     shipping_address  =  relationship( "Address" )
 
class  Address(Base):
     __tablename__  =  'address'
     id  =  Column(Integer, primary_key = True )
     street  =  Column(String)
     city  =  Column(String)
     state  =  Column(String)

There is no problem creating a table structure , But you Address The following error will be reported when inserting data into the table

1
2
3
4
5
6
sqlalchemy.exc.AmbiguousForeignKeysError: Could  not  determine join
condition between parent / child tables on relationship
Customer.billing_address  -  there are multiple foreign key
paths linking the tables.  Specify the  'foreign_keys'  argument,
providing a  list  of those columns which should be
counted as containing a foreign key reference to the parent table.

The solution is as follows

1
2
3
4
5
6
7
8
9
10
class  Customer(Base):
     __tablename__  =  'customer'
     id  =  Column(Integer, primary_key = True )
     name  =  Column(String)
 
     billing_address_id  =  Column(Integer, ForeignKey( "address.id" ))
     shipping_address_id  =  Column(Integer, ForeignKey( "address.id" ))
 
     billing_address  =  relationship( "Address" , foreign_keys = [billing_address_id])
     shipping_address  =  relationship( "Address" , foreign_keys = [shipping_address_id])

such sqlachemy Can distinguish which foreign key is corresponding to which field

use orm How to express ?

# A book can have more than one author , One author can publish more than one book
from sqlalchemy import Table, Column, Integer,String,DATE, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker Base = declarative_base() book_m2m_author = Table('book_m2m_author', Base.metadata,
Column('book_id',Integer,ForeignKey('books.id')),
Column('author_id',Integer,ForeignKey('authors.id')),
) class Book(Base):
__tablename__ = 'books'
id = Column(Integer,primary_key=True)
name = Column(String(64))
pub_date = Column(DATE)
authors = relationship('Author',secondary=book_m2m_author,backref='books') def __repr__(self):
return self.name class Author(Base):
__tablename__ = 'authors'
id = Column(Integer, primary_key=True)
name = Column(String(32)) def __repr__(self):
return self.name

orm Many to many

Next, create a few books and authors

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Session_class  =  sessionmaker(bind = engine)  # Create a session with the database session class , Be careful , Here we go back to session The is a class, Not the instance
=  Session_class()  # Generate session example
 
b1  =  Book(name = " Follow Alex learn Python" )
b2  =  Book(name = " Follow Alex Learn to be a girl " )
b3  =  Book(name = " Follow Alex Learn to dress up " )
b4  =  Book(name = " Follow Alex Learn to drive " )
 
a1  =  Author(name = "Alex" )
a2  =  Author(name = "Jack" )
a3  =  Author(name = "Rain" )
 
b1.authors  =  [a1,a2]
b2.authors  =  [a1,a2,a3]
 
s.add_all([b1,b2,b3,b4,a1,a2,a3])
 
s.commit()

here , Manually connect mysql, Look at this separately 3 A watch , You'll find that ,book_m2m_author Multiple records are automatically created to connect book and author surface

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
mysql> select  *  from  books;
+ - - - - + - - - - - - - - - - - - - - - - - - + - - - - - - - - - - +
id  | name             | pub_date |
+ - - - - + - - - - - - - - - - - - - - - - - - + - - - - - - - - - - +
|   1  | Follow Alex learn Python   | NULL     |
|   2  | Follow Alex Learn to be a girl      | NULL     |
|   3  | Follow Alex Learn to dress up      | NULL     |
|   4  | Follow Alex Learn to drive      | NULL     |
+ - - - - + - - - - - - - - - - - - - - - - - - + - - - - - - - - - - +
4  rows  in  set  ( 0.00  sec)
 
mysql> select  *  from  authors;
+ - - - - + - - - - - - +
id  | name |
+ - - - - + - - - - - - +
10  | Alex |
11  | Jack |
12  | Rain |
+ - - - - + - - - - - - +
3  rows  in  set  ( 0.00  sec)
 
mysql> select  *  from  book_m2m_author;
+ - - - - - - - - - + - - - - - - - - - - - +
| book_id | author_id |
+ - - - - - - - - - + - - - - - - - - - - - +
|        2  |         10  |
|        2  |         11  |
|        2  |         12  |
|        1  |         10  |
|        1  |         11  |
+ - - - - - - - - - + - - - - - - - - - - - +
5  rows  in  set  ( 0.00  sec)

here , Let's use it orm Check the data

1
2
3
4
5
6
7
8
9
print ( '-------- Look up the related author through the book list ---------' )
 
book_obj  =  s.query(Book).filter_by(name = " Follow Alex learn Python" ).first()
print (book_obj.name, book_obj.authors)
 
print ( '-------- Look up the related books through the author table ---------' )
author_obj  = s.query(Author).filter_by(name = "Alex" ).first()
print (author_obj.name , author_obj.books)
s.commit()

Output is as follows

1
2
3
4
- - - - - - - - Look up the related author through the book list - - - - - - - - -
Follow Alex learn Python [Alex, Jack]
- - - - - - - - Look up the related books through the author table - - - - - - - - -
Alex [ Follow Alex Learn to be a girl , Follow Alex learn Python]

The cow forced my brother !! Perfect and realize many to many

Many to many delete

Don't worry when deleting data boo_m2m_authors , sqlalchemy It will automatically delete the corresponding data for you

Delete the author through the book

1
2
3
4
5
6
author_obj  = s.query(Author).filter_by(name = "Jack" ).first()
 
book_obj  =  s.query(Book).filter_by(name = " Follow Alex Learn to be a girl " ).first()
 
book_obj.authors.remove(author_obj)  # Delete an author from a Book
s.commit()

Delete the author directly

When deleting authors , The association data between the author and all books will be automatically deleted

1
2
3
4
author_obj  = s.query(Author).filter_by(name = "Alex" ).first()
# print(author_obj.name , author_obj.books)
s.delete(author_obj)
s.commit()
 

python And SQLAlchemy More articles about

  1. be based on Python Of SQLAlchemy The operation of

    install stay Python Use SQLAlchemy The first premise of the project is to install the corresponding module , Certainly as python The advantages of , You can go to python Install under directory scripts Next , Press and hold at the same time shift+ With the left mouse button , So you can open it in the menu ...

  2. SQLAlchemy(1) -- Python Of SQLAlchemy and ORM

    Python Of SQLAlchemy and ORM(object-relational mapping: Object relation mapping ) web A common task in programming is to create an effective background database . before , Programmers write through sql sentence , ...

  3. python Use sqlalchemy Connect pymysql database

    python Use sqlalchemy Connect mysql database Number of words 833  read 461  Comment on 0  like 1 sqlalchemy yes python One of the more famous orm Program . What is? orm? orm English full name objec ...

  4. Python’s SQLAlchemy vs Other ORMs[ forward 7] Compare the results

    Comparison Between Python ORMs For each Python ORM presented in this article, we are going to list t ...

  5. Python’s SQLAlchemy vs Other ORMs[ forward 6]SQLAlchemy

    SQLAlchemy SQLAlchemy is an open source SQL toolkit and ORM for the Python programming language rele ...

  6. Python’s SQLAlchemy vs Other ORMs[ forward 3]Django&#39;s ORM

    Django's ORM Django is a free and open source web application framework whose ORM is built tightly i ...

  7. Python’s SQLAlchemy vs Other ORMs[ forward 2]Storm

    Storm Storm is a Python ORM that maps objects between one or more databases and Python. It allows de ...

  8. Python’s SQLAlchemy vs Other ORMs[ forward 0]

    Original address :http://pythoncentral.io/sqlalchemy-vs-orms/ Overview of Python ORMs As a wonderful language, Py ...

  9. Python’s SQLAlchemy vs Other ORMs[ forward 1]SQLObject

    SQLObject SQLObject is a Python ORM that maps objects between a SQL database and Python. It is becom ...

Random recommendation

  1. C# await and async

    Basic reading :http://www.cnblogs.com/jesse2013/p/async-and-await.html Q & a reading :http://www.cnblogs.com/heyuquan/ar ...

  2. MySQL keyword ( Reserved words ) list

    In the use of MySQL When , Generally try to avoid using keywords as table names , For example, use keywords as table names , It needs to be written in a standard way SQL Statement plus []( or “) Distinguish between field names and table names . The following is a list MySQL All keywords , Hope to use MySQL My friends offer some ...

  3. SQL Group and aggregate (Grouping and Aggregates)

    This chapter should be difficult , Also become SQL The only way for a master . Pay attention to it GROUP When the sentence is ,WHERE and HAVING The occasion of . The former is used for conditional filtering before retrieval . The latter is used for conditional filtering after retrieving the results . ========== ; ...

  4. Soft test ---- code 、ASII Code, etc

    Wash rice 2014 Intern written test , This year is Taomi's first year of recruiting summer interns , The written test is good, most of the tests are soft test topics ( Labor and capital regret that they didn't take the soft test ), Other styles are shallow and extensive ,C++,SQL sentence , data structure ( Huffman tree , Binary search tree , Stack suffix ...

  5. SQL Query optimization Index optimization

    sql Statements to optimize The poor performance of the system is partly because the load of the application really exceeds the actual processing capacity of the server , It's more because the system has a lot of SQL Statements need to be optimized . In order to achieve stable execution performance ,SQL The simpler the sentence, the better . For complex ...

  6. ( turn ) jmeter obtain cookie

      from https://blog.csdn.net/five3/article/details/53842283 jmeter It is a testing tool that will be used in the testing process , We can use it to measure the pressure , It can also be used. ...

  7. k8s Deploy rabbitmq A single node

    apiVersion: extensions/v1beta1 kind: Deployment metadata: annotations: fabric8.io/iconUrl: https://r ...

  8. datetime The string contains T

    json serialize datetime type , Back to the front end to show , String with T for example :var time = 2018-08-08T09:07:04.767  =>  time.substr(0, 16).r ...

  9. npm Package release record

    It's snowing , Idle at home , It's better to write a npm Package release . ordinary npm There are many tutorials on the Internet , I won't record it . Record here , A complex npm Package release , Complex refers to the complex construction environment . The whole project uses rollup To build , Its ...

  10. 20165324《Java Programming 》 The fourth week

    Student number 2016-2017-2 <Java Programming > Fourth week learning summary Summary of the learning content of the textbook The fifth chapter : Subclasses and inheritance Definition of subclass :class Subclass name extends Parent class name { ... } Subclass inheritance : ...