Python SQL operations

Python-operated SQL pymsql is a module used to operate MySQL in Python. its usage is almost the same as that of MySQLdb.

1. download and install

Pip3 install pymysql

II. operation and use

1. execute SQL

#! /Usr/bin/env python #-*-coding: UTF-8-*-import pymysql # create connection conn = pymysql. connect (host = '2017. 0.0.1 ', port = 3306, user = 'root', passwd = '000000', db = 'T1') # Create a cursor = conn. cursor () # execute the SQL statement and return the number of rows affected by the receipt. export t_row = cursor.exe cute ("update hosts set host = '1. 1.1.2 '") # execute the SQL statement and return the affected number of rows # effect_row = cursor.exe cute (" update hosts set host = '1. 1.1.2 'Where nid> % s ", (1,) # run the SQL statement and return the number of affected rows # effect_row = cursor.exe cute.pdf (" insert into hosts (host, color_id) values (% s, % s) ", [(" 1.1.1.11 ", 1), (" 1.1.1.11 ", 2)]) # submit, otherwise, the new or modified data conn cannot be saved. commit () # Close the cursor. close () # close the connection conn. close ()

2. get the auto-increment ID of the newly created data

#! /Usr/bin/env python #-*-coding: UTF-8-*-import pymysql conn = pymysql. connect (host = '2017. 0.0.1 ', port = 3306, user = 'root', passwd = '000000', db = 'T1') cursor = conn.cursor()cursor.exe cute.pdf ("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 () # obtain the latest auto-increment IDnew_id = cursor. lastrowid

3. query data

#! /Usr/bin/env python #-*-coding: UTF-8-*-import pymysql conn = pymysql. connect (host = '2017. 0.0.1 ', port = 3306, user = 'root', passwd = '000000', db = 'T1') cursor = conn.cursor()cursor.exe cute ("select * from hosts ") # obtain the first row of data row_1 = cursor. fetchone () # obtain the first n rows of data # row_2 = cursor. fetchiterator (3) # obtain all data # row_3 = cursor. fetchall () conn. commit () cursor. close () conn. close ()

Note: When fetch data is performed in order, you can use cursor. scroll (num, mode) to move the cursor position, for example:

Cursor. scroll (1, mode = 'relative ') # Move relative to the current position

Cursor. scroll (2, mode = 'absolute ') # Move relative to absolute position

4. the fetch data type indicates that the data obtained by default is the ancestor type. if you want to use the data of the dictionary type, that is:

#! /Usr/bin/env python #-*-coding: UTF-8-*-import pymysql conn = pymysql. connect (host = '2017. 0.0.1 ', port = 3306, user = 'root', passwd = '000000', db = 'T1') # set the cursor to the dictionary type cursor = conn. cursor (cursor = pymysql. cursors. dictCursor) r = cursor.exe cute ("call p1 ()") result = cursor. fetchone () conn. commit () cursor. close () conn. close ()

SQLAlchemy

SQLAlchemy is an ORM framework in the Python programming language. it is built on database APIs and uses relational object ING for database operations. In short, it converts an object to SQL, then, use the data API to execute the SQL statement and obtain the execution result.

Installation:

Pip3 install SQLAlchemy

SQLAlchemy itself cannot operate databases. it must be a third-party plug-in such as pymsql. Dialect is used to communicate with data APIs. different database APIs are called based on different configuration files to perform database operations, for example:

MySQL-Python    mysql+mysqldb://
 
  :
  
   @
   
    [:
    
     ]/
     
         pymysql    mysql+pymysql://
      
       :
       
        @
        
         /
         
          [?
          
           ] MySQL-Connector mysql+mysqlconnector://
           
            :
            
             @
             
              [:
              
               ]/
               
                 cx_Oracle oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]
               
              
             
            
           
          
         
        
       
      
     
    
   
  
 

I. internal processing

Use Engine/ConnectionPooling/Dialect to perform database operations. The Engine uses ConnectionPooling to connect to the database, and then runs 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) # run SQL # cur = engine.exe cute (#" INSERT INTO hosts (host, color_id) VALUES ('1. 1.1.22 ', 3) "#) # ID of the newly inserted row # cur. lastrowid # run SQL # cur = engine.exe cute (# "INSERT INTO hosts (host, color_id) VALUES (% s, % s)", [('1. 1.1.22 ', 3), ('1. 1.1.221 ', 3),] #) # run SQL # cur = engine.exe cute (# "INSERT INTO hosts (host, color_id) VALUES (% (host) s, % (color_id) s) ", # host = '1. 1.1.99', color_id = 3 #) # run SQL # cur = engine.exe cute ('select * from hosts') # obtain the first row of data # cur. fetchone () # obtain row n # cur. fetchmany (3) # obtain all data # cur. fetchall ()

II. usage of ORM functions

Use ORM/Schema Type/SQL Expression Language/Engine/ConnectionPooling/Dialect to operate data. Create an object based on the class, convert the object to SQL, and execute SQL.

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 a single table class Users (Base): _ tablename _ = 'users' id = Column (Integer, primary_key = True) name = Column (String (32 )) extra = Column (String (16) _ 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 (50), default = 'red', unique = True) class Person (Base): _ tablename _ = 'person 'nid = Column (Integer, primary_key = True) name = Column (String (32), 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 (64), unique = True, nullable = False) port = Column (Integer, default = 22) class Server (Base ): _ tablename _ = 'server' id = Column (Integer, primary_key = True, autoincrement = True) hostname = Column (String (64), unique = True, nullable = False) class ServerToGroup (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') def init_db (): Base. metadata. create_all (engine) def drop_db (): Base. metadata. drop_all (engine) Note: another method for setting the external check 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: 123@127.0.0.1: 3306/t1", max_overflow = 5) Base = declarative_base () # Create a single table class Users (Base): _ tablename _ = 'users' id = Column (Integer, primary_key = True) name = Column (String (32 )) extra = Column (String (16) _ 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-multiple class Favor (Base): _ tablename _ = 'favor' nid = Column (Integer, primary_key = True) caption = Column (String (50 ), default = 'red', unique = True) def _ repr _ (self): return "% s-% s" % (self. nid, self. caption) class Person (Base): _ tablename _ = 'person 'nid = Column (Integer, primary_key = True) name = Column (String (32), index = True, nullable = True) favor_id = Column (Integer, ForeignKey ("favor. nid ") # It is irrelevant to the table structure generated. it is only used for query convenience. favor = relationship (" Favor ", backref = 'pers') # many-to-many class ServerToGroup (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 ("Group", backref = 's2g') server = relationship ("Server", backref = 's2g') class Group (Base ): _ tablename _ = 'group' id = Column (Integer, primary_key = True) name = Column (String (64), unique = True, nullable = False) port = Column (Integer, 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 (64), unique = True, nullable = False) def init_db (): Base. metadata. create_all (engine) def drop_db (): Base. metadata. drop_all (engine) Session = sessionmaker (bind = engine) 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 (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 ret = session. query (Users ). filter (Users. name. like ('e % ')). all () ret = session. query (Users ). filter (~ Users. name. like ('e % ')). all () # restrict ret = session. query (Users) [] # 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 ). having (func. min (Users. id)> 2 ). all () # join table ret = session. 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 () # combined 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 ()