python Foundation (36):pymysql module

Keywords: Python SQL MySQL Database

1. pymysql module

Before using pymysql module, you need to learn database MySQL: MySQL Foundation.

1.1 download and use of pymysql

After reading the MySQL foundation, we all operate the database through mysql, the command-line client tool of MySQL. How to operate the database in the python program? This uses the pymysql module, which is essentially a socket client software, which needs to be installed before use.  

1.1.1 download pymysql module

pip3 install pymysql

1.1.2 use of pymysql

Database and data already exist

# Implementation: Using Python Implement user login. If the user exists, the login succeeds (assuming that the user is already in the database)


import pymysql
user = input('Please enter the user name:')

pwd = input('Please input a password:')



# 1.Connect
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='', db='db8', charset='utf8')


# 2.Create cursors
cursor = conn.cursor()

#Be careful%s Quotes required
sql = "select * from userinfo where username='%s' and pwd='%s'" %(user, pwd)
print(sql)

# 3.implement sql Sentence
cursor.execute(sql)

result=cursor.execute(sql) #implement sql Statements, returning sql Number of records successfully queried
print(result)


# Close the connection. Both the cursor and the connection should be closed
cursor.close()
conn.close()

if result:
    print('Landing successfully')
else:
    print('Login failed')

1.2 sql injection of execute()

For the last space, if you encounter select * from userinfo where username ='mjj '-- asadasdas' and PWD =' 'in an sql statement, the condition after -- is commented out (note -- there is a space after)

#1. sql injection: user exists, bypass password
 xhh '-- any character

#2. sql injection: the user does not exist, bypassing the user and password
 Xxx 'or 1 = 1 -- any character

Solution:

# It turned out that we were right sql String splicing
# sql="select * from userinfo where name='%s' and password='%s'" %(username,pwd)
# print(sql)
# result=cursor.execute(sql)

#Rewrite as ( execute Do string splicing for us. We don't need to and can't do it any more%s Quoted)
sql="select * from userinfo where name=%s and password=%s" #!!!Be careful%s Quotes need to be removed because pymysql Will automatically add
result=cursor.execute(sql,[user,pwd]) #pymysql Modules help us solve it automatically sql The problem of injection, as long as we follow pymysql Come on.

1.3 add, delete and change: conn.commit()

commit() method: when adding, deleting, or modifying in the database, you must submit, otherwise the inserted data will not take effect.

import pymysql
username = input('Please enter the user name:')

pwd = input('Please input a password:')



# 1.Connect
conn = pymysql.connect(host='localhost', port=3306, user='root', password='', db='db8', charset='utf8')


# 2.Create cursors
cursor = conn.cursor()


# operation
# increase
# sql = "insert into userinfo(username,pwd) values (%s,%s)"


# effect_row = cursor.execute(sql,(username,pwd))
#Insert multiple data at the same time
#cursor.executemany(sql,[('Li Si','110'),('Wang Wu','119')]) 

# print(effect_row)#

# change
# sql = "update userinfo set username = %s  where id = 2"
# effect_row = cursor.execute(sql,username)
# print(effect_row)


# Delete
sql = "delete from userinfo  where id = 2"
effect_row = cursor.execute(sql)
print(effect_row)


#Must remember commit
conn.commit()

# 4.Close cursor
cursor.close()

# 5.Close connection
conn.close()

1.4 check: fetchone, fetchmany, fetchall

fetchone(): get the next row of data, the first row is the first row;
fetchall(): get all row data sources
 fetchmany(4): get 4 rows of data

Check the following table:

mysql> select * from userinfo;
+----+----------+-----+
| id | username | pwd |
+----+----------+-----+
|  1 | mjj      | 123 |
|  3 | Zhang San     | 110 |
|  4 | Li Si     | 119 |
+----+----------+-----+
rows in set (0.00 sec)

Use fetchone():

import pymysql

# 1.Connect
conn = pymysql.connect(host='localhost', port=3306, user='root', password='', db='db8', charset='utf8')


# 2.Create cursors
cursor = conn.cursor()

sql = 'select * from userinfo'
cursor.execute(sql)


# Query the data in the first row
row = cursor.fetchone()
print(row) # (1, 'mjj', '123')

# Query the second row of data
row = cursor.fetchone()
print(row) # (3, 'Zhang San', '110')

# 4.Close cursor
cursor.close()

# 5.Close connection
conn.close()

Use fetchall():

import pymysql

# 1.Connect
conn = pymysql.connect(host='localhost', port=3306, user='root', password='', db='db8', charset='utf8')


# 2.Create cursors
cursor = conn.cursor()

sql = 'select * from userinfo'
cursor.execute(sql)

# Get all the data
rows = cursor.fetchall()
print(rows)

# 4.Close cursor
cursor.close()

# 5.Close connection
conn.close()

#Operation result
((1, 'mjj', '123'), (3, 'Zhang San', '110'), (4, 'Li Si', '119'))

By default, the return value we get is a tuple. We can only see the data of each row, but we don't know what each column represents. At this time, we can use the following methods to return the dictionary, and the data of each row will generate a dictionary:

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  #When instantiating, attribute cursor Set to pymysql.cursors.DictCursor

In the fetchone example, when acquiring row data, it can be understood that at the beginning, there is a row pointer pointing to the top of the first row, acquiring a row, and it moves down a row, so when the row pointer reaches the last row, it can no longer obtain the content of the row, so we can use the following methods to move the row pointer:

cursor.scroll(1,mode='relative ') moves relative to the current position
 cursor.scroll(2,mode='absolute ') (relative absolute position movement)
The first value is the number of rows moved, the integer is moved down, and the negative number is moved up. mode specifies whether to move relative to the current position or the first row
# 1.Python Implement user login
# 2.Mysql Save data

import pymysql

# 1.Connect
conn = pymysql.connect(host='localhost', port=3306, user='root', password='', db='db8', charset='utf8')


# 2.Create cursors
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

sql = 'select * from userinfo'
cursor.execute(sql)


# Query the data in the first row
row = cursor.fetchone()
print(row) # (1, 'mjj', '123')

# Query the second row of data
row = cursor.fetchone() # (3, 'Zhang San', '110')
print(row)

cursor.scroll(-1,mode='relative') #After setting, the cursor moves forward one line relative to the current position, so the printed result is the data of the second line
row = cursor.fetchone() 
print(row)

cursor.scroll(0,mode='absolute') #After setting, the cursor does not change with respect to the first row, so the printed result is the first row of data
row = cursor.fetchone() 
print(row)

# 4.Close cursor
cursor.close()

# 5.Close connection
conn.close()

#give the result as follows

{'id': 1, 'username': 'mjj', 'pwd': '123'}
{'id': 3, 'username': 'Zhang San', 'pwd': '110'}
{'id': 3, 'username': 'Zhang San', 'pwd': '110'}
{'id': 1, 'username': 'mjj', 'pwd': '123'}

fetchmany():

import pymysql

# 1.Connect
conn = pymysql.connect(host='localhost', port=3306, user='root', password='', db='db8', charset='utf8')


# 2.Create cursors
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

sql = 'select * from userinfo'
cursor.execute(sql)



# Get 2 pieces of data
rows = cursor.fetchmany(2)
print(rows)

# 4.Close cursor

# rows = cursor.fetchall()
# print(rows)
cursor.close()

# 5.Close connection
conn.close()

#The results are as follows:
[{'id': 1, 'username': 'mjj', 'pwd': '123'}, {'id': 3, 'username': 'Zhang San', 'pwd': '110'}]

Posted by gazever on Thu, 21 Nov 2019 02:46:11 -0800