Python---Fundamentals review (eight) Database Basics operations (SQLite and MySQL)

One: SQLite operation

SQLite is an embedded database, and its database is a file. Since SQLite itself is written in C and small in size, it is often integrated into a variety of applications, even in iOS and Android apps.

Python has built-in SQLite3, so using SQLite in Python does not require anything to be installed and used directly.

Operation steps: 1. Create a connection (remember to import the module)

import sqlite3   = Sqlite3.connect ("test.db") #若是文件不存在会自动创建, the path can be relative, or it can be absolute. This file is equivalent to a database

2. Get the cursor based on the connection

Curs = Conn.cursor ()

3. Operation data table according to the cursor

Curs.execute ("" "create TABLE info (                    uid INTEGER PRIMARY KEY autoincrement, #注意设置主键, must be Integer type                    username varchar (+),                    password varchar (+)                )             "" ")curs.execute (" ""                CREATE INDEX  un on info ( username) #创建索引 "" ")
Related Basics

4. Make and delete data and change

Increase
Curs.execute ("""INSERT into info (username,password) VALUES ("LD","123456");""")Delete Curs.execute ("""DELETE from info WHERE uid >3;""")Change Curs.execute ("""UPDATE Info SET username ="Dsad"WHERE uid =2;""")

= Curs.execute ("" "From                info; """ ) for in Curs.fetchall ():    print (row[0],row[1],row[ 2])

5. If we have modified the database, the original data (or data sheet), we need to submit our operation

Conn.commit ()

6. Operation complete, disconnect cursor and database connection

Curs.close () Conn.close ()

Two: MySQL operation

MySQL operation requires us to import the Pymysql module (not built-in).

PIP3 Install Pymysql

(1) Its operation steps and above SQLite almost consistent

= Pymysql.connect (host="localhost", user="root", password= " Root ", database="T1"= conn.cursor () #设置游标, the default is to return the tuple information after executing the query

#cur = Conn.cursor (cursor=pymysql.cursors.dictcursor) #设置游标返回字典类型信息

" SELECT * from Users "  = cur.fetchall () #返回元组信息print (ret) # ((1, ' Xiaoyu ', ' Xiaoudaf ', ' 123 '), (2, ' Xiaoyu ', ' xiaoudafc ', ' 123 '),)
Cur.close () Conn.close ()

(2) SQL injection

For user input information, we do not directly splicing, need to filter processing, or use a modular approach to help us solve the problem directly

# It turns out we're string concatenation of SQL # sql="select * from UserInfo where name= '%s ' and password= '%s '" % (USER,PWD) # Print (SQL) # res=cursor.execute (SQL) #改写为 (execute helps us do string concatenation, we don't need and must not add quotes for% s) SQL ="select * from UserInfo where name=%s and password=%s" #!!! Note that%s needs to be stripped of the quotation marks, because Pymysql will automatically add res=cursor.execute (sql,[user,pwd]) #pymysql模块自动帮我们解决sql注入的问题, As long as we follow the rules of Pymysql.

(3) Additional deletions require commit

Add one at a time
' INSERT into Users (Name,fullname,password) VALUES ("Dsada", "dasdasf", "Dsad") '  'insert into Users (Name,fullname,password) VALUES (%s,%s,%s)'cur.execute ( SQL, ("aaaaa","asd666","6666 "))
#获取插入的最后一条数据的自增ID

Print (CUR.LASTROWID) #只对一次增加一个可以直接看出, for adding multiple quantities at a time

execute multiple SQL at one time='INSERT into Users (Name,fullname,password) VALUES (%s,%s,%s)'Cur.executemany (sql,[("aaadsaaa","Asd6dasd66","6666"),("adsaaaaa","asd6awf66","6666"),("awaaaaa","asd666","6666")])

Note that execute and Executemany will return the number of execution impact bars after execution

(4) Enquiry

sql ="SELECT * from Users"rows=cur.execute (SQL) #返回影响的函数rows, results are placed in a collection, waiting for the query ret=Cur.fetchone () #获取一条数据print (ret) # (1,'Xiaoyu','Xiaoudaf','123') ret= Cur.fetchmany (2) #获取指定数量的数据 from the current position, print (ret) # (2,'Xiaoyu','XIAOUDAFC','123'), (3,'DAF','Fafwafs','FWA')) RET=Cur.fetchall () #获取当前位置下的所有数据print (ret) #移动游标位置cur. Scroll (0, mode="Absolute") ret=Cur.fetchall () #获取了所有的数据print (ret) cur.scroll (-5, mode="relative"#游标处于最后, now move forward 5 ret relative to the last=Cur.fetchall () #获取了后5条数据print (ret)

Python---Fundamentals review (eight) Database Basics operations (SQLite and MySQL)