Python Operation Sqlachemy

I really do not understand it, see this: http://www.cnblogs.com/jixuege-1/p/6272888.html

There are two main ways to use MySQL for Python operations:

• Native Module Pymsql
• ORM Framework Sqlachemy

Pymysql

Pymsql is a module that operates MySQL in Python and is used almost the same way as MySQLdb.

Download Installation

PIP3 Install Pymysql

Use action

1. Execute SQL

#!/usr/bin/env python#-*-coding:utf-8-*-import pymysql  # Create Connection conn = Pymysql.connect (host= ' 127.0.0.1 ', port=3306, User= ' root ', passwd= ' 123 ', db= ' t1 ') # Create cursor cursor = Conn.cursor ()  # Execute SQL and return the number of affected rows Effect_row = Cursor.execute (" Update hosts Set host = ' 1.1.1.2 ')  # executes SQL and returns the number of affected rows #effect_row = Cursor.execute ("update hosts set host = ' 1.1.1.2 ' wh Ere nid >%s ", (1,))  # Executes SQL and returns the number of rows affected #effect_row = Cursor.executemany (" INSERT into hosts (HOST,COLOR_ID) values (%s ,%s) ", [(" 1.1.1.11 ", 1), (" 1.1.1.11 ", 2)])    # Commit, otherwise unable to save new or modified Data conn.commit ()  # Close Cursor cursor.close () # Close Connection Conn.close ()

2. Get the newly created data self-increment ID

#!/usr/bin/env python#-*-coding:utf-8-*-import pymysql  conn = pymysql.connect (host= ' 127.0.0.1 ', port=3306, user= ' Root ', passwd= ' 123 ', db= ' t1 ') cursor = Conn.cursor () cursor.executemany ("INSERT into hosts (host,color_id) VALUES (%s,%s ) ", [(" 1.1.1.11 ", 1), (" 1.1.1.11 ", 2)]) Conn.commit () Cursor.close () Conn.close ()  # Get latest self-increment idnew_id = Cursor.lastrowid

3. Get Query data

#!/usr/bin/env python#-*-coding:utf-8-*-import pymysql  conn = pymysql.connect (host= ' 127.0.0.1 ', port=3306, user= ' Root ', passwd= ' 123 ', db= ' t1 ') cursor = Conn.cursor () cursor.execute ("SELECT * from hosts")  # Gets the first row of data Row_1 = Cursor.fetchone ()  # Gets the first n rows of data # row_2 = Cursor.fetchmany (3) # Get all data # Row_3 = Cursor.fetchall ()  conn.commit () Cursor.close () Conn.close ()

Note: In order to fetch data, you can use Cursor.scroll (Num,mode) to move the cursor position, such as:

• Cursor.scroll (1,mode= ' relative ') # moves relative to the current position
• Cursor.scroll (2,mode= ' absolute ') # relative absolute position movement

4. Fetch data type

The data that is obtained by default is the Ganso type, if desired or the dictionary type of data, i.e.:

#!/usr/bin/env python#-*-coding:utf-8-*-import pymysql  conn = pymysql.connect (host= ' 127.0.0.1 ', port=3306, user= ' Root ', passwd= ' 123 ', db= ' t1 ')  # Cursor set to dictionary type cursor = conn.cursor (cursor=pymysql.cursors.dictcursor) R = Cursor.execute ("Call P1 ()")  result = Cursor.fetchone ()  conn.commit () cursor.close () Conn.close ()

Develop your own web framework

• Socket
• HTTP protocol
• HTML knowledge
• Database (Pymysql,sqlalchemy)

ORM Framework

Function: Provides simple rules that are automatically converted into SQL statements

ORM into DB first and code first
DB First: Manually create a database and table-->orm framework--Automatically generated classes
Code First: Manually create a class manually create a database-->orm frame--auto generate table, Sqlachemy belongs to code first

Sqlachemy

SQLAlchemy is an ORM framework in the Python programming language that builds on database APIs and uses relational object mappings for database operations, in short: converting objects to SQL and then executing SQL using the data API and getting execution results.

Installation:

1	pip3 install SQLAlchemy

　　　　　　　　

SQLAlchemy itself cannot operate the database, it must take advantage of third-party plug-ins such as Pymysql, dialect used to communicate with the data API, depending on the configuration file call different database APIs, so as to achieve the operation of the database, such as:

Mysql-python    mysql+mysqldb://<user>:<password>@　　

first, internal processing

Using Engine/connectionpooling/dialect for database operations, the Engine uses connectionpooling to connect to the database and then executes the SQL statement through dialect.

