Basic operation of Pymysql

What is Pymysql?

Pymysql is a library used in the python3.x version to connect to the MySQL server, and MySQLdb is used in Python2.

Pymysql follows the Python database API v2.0 specification and includes the Pure-python MySQL client library.

Installation of Pymysql

PIP3 Install Pymysql#pymysql module, which belongs to the socket module.

Basic use of Pymysql uses Pymysql module to fix the order of execution

1. Establish a connection
2. Get the cursor.
3. Execute SQL statement
4. Close (cursors, connections)

Small example:

Import Pymysqlusername=Input'>>>:'). Strip () PWD=Input'>->>:'). Strip () #建立连接db=Pymysql.connect (Host='192.168.9.99', Port=3306,     User='Root', Password='123', DB= 'dbname', CharSet='UTF8',) #拿到游标cursor =Db.cursor() #执行SQL语句 # sql= 'SELECT * from userinfo where username = "%s" and pwd = "%s"'SQL= 'INSERT INTO UserInfo (USERNAME,PWD) VALUES (%s,%s)'try: #rows变量得到数据库中被影响的数据行数. Rows= cursor.Execute(SQL, (username, pwd)) # submits a db to the database.Commit() #如果没有commit (), the field in the library has shifted downward but the content is not written in, but the automatically generated ID is automatically incremented. except: # Roll Back db when an error occurs.rollback() #关闭 (cursors, databases)cursor.Close() db.Close()ifrows: #通过ROWS的返回值来判断执行结果. Print('Connection Successful')Else:    Print('Logon Failure')

Inserting more than one data: Executemany ()

Use Executemany () to insert more than one message. To be inserted in the form of a list containing a tuple . [(),(),()]

= cursor. Executemany (SQL,['sly ', ' 123 ', (' hh ', ' 123 ')]) #这个例子中的rows可以不加. Its role is to record the number of rows affected. 

Inquire

Fetch the returned data by fetching (altogether three kinds)

1. Fetchone () One piece of data at a time.
2. Fetchmany () Once multiple data, fill in parentheses with the number of data bars to read. Do not fill the 1 data, if the reading exceeds the actual number of bars, only show the actual number of bars.

3. Fetchall () reads all data at once and returns an empty tuple or empty list if there is no data in the pipeline.

   For example,print(cursor. Fetchone ()) takes a piece of data in the pipeline. Note: Using the Fetchone () value over the maximum number will return none

The retrieved values are displayed in a dictionary: pymysql.cursors.DictCursor

Need to be loaded when cursors are taken

For example:cursor= db. cursor (Pymysql.cursors.DictCursor)

Move pointer

Note: Once the query is finished, the query is empty again because the pointer is to the last bit, and if you want to find it again, you need to point the pointer to the specified location. In addition, the position of the pointer is the same as the list, with the first 0. (The No. 0 bit is the first data)

There are two ways to move a pointer:

1, relative absolute position movement: absolute

#指的是由第一条记录开始的第N个位置 # Example: Scroll (2, Mode='absolute') # The pointer points to the 2nd digit of the data record. The first record of the data is the No. 0 digit.

2, move relative to current position: relative

#指的是由当前的记录位置开始的第N个位置 # Example: Scroll (3, Mode='relative') # The pointer points to the post 3-bit record of the current data position.

3, query the current database pointer location: LASTROWID

#上次光标位置 Print (cursor. Lastrowid)

Small exercise:

Import Pymysqlconn=Pymysql.connect (Host='127.0.0.1', Port=3306,    User='Sly', Password='123', DB='pytest')cursor =Conn.cursor(pymysql.cursors.DictCursor) SQL= 'SELECT * from UserInfo'# sql= 'SELECT * from UserInfo where user=%s and pwd=%s'#查询 # sql= 'SELECT * from UserInfo where user= "%s" and pwd= "%s"' %(User, PWD) #该方法不安全, to be imported using the pymysql die table. # sql= 'INSERT INTO UserInfo (user,pwd) value (%s,%s)'#增加 # sql= 'Update userinfo set user=%s where user=%s'#更改 # sql= 'Delete from userinfo where user=%s and pwd=%s'#删除 #Print(SQL) # rows= cursor.Execute(SQL, (User, PWD)) # Rows= cursor. Executemany (SQL,[(' sly1 ', ' 123 '), (' sly2 ', ' 123 '), (' Sly3 ', ' 123 ')]) Rows= cursor.Execute(SQL)Print(cursor. Lastrowid)cursor. Scroll (1, mode='Absolute')Print(cursor. Fetchone ()) #cursor. Scroll (2, mode='relative')Print('This is fetch one ...')Print(cursor. Fetchmany (2))Print('This is fetch many ....')Print(cursor. Fetchall ())Print('This is the fetch all ...')Print(rows) conn.Commit()cursor.Close() Conn.Close()

Basic operation of Pymysql