The path of Python 51-sqlalchemy

ORM Introduction

ORM English Full Name Object relational mapping, that is, objects mapping relation program, simply say we like python this object-oriented program is all objects, but we use the database is relational, in order to ensure consistent use of the habit, By ORM, the object model of the programming language and the relational model of the database are mapped, so that we can use the programming language to manipulate the database directly using the object model of the programming language, rather than using the SQL language directly.

Advantages of ORM:

Hidden data access details, "closed" Universal database interaction, ORM Core. He makes our universal database interaction simple and easy, without having to think about the damned SQL statements at all. Rapid development, resulting from this.

ORM makes it easy to construct a solidified data structure.

Disadvantages:

Inevitably, automation means mapping and association management at the expense of performance (early, this is what all dislike orm people have in common). Now the various ORM frameworks are trying to use various methods to alleviate this block (Lazyload,cache), the effect is still very significant.

SQLAlchemy Installation

Pip Install SQLAlchemy

Pip Install Pymysql

SQLAlchemy Basic Use

Create a table first

CREATE TABLE User (id INTEGER not NULL auto_increment, name varchar (+), password varchar), PRIMARY KEY (i D))

Use SQLAlchemy to achieve the same functionality

Create User table

Import sqlalchemyfrom sqlalchemy import create_enginefrom sqlalchemy.ext.declarative  import declarative_basefrom sqlalchemy import Column, Integer,  Stringengine = create_engine ("Mysql+pymysql://root:@127.0.0.1/learn_python",                         encoding= "Utf-8",                        echo=true) base = declarative_base ()     #  Generate ORM Base class Class user (Base):    __tablename__ =  "User"    #  show     id = column (integer, primary_key=true)      name = column (String (+))     password = column (String (64)) Base.metadata.create_all (engine)     #  Create a table structure 

The user table has been created, so let's start with an ORM and try to create a piece of data.

Import sqlalchemyfrom sqlalchemy import create_enginefrom sqlalchemy.ext.declarative  import declarative_basefrom sqlalchemy import column, integer, stringfrom  sqlalchemy.orm import sessionmaker#  Create user Table Engine = create_engine ("mysql+ Pymysql://root:@127.0.0.1/learn_python ",                        encoding= "Utf-8") Base =  Declarative_base ()    #  generate ORM base class Class user (base):     __tablename__  =  "user"   #  table name     id = column (integer, primary_ Key=true)     name = column (String (+))     password =  column (String) Base.metadata.create_all (engine)     #  CREATE TABLE structure Session_class  = sessionmaker (bind=engine   #  create a session with the database Session class, note that this is a class that is returned to the session, not an instance Session = session_ Class ()                     #  Generate session Instance User_obj = user (name= "Rain",  password= "1111111")       #  generate the data object you want to create # print (User_obj.name, user_obj.password)               #  you haven't created an object yet, don't believe it. Print ID discovery or nonesession.add (user _obj)                                 #  Add the data object you want to create to this session, create a unified # print (user_obj.name, user_obj.id)                     #  still hasn't created Session.commit ()                                      #  now only unified commit, create data

Inquire

Import sqlalchemyfrom sqlalchemy import create_enginefrom sqlalchemy.ext.declarative  import declarative_basefrom sqlalchemy import column, integer, stringfrom  sqlalchemy.orm import sessionmakerengine = create_engine ("mysql+pymysql://root:@ 127.0.0.1/learn_python ",                        encoding= "Utf-8")                         base  = declarative_base () Class user (base):    __tablename__ =  "User"   #  show     id = column (integer, primary_key=true)      name = column (String (+))     password = column (String (64))     deF __repr__ (self):        return  "<User (name= '%s ',  Password= '%s ') > " %  (Self.name, self.password) data = session.query (User). Filter ( user.name== "Tom"). All ()     #  query the user table for name Tom, all information Data = session.query ( User). filter_by (name= "Tom"). All ()        #  query the user table for name Tom, all information data  = session.query (User.Name). Filter (USER.ID&NBSP;&GT;&NBSP;3). All ()     #  Query the user table with IDs greater than 3, namedata = session.query (user). Filter (user.id > 0). Filter (user.id  &LT;&NBSP;3). All ()     #  multi-Criteria Query Data = session.query (User). Filter (User.ID  > 0). Filter (user.name ==  "Tom"). All () print (data)