#!/usr/bin/env python#-*-coding:utf-8-*-from sqlalchemy import create_engine    engine = Create_engine ("mysql+ Pymysql://root:[email protected]:3306/t1 ", max_overflow=5)  # execute sql# cur = engine.execute (#     " INSERT into hosts ( Host, color_id) VALUES (' 1.1.1.22 ', 3)  #) # New Insert Row auto-increment id# cur.lastrowid  # Execute SQL engine encapsulates pymysql cursor# = engine . Execute (#     "INSERT into hosts (host, color_id) VALUES (%s,%s)", [(' 1.1.1.22 ', 3), (' 1.1.1.221 ', 3),]#)    # execute sql# CU R = Engine.execute (#     "INSERT into hosts (host, color_id) VALUES (% (host) s,% (color_id) s)", #     host= ' 1.1.1.99 ', color_id=3#)  # execute sql# cur = engine.execute (' SELECT * from hosts ') # get first row of data # Cur.fetchone () # get nth row Data # Cur.fetchmany (3) # Get all Data # Cur.fetchall ()

Second, the use of ORM function

Use Orm/schema type/sql Expression language/engine/connectionpooling/dialect All components to manipulate the data. Objects are created from the class, objects are converted to SQL, and SQL is executed.

1. Create a table

#!/usr/bin/env python#-*-coding:utf-8-*-from sqlalchemy.ext.declarative import Declarative_basefrom SQLAlchemy Import Column, Integer, String, ForeignKey, UniqueConstraint, indexfrom sqlalchemy.orm import Sessionmaker, Relationshipfrom sqlalchemy import Create_engine engine = Create_engine ("mysql+pymysql://root:[email protected") : 3306/t1 ", max_overflow=5) Base = Declarative_base () # Create single-table class Users (base): __tablename__ = ' Users ' #真正的表名 id = Co Lumn (Integer, Primary_key=true) #在内部会把id, Name,extra copy to __init__ name = Column (String) #string代表char和varchar ext RA = Column (String ()) __table_args__ = (UniqueConstraint (' id ', ' name ', name= ' uix_id_name '), Index (' I X_id_name ', ' name ', ' Extra '), # One-to-many class Favor (Base): __tablename__ = ' Favor ' nid = Column (Integer, Primary_ke Y=true) caption = Column (String (), default= ' Red ', Unique=true) class person (Base): __tablename__ = ' person ' ni D = Column (Integer, primary_key=true) name = ColuMn (String (+), Index=true, nullable=true) favor_id = Column (Integer, ForeignKey ("Favor.nid")) # Many-to-many class Group (Base): __tablename__ = ' group ' id = column (Integer, primary_key=true) name = Column (String), Unique=true, NULLABLE=FA LSE) port = column (integer, default=22) class Server (Base): __tablename__ = ' Server ' id = column (integer, prima     Ry_key=true, autoincrement=true) hostname = Column (String (), Unique=true, Nullable=false) class Servertogroup (Base): __tablename__ = ' servertogroup ' nid = Column (Integer, Primary_key=true, autoincrement=true) server_id = column (I Nteger, ForeignKey (' server.id ')) group_id = Column (Integer, ForeignKey (' Group.id ')) def init_db (): Base.metadata.cr Eate_all (Engine) def drop_db (): Base.metadata.drop_all (Engine)

Note: Another way to set the foreign key is ForeignKeyConstraint ([' other_id '], [' othertable.other_id '])

2. Operation table

#!/usr/bin/env python#-*-coding:utf-8-*-from sqlalchemy.ext.declarative import Declarative_basefrom SQLAlchemy Import Column, Integer, String, ForeignKey, UniqueConstraint, indexfrom sqlalchemy.orm import Sessionmaker, Relationshipfrom sqlalchemy Import create_engineengine = Create_engine ("mysql+pymysql://root:[email protected") : 3306/t1 ", max_overflow=5) Base = Declarative_base () # Create single-table class Users (base): __tablename__ = ' Users ' id = Column (Inte GER, primary_key=true) name = Column (string ()) Extra = column (string ()) __table_args__ = (uniqueconstrain T (' id ', ' name ', name= ' uix_id_name '), Index (' Ix_id_name ', ' name ', ' Extra '), Def __repr__ (self): retur N "%s-%s"% (Self.id, self.name) # One-to-many class Favor (Base): __tablename__ = ' Favor ' nid = Column (Integer, Primary_key=tru E) caption = Column (String (), default= ' Red ', unique=true) def __repr__ (self): return "%s-%s"% (Self.nid, SE Lf.caption) class Person (Base): __tablename__ = ' peRson ' nid = Column (Integer, primary_key=true) name = Column (String (+), Index=true, nullable=true) favor_id = Col Umn (Integer, ForeignKey ("Favor.nid")) # is independent of the build table structure and is only used for query convenience favor = relationship ("Favor", backref= ' pers ') # Many-to-many class Ser Vertogroup (Base): __tablename__ = ' servertogroup ' nid = Column (Integer, Primary_key=true, Autoincrement=true) ser ver_id = Column (Integer, ForeignKey (' server.id ')) group_id = Column (Integer, ForeignKey (' group.id ')) group = Relatio Nship ("Group", backref= ' s2g ') server = relationship ("Server", backref= ' s2g ') class Group (Base): __tablename__ = ' Grou P ' id = column (Integer, primary_key=true) name = Column (String (unique=true), nullable=false) port = column (in Teger, default=22) # group = relationship (' group ', secondary=servertogroup,backref= ' host_list ') class Server (Base): __ tablename__ = ' server ' id = column (Integer, primary_key=true, autoincrement=true) hostname = column (String), Uniq Ue=true, Nullable=false)Def init_db (): Base.metadata.create_all (Engine) def drop_db (): Base.metadata.drop_all (engine) Session = Sessionmaker (b Ind=engine) session = Session ()

Increase

obj = Users (name= "alex0", extra= ' SB ') session.add (obj) session.add_all ([    Users (name= "Alex1", extra= ' SB '),    Users (name= "Alex2", extra= ' SB '),]) Session.commit ()

By deleting

Session.query (Users). Filter (Users.id > 2). Delete () Session.commit ()

Change

Session.query (Users). Filter (Users.id > 2). Update ({"name": "099"}) session.query (users). Filter (Users.id > 2). Update ({Users.name:Users.name + "099"}, Synchronize_session=false) Session.query (Users). Filter (Users.id > 2). Update ({"num": Users.num + 1}, synchronize_session= "Evaluate") Session.commit ()

Check

ret = session.query (users). All () ret = Session.query (Users.name, Users.extra). All () ret = session.query (users). Filter_ by (Name= ' Alex '). All () ret = session.query (users). filter_by (name= ' Alex '). First () ret = session.query (users). Filter ( Text ("Id<:value and Name=:name")). Params (value=224, name= ' Fred '). Order_by (User.ID). All () ret = Session.query ( Users). From_statement (Text ("SELECT * from Users where Name=:name")). Params (name= ' Ed '). All ()

Other

# conditional ret = session.query (users). filter_by (name= ' Alex '). All () ret = session.query (users). Filter (Users.id > 1, Users.name = = ' Eric '). All () ret = Session.query (Users). Filter (Users.id.between (1, 3), Users.name = = "Eric"). All () ret = Session.query (Users). Filter (Users.id.in_ ([1,3,4]). All () ret = session.query (users). Filter (~users.id.in_ ([1,3,4]) ). All () ret = Session.query (Users). Filter (Users.id.in_ (Session.query (users.id). Filter_by (Name= ' Eric ')). SQLAlchemy import and_, Or_ret = Session.query (Users). Filter (And_ (Users.id > 3, Users.name = = ' Eric ')). All () ret = Sessi        On.query (Users). Filter (Or_ (Users.id < 2, Users.name = = ' Eric '). All () ret = session.query (users). Filter (Or_ ( Users.id < 2, and_ (users.name = = ' Eric ', Users.id > 3), Users.extra! = "")). All () # wildcard character ret = Sessio N.query (Users). Filter (Users.name.like (' e% ')). All () ret = session.query (users). Filter (~users.name.like (' e% ')). All ( # LIMIT ret = session.query (users) [1:2]# sort ret = session.query (users). orDer_by (Users.name.desc ()). All () ret = Session.query (Users). Order_by (Users.name.desc (), USERS.ID.ASC ()). All () # Group from sqlalchemy.sql Import Funcret = Session.query (Users). group_by (Users.extra). All () ret = Session.query (Func.max (U sers.id), Func.sum (users.id), Func.min (Users.id)). Group_by (Users.name). All () ret = Session.query (Func.max (Users.i D), Func.sum (users.id), Func.min (Users.id)). Group_by (Users.name). have (Func.min (users.id) >2). All () # even table ret = ses Sion.query (Users, Favor). Filter (Users.id = = Favor.nid). All () ret = session.query (person). Join (Favor). All () ret = Session.query (person). Join (Favor, isouter=true). All () # Combo Q1 = session.query (users.name). Filter (Users.id > 2) q2 = Session.query (favor.caption). Filter (Favor.nid < 2) ret = q1.union (Q2). All () q1 = session.query (users.name). Filter ( Users.id > 2) q2 = Session.query (favor.caption). Filter (Favor.nid < 2) ret = Q1.union_all (Q2). All ()

　　

Python Operation Sqlachemy