Python operation MySQL

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

• Native Module Pymsql
• ORM Framework Sqlachemy

Pymsql

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

Download installation

PIP3 Install pymysql-i https://pypi.douban.com/simple

Use action

1. Execute SQL

Import Pymysqluser= Input ("Username:") PWD= Input ("Password:")
# 创建连接Conn= Pymysql.connect (host="localhost", user='Root', password="', database="db666")# 创建游标
Cursor=conn.cursor () SQL="SELECT * from UserInfo where username= '%s ' and password=%s"%(User,pwd,)
# SELECT * from userinfo where username= ' UU ' or 1=1--' and password= '%s ' #sql注入

# 执行SQL，并返回收影响行数
Cursor.execute (SQL)# 获取第一行数据
Result=Cursor.fetchone () cursor.close () Conn.close ( )ifResult:print ('Login Successful')Else: Print ('Logon Failure')

Note: In the presence of Chinese, the connection needs to add charset= ' UTF8 ', otherwise the Chinese display garbled.

Get Query data

Conclusion: When executing SQL statements, Excute must use parameterized methods, otherwise SQL injection vulnerability will inevitably occur.

Import Pymysqluser = input ("Username") pwd = input ("Password") conn = Pymysql.connect (host="localhost", user=‘Root ", Password=", Database= "db666" ) cursor = conn.cursor () sql =  "select * from UserInfo where username=%s and Password=%s " Cursor.execute (SQL,USER,PWD) #推荐这种方法 to prevent SQL injection 
 # cursor.execute (Sql,[user,pwd]) # Cursor.execute (Sql,{u": User,  "p"  :P WD}) #查询一行 
 result = Cursor.fetchone () 
 #查询全部 
     result = Cursor.fetchall ()
#查询四行
result = Cursor.fetchmany (4)< Span style= "COLOR: #800000" > < Span style= "COLOR: #800000" > 
 Cursor.close () Conn.close () if Result:print (  '  login succeeded  ' " else"  Login failed          

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

Add, Delete, the

conn = Pymysql.connect (host="localhost", user='Root', password="', database="db666") Cursor=conn.cursor () SQL="INSERT INTO UserInfo (Username,password) VALUES (%s,%s)"# Insert multiple rows of data R= Cursor.executemany (sql,[('Egon','SB'),('Laoyao','BS')])#  ******# Commit, otherwise unable to save new or modified data conn.commit () cursor.close () conn.close ()

Fetch data type

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

# Check Conn= Pymysql.connect (host="localhost", user='Root', password="', database="db666")# Cursor set to dictionary type  cursor= Conn.cursor (cursor= pymysql.cursors.DictCursor) SQL="SELECT * from UserInfo"cursor.execute (SQL) Cursor.scroll (1, mode='relative') # Move Cursor.scroll relative to the current position (2, mode='Absolute'# relative absolute position Move # query row result=Cursor.fetchone () print (Result) # query all result=Cursor.fetchall () print (Result) # query 4 rows result= Cursor.fetchmany (4) print (Result) cursor.close () conn.close ()

Gets the newly created data self-increment ID

= Pymysql.connect (host="localhost", user='root', password= ", database="db666"="insert INTO UserInfo (Username,password) VALUES (' Asdfasdf ', ' 123123 ')"cursor.execute (SQL) Conn.commit () # The self-increment ID of the newly inserted data, and the Idprint (CURSOR.LASTROWID) cursor.close () Conn.close () that gets the last bar when inserting multiple bars

Python operation MySQL