modifying data

Import sqlalchemyfrom sqlalchemy import create_enginefrom sqlalchemy.ext.declarative  import declarative_basefrom sqlalchemy import column, integer, stringfrom  sqlalchemy.orm import sessionmaker# #  Create user table Engine = create_engine ("MySQL +pymysql://root:@127.0.0.1/learn_python ",                        encoding= "Utf-8") Base =  Declarative_base ()    #  generate ORM base class Class user (base):     __tablename__  =  "User"   #  indicates     id = column (integer, primary_ Key=true)     name = column (String (+))     password =  column (String)     def __repr__ (self):         return  "&LT User (name= '%s ',  password= '%s ') > " %  (Self.name, self.password) session_class =  Sessionmaker (bind=engine)    #  Create a session with the database Session class, note that this is a class that is returned to the session, Not an instance Session = session_class ()                     #  Generate Session Instance data = session.query (User). Filter_ by (name= "Tom"). First () data.name =  "Ben" Session.commit ()

Delete data

Import sqlalchemyfrom sqlalchemy import create_enginefrom sqlalchemy.ext.declarative  import declarative_basefrom sqlalchemy import column, integer, stringfrom  sqlalchemy.orm import sessionmaker#  Create user Table Engine = create_engine ("mysql+ Pymysql://root:@127.0.0.1/learn_python ",                        encoding= "Utf-8") Base =  Declarative_base ()    #  generate ORM base class Class user (base):     __tablename__  =  "User"   #  indicates     id = column (integer, primary_ Key=true)     name = column (String (+))     password =  column (String)     def __repr__ (self):         return  "<user (Name= '%s ',  password= '%s ') > '  %  (self.name, self.password) session_class =  Sessionmaker (bind=engine)    #  Create a session with the database Session class, note that this is a class that is returned to the session, Not an instance Session = session_class ()                     #  Generate Session Instance data = session.query (User). Filter_ by (name= "Ben"). First () Session.delete (data) Session.commit ()

Statistics

Import sqlalchemyfrom sqlalchemy import create_enginefrom sqlalchemy.ext.declarative  import declarative_basefrom sqlalchemy import column, integer, stringfrom  sqlalchemy.orm import sessionmaker#  Create user Table Engine = create_engine ("mysql+ Pymysql://root:@127.0.0.1/learn_python ",                        encoding= "Utf-8") Base =  Declarative_base ()    #  generate ORM base class Class user (base):     __tablename__  =  "User"   #  indicates     id = column (integer, primary_ Key=true)     name = column (String (+))     password =  column (String)     def __repr__ (self):         return  "<user (Name= '%s ',  password= '%s ') > '  %  (self.name, self.password) session_class =  Sessionmaker (bind=engine)    #  Create a session with the database Session class, note that this is a class that is returned to the session, Not an instance Session = session_class ()                     #  Generate Session Instance data = session.query (User). Filter ( User.name.like ("ra%")). Count () print (data)

Group

Import sqlalchemyfrom sqlalchemy import create_enginefrom sqlalchemy.ext.declarative  import declarative_basefrom sqlalchemy import column, integer, stringfrom  sqlalchemy.orm import sessionmakerfrom sqlalchemy import func#  Create User table Engine = create_engine ("Mysql+pymysql://root:@127.0.0.1/learn_python",                         encoding= "Utf-8") base = declarative_base ()    #  generate ORM base class class  User (Base):    __tablename__ =  "user"   #  show      id = column (integer, primary_key=true)     name = column ( String (+))     password = column (string)     def __ Repr__ (self):         return  "<user (name= '%s ',  password= '%s ') >"  %  (self.name,  Self.password) Session_class = sessionmaker (bind=engine)    #  create session with Database  class, note that this is a class that is returned to the Session, not an instance session = session_class ()                     #  Generate session Instance data  = session.query (Func.count (user.name),  user.name). group_by (User.Name). All () print (data)

This article is from the "Eight Miles" blog, so be sure to keep this source http://5921271.blog.51cto.com/5911271/1913920

The path of Python 51-sqlalchemy