MySQL Basics (v) of Pymysql

Pymysql module:
      PIP3 Install Pymysql-i Https://pypi.douban.com/simple
      Python module: Manipulating the database (SQL statements)

      1. Python Enables user login
      2. mysql Save data
                -Connect, close (cursor)
                -Execute ()   --SQL injection
                -Modify: Conn.commit ()     can return the number of rows affected
                -Fetchone Fetchall
                -Get Insert data self-increment ID
                -scroll Move Cursor Position

1 #实现增删改2 3 Import Pymysql4conn = pymysql.connect (host= ' localhost ', user= ' root ', password= ' 123 ', database= ' db1 ')5Cursor=conn.cursor ()6 7Sql= "INSERT into Userinfo4 (Username,password) VALUES (' root ', ' 123123 ')"8R =cursor.execute (SQL) # returns the number of rows affected9 Tensql = "INSERT into UserInfo (Username,password) VALUES (%s,%s)" OneR = Cursor.executemany (sql,[(' Egon ', ' SB '), (' Laoyao ', ' BS '))]) A  - conn.commit () # Additions and deletions must be submitted - print (R) the cursor.close () - conn.close () -  -  + # Check - Import Pymysql +conn = Pymysql.connect (host= "localhost", user= ' root ', password= ' 123 ', database= "DB1") Acursor = Conn.cursor (cursor=pymysql.cursors.DictCursor) atsql = "SELECT * FROM Userinfo4" - cursor.execute (SQL) -  -Cursor.scroll (1,mode= ' relative ') # Move relative to the current position -Cursor.scroll (2,mode= ' absolute ') # relative absolute position movement -result =cursor.fetchone () #获取一行 in print (Result) -  toresult =Cursor.fetchall () #获取全部 + print (Result) -  theresult = Cursor.fetchmany (2#获取指定行数 are not normally used, and the number of rows in the SQL statement is limited * print (Result) $ cursor.close ()Panax Notoginseng conn.close () -  the  + #获取新插入数据的自增id Cursor.lastrowid A # when inserting a row of data for a table and not knowing how much its self-increment ID is, you can use Lastrowid to view the ID when inserting an associated table (foreign key) to him the Import Pymysql +  -conn = Pymysql.connect (host= "localhost", user= ' root ', password= ' 123 ', database= "DB1") $cursor =conn.cursor () $sql = "INSERT into Userinfo4 (Username,password) VALUES (' Asdfasdf ', ' 123123 ')" - cursor.execute (SQL) - Conn.commit () the print (CURSOR.LASTROWID) - cursor.close ()WuyiConn.close ()

SQL injection:

1 Import Pymysql2Name= input (' User name ')3Passwd=input (' Password ')4Conn =pymysql.connect (host= ' localhost ', user= ' root ', password= ' 123 ', database= ' db1 ', charset= ' UTF8 ')5Cursor=conn.cursor ()6 7Cursor=conn.cursor (cursor=pymysql.cursors.DictCursor) #设置返回的数据的格式为字典8 9 TenSql= ' select * from Userinfo4 where username= '%s ' and password= '%s '%(NAME,PASSWD) One  A#sql = ' SELECT * from Userinfo4 where username= "Alex"--"and password="%s "'%(NAME,PASSWD) -  - cursor.execute (SQL) theresult =Cursor.fetchone () -  - cursor.close () - conn.close () +  - ifResult: +Print (' yes! ') A Else: atPrint (' No ')

View Code

Navicat (client)
    Create a table
    New query
    Dump SQL file

Data backup:
     Backing up data and table structures
     Mysqldump-u root db1 > db1.sql-p            under current directory
     Backup table Structure
     Mysqldump-u root-d db1 > Db1.sql-p
Import Database:
     Create databases DB5;
     Mysql-u root-d DB5 < db1.sql-p

MySQL Basics (v) of Pymysql