Python Operation SQL

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

First, download the installation

PIP3 Install Pymysql

Second, the operation of the use

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 about the default gets the data is the Ganso type, if you want or the dictionary type of data, namely:

#!/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 ()

SQLAlchemy

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:

PIP3 Install SQLAlchemy

SQLAlchemy itself cannot operate the database, it must have been pymsql and other third-party plug-ins, dialect used to communicate with the data API, depending on the configuration file to 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:123@127.0.0.1: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# cur = engine.execute (#     "Insert I NTO hosts (host, color_id) VALUES (%s,%s) ", [(' 1.1.1.22 ', 3), (' 1.1.1.221 ', 3),]#)    # execute sql# cur = engine.execute (#     "INSERT into hosts (host, color_id) VALUES (% (host) s,% (color_id) s)", #     host= ' 1.1.1.99 ', color_id=3#)  # execute sql# CU r = Engine.execute (' SELECT * from hosts ') # get the 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:123@127.0.0.1:3306/t1" , max_overflow=5) Base = Declarative_base () # Create single-table class Users (Base): __tablename__ = ' Users ' id = Column (Integer, PR imary_key=true) name = Column (string ()) Extra = column (string ()) __table_args__ = (UniqueConstraint (' id ')  , ' name ', name= ' uix_id_name '), Index (' Ix_id_name ', ' name ', ' Extra '), # One-to-many class Favor (Base): __tablename__ = ' favor ' nid = Column (Integer, primary_key=true) caption = column (String), default= ' Red ', Unique=true) class P Erson (Base): __tablename__ = ' person ' nid = Column (Integer, primary_key=true) name = Column (String), Index=tru E, 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=false) port = column (Integer, default=22) class Server (Base): __tablena me__ = ' server ' id = column (Integer, primary_key=true, autoincrement=true) hostname = column (String), Unique=tru E, Nullable=false) class Servertogroup (Base): __tablename__ = ' servertogroup ' nid = Column (Integer, Primary_key=tru E, autoincrement=true) server_id = Column (Integer, ForeignKey (' server.id ')) group_id = Column (Integer, ForeignKey (' G Roup.id ')) def init_db (): Base.metadata.create_all (Engine) def drop_db (): Base.metadata.drop_all (engine) Note: Set up another type of external check Way ForeignKeyConstraint ([' other_id '], [' othertable.other_id '])
 2, Action 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:123@127.0.0.1:3306/t1" , max_overflow=5) Base = Declarative_base () # Create single-table class Users (Base): __tablename__ = ' Users ' id = Column (Integer, Prim ary_key=true) name = Column (string ()) Extra = column (string ()) __table_args__ = (UniqueConstraint (' id ', '  Name ', name= ' uix_id_name '), Index (' Ix_id_name ', ' name ', ' Extra '), Def __repr__ (self): return "%s-%s" % (Self.id, self.name) # One-to-many class Favor (Base): __tablename__ = ' Favor ' nid = Column (Integer, primary_key=true) cap tion = Column (String (), default= ' Red ', unique=true) def __repr__ (self): return "%s-%s"% (Self.nid, Self.captio    n) class person (Base): __tablename__ = ' person 'Nid = Column (Integer, primary_key=true) name = Column (String (+), Index=true, nullable=true) favor_id = column (integ Er, 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 Servertogrou P (Base): __tablename__ = ' servertogroup ' nid = Column (Integer, Primary_key=true, autoincrement=true) server_id = Column (Integer, ForeignKey (' server.id ')) group_id = Column (Integer, ForeignKey (' group.id ')) group = relationship ("Gr OUP ", backref= ' s2g ') server = relationship (" Server ", backref= ' s2g ') class Group (Base): __tablename__ = ' group ' ID = Column (integer, primary_key=true) name = Column (String (unique=true), nullable=false) port = column (Integer, DE fault=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), Unique=true, NULLABLE=FALSE) def Init_DB (): Base.metadata.create_all (Engine) def drop_db (): Base.metadata.drop_all (engine) Session = Sessionmaker (bind=engi NE) session = session ()
obj = Users (name= "alex0", extra= ' SB ') session.add (obj) session.add_all ([    Users (name= "Alex1", extra= ' SB '),    Users (name= "Alex2", extra= ' SB '),]) Session.commit ()
Session.query (Users). Filter (Users.id > 2). Delete () Session.commit ()
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 ()
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_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 ')). All () from SQLAlchemy Import And_, Or_ret = Session.query (Users). Filter (And_ (Users.id > 3, Users.name = = ' Eric ')). All () ret = Session.query (U SERS). Filter (Or_ (Users.id < 2, Users.name = = ' Eric '). All () ret = Session.query (Users). Filter (Or_ (Users.id & Lt 2, and_ (users.name = = ' Eric ', Users.id > 3), Users.extra! = ")). All () # wildcard character ret = session.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 ( users.id), Func.sum (users.id), Func.min (Users.id)). Group_by (Users.name). All () ret = Session.query (Func.max (Users. ID), Func.sum (users.id), Func.min (Users.id)). Group_by (Users.name). have (Func.min (users.id) >2). All () # even table ret = SE Ssion.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 ()
 