The use of pymysql module

I. Connecting to MySQL Server

import pymysql

# Connect to the database and call the Connect function (the Connect function returns an object of Connection)
conn = pymysql.Connect(host='localhost',        #Host ip
                             port=3306,         #port
                             user='user',       #User name
                             password='passwd', #Password
                             database='db',     #Database name
                             charset='utf8',    #character set
                            )

2. Operating the database (adding, deleting, modifying and checking)

mysql> select * from userinfo;
+----+-------+------+
| id | name  | pwd  |
+----+-------+------+
|  1 | sssss | 123  |
|  2 | abc   | 233  |
+----+-------+------+
1. Query (simulated user login)
username = input('Please enter a user name:')
pwd = input('Please input a password:')
#Write the SQL statement to be executed (string form, pay attention to the use of single and double quotation marks)
sql = 'select * from userinfo where name="%s" and pwd="%s"'%(username,pwd)
cur = conn.cursor()     #Call the cursor method, which returns a Cursor object
cur.execute(sql) #Call the execute method to execute the sql statement (if executed successfully, the number of query results will be returned here)
cur.close()      #Close
conn.close()

#The above sql statement has a "vulnerability". If username input: XX "or 1 = 1 -- xxxx, such a Sao operation can use the wrong username and skip the password, because it is equivalent to executing a select * from userinfo where name=" XX "or 1 = 1 - xx" and PWD = "here at will" statement, 1 = 1 is always valid, and 1 = 1 is commented out later.

username = input('Please enter a user name:')
pwd = input('Please input a password:')
sql = 'select * from userinfo where name=%(name)s and pwd=%(pwd)s'
cur.execute(sql,{'name':username,'pwd':pwd}) #Anti-annotation Processing
cur.close()     
conn.close()
2. additions and deletions
mysql> select * from userinfo;
+----+--------+------+
| id | name   | pwd  |
+----+--------+------+
|  1 | Sroxi  | 123  |
|  2 | abc    | 233  |
|  3 | QaQ    | 306  |
|  4 | Violet | 520  |
+----+--------+------+
#increase
cur = conn.cursor()
sql = 'insert into userinfo(username,pwd) valuses(%s,%s)'
cur.execute(sql,(username,pwd))
#cur.executemany(sql,[(username1,pwd1),(username2,pwd2)]) #Insert multiple data at a time
#change
sql = 'update userinfo set name = %s where id = 1'
cur.execute(sql,username)
#Delete 
sql = 'delete from userinfo where id = 2'
cursor.execute(sql)



conn.commit() #Make sure to commit after adding, deleting and modifying, otherwise the operation is invalid.
cursor.close()
conn.close()
3. Check: fetchone(), fetchmany(), fetchall() and scroll()

Before using these three methods, you must first use execute(), otherwise raise err. Programming Error ("execute() first") and persist select statements, which can only be found in the temporary table obtained from the select query.

  1. fetchone() queries a record and moves the cursor to the next row
  2. fetchall() queries all data
  3. fetchmany(N) queries N pieces of data
  4. scroll(value,mode) moves cursor, negative mode; it can be'relative'or'absolute'
3.1 fetchone () and fetchall()
#By default, the value we get is tuple. We can only see the data of each row, but do not know what each column represents. At this time, we can return to the dictionary in the following way.
cur = conn.cursor(cursor=pymysql.cursors.DictCursor)
sql = 'select * from userinfo '
cur.execute(sql)
row = cur.fetchone() 
print(row)
row1 = cur.fetchone()
print(row1)
row2 = cur.fetchall()
print(row2)

//Print results:
{'id': 1, 'name': 'Sroxi', 'pwd': '123'}
{'id': 2, 'name': 'abc', 'pwd': '233'}
[{'id': 3, 'name': 'QaQ', 'pwd': '306'}, {'id': 4, 'name': 'Violet', 'pwd': '520'}]
3.2 fetchmany () and scroll()
cur.scroll(-2,'relative')  #Negative values move up,'relative'indicates the current position and absolute() indicates the starting position.
row3 = cur.fetchmany(2) #Read two records
print(row3)

Tags: MySQL SQL Database Programming

Posted on Thu, 03 Oct 2019 15:28:03 -0400 by phpdood