Of Stack

Summary of the use of pymysql of Python

  • 2021-07-06 11:27:56
  • OfStack

In python3. x, you can use pymysql to MySQL database connection, and achieve a variety of database operations, this blog mainly introduces the installation and use of pymysql.

Installation of PyMySQL

1.. Installation method on windows:

In python3.6, pip3 comes with it, so pip3 can be directly used to install the required modules in python3: 


pip3 install pymysql -i https://pypi.douban.com/simple

2.. Installation method under linux:

1. tar package download and decompression 


 Download tar Bag 
wget https://pypi.python.org/packages/29/f8/919a28976bf0557b7819fd6935bfd839118aff913407ca58346e14fa6c86/PyMySQL-0.7.11.tar.gz#md5=167f28514f4c20cbc6b1ddf831ade772
 Extract and expand tar Bag 
tar xf PyMySQL-0.7.11.tar.gz

Step 2 Install 


[root@localhost PyMySQL-0.7.11]# python36 setup.py install

Connection to database

Data and tables created in this test: 


# Create databases and tables, and then insert data 
mysql> create database dbforpymysql;
mysql> create table userinfo(id int not null auto_increment primary key,username varchar(10),passwd varchar(10))engine=innodb default charset=utf8;
mysql> insert into userinfo(username,passwd) values('frank','123'),('rose','321'),('jeff',666);

# View table contents 
mysql> select * from userinfo;
+----+----------+--------+
| id | username | passwd |
+----+----------+--------+
| 1 | frank  | 123  |
| 2 | rose   | 321  |
| 3 | jeff   | 666  |
+----+----------+--------+
3 rows in set (0.00 sec)

Connect to the database: 


import pymysql

# Connect to a database 
db = pymysql.connect("localhost","root","LBLB1212@@","dbforpymysql")

# Use cursor() Method to create 1 Cursor objects 
cursor = db.cursor()

# Use execute() Method execution SQL Statement 
cursor.execute("SELECT * FROM userinfo")

# Use fetall() Get all the data 
data = cursor.fetchall()

# Print the acquired data 
print(data)

# Close the connection between cursor and database 
cursor.close()
db.close()

# Running result 
((1, 'frank', '123'), (2, 'rose', '321'), (3, 'jeff', '666'))

To complete the connection of 1 MySQL data, the following parameters can be accepted in connect: 


def __init__(self, host=None, user=None, password="",
       database=None, port=0, unix_socket=None,
       charset='', sql_mode=None,
       read_default_file=None, conv=None, use_unicode=None,
       client_flag=0, cursorclass=Cursor, init_command=None,
       connect_timeout=10, ssl=None, read_default_group=None,
       compress=None, named_pipe=None, no_delay=None,
       autocommit=False, db=None, passwd=None, local_infile=False,
       max_allowed_packet=16*1024*1024, defer_connect=False,
       auth_plugin_map={}, read_timeout=None, write_timeout=None,
       bind_address=None):
 Parameter interpretation: 
host: Host where the database server is located  # Host name or host address 
user: Username to log in as  # User name 
password: Password to use.  # Password 
database: Database to use, None to not use a particular one.  # Specified database 
port: MySQL port to use, default is usually OK. (default: 3306)  # Port, default is 3306
bind_address: When the client has multiple network interfaces, specify
  the interface from which to connect to the host. Argument can be
  a hostname or an IP address.  # When the client has multiple network interfaces, point to the interface connected to the database, which can be 1 Hostname or ip Address 
unix_socket: Optionally, you can use a unix socket rather than TCP/IP.
charset: Charset you want to use.  # Specify character encoding 
sql_mode: Default SQL_MODE to use. 
read_default_file:
  Specifies my.cnf file to read these parameters from under the [client] section.
conv:
  Conversion dictionary to use instead of the default one.
  This is used to provide custom marshalling and unmarshaling of types.
  See converters.
use_unicode:
  Whether or not to default to unicode strings.
  This option defaults to true for Py3k.
client_flag: Custom flags to send to MySQL. Find potential values in constants.CLIENT.
cursorclass: Custom cursor class to use.
init_command: Initial SQL statement to run when connection is established.
connect_timeout: Timeout before throwing an exception when connecting.
  (default: 10, min: 1, max: 31536000)
ssl:
  A dict of arguments similar to mysql_ssl_set()'s parameters.
  For now the capath and cipher arguments are not supported.
read_default_group: Group to read from in the configuration file.
compress; Not supported
named_pipe: Not supported
autocommit: Autocommit mode. None means use server default. (default: False)
local_infile: Boolean to enable the use of LOAD DATA LOCAL command. (default: False)
max_allowed_packet: Max size of packet sent to server in bytes. (default: 16MB)
  Only used to limit size of "LOAD LOCAL INFILE" data packet smaller than default (16KB).
defer_connect: Don't explicitly connect on contruction - wait for connect call.
  (default: False)
auth_plugin_map: A dict of plugin names to a class that processes that plugin.
  The class will take the Connection object as the argument to the constructor.
  The class needs an authenticate method taking an authentication packet as
  an argument. For the dialog plugin, a prompt(echo, prompt) method can be used
  (if no authenticate method) for returning a string from the user. (experimental)
db: Alias for database. (for compatibility to MySQLdb)
passwd: Alias for password. (for compatibility to MySQLdb)

cursor actually calls the class of Cursor under cursors module. The main function of this module is to interact with the database. When you instantiate an object, you can call various binding methods below the object: 


class Cursor(object):
  """
  This is the object you use to interact with the database.
  """
  def close(self):
    """
    Closing a cursor just exhausts all remaining data.
    """
  def setinputsizes(self, *args):
    """Does nothing, required by DB API."""

  def setoutputsizes(self, *args):
    """Does nothing, required by DB API."""    
  def execute(self, query, args=None):
    """Execute a query

    :param str query: Query to execute.

    :param args: parameters used with query. (optional)
    :type args: tuple, list or dict

    :return: Number of affected rows
    :rtype: int

    If args is a list or tuple, %s can be used as a placeholder in the query.
    If args is a dict, %(name)s can be used as a placeholder in the query.
    """
  def executemany(self, query, args):
    # type: (str, list) -> int
    """Run several data against one query

    :param query: query to execute on server
    :param args: Sequence of sequences or mappings. It is used as parameter.
    :return: Number of rows affected, if any.

    This method improves performance on multiple-row INSERT and
    REPLACE. Otherwise it is equivalent to looping over args with
    execute().
    """
  def fetchone(self):
    """Fetch the next row"""
  def fetchmany(self, size=None):
    """Fetch several rows"""
  def fetchall(self):
    """Fetch all the rows"""
  ......

Database operation

1. Database addition, deletion and modification operations

commit () method: Add, delete, change in the database, must be submitted, otherwise the inserted data does not take effect. 


import pymysql
config={
  "host":"127.0.0.1",
  "user":"root",
  "password":"LBLB1212@@",
  "database":"dbforpymysql"
}
db = pymysql.connect(**config)
cursor = db.cursor()
sql = "INSERT INTO userinfo(username,passwd) VALUES('jack','123')"
cursor.execute(sql)
db.commit() # Submit data 
cursor.close()
db.close()
 Or in execute Provide inserted data 
import pymysql
config={
  "host":"127.0.0.1",
  "user":"root",
  "password":"LBLB1212@@",
  "database":"dbforpymysql"
}
db = pymysql.connect(**config)
cursor = db.cursor()
sql = "INSERT INTO userinfo(username,passwd) VALUES(%s,%s)"
cursor.execute(sql,("bob","123")) 
db.commit() # Submit data 
cursor.close()
db.close()

Small knowledge point, mysql injection problem: 


 In mysql Use in "--" Represents annotations, such as implementing now 1 A small program for users to log in: 
 Both user name and password exist in the table userinfo The table reads as follows: 
mysql> select * from userinfo;
+----+----------+--------+
| id | username | passwd |
+----+----------+--------+
| 1 | frank  | 123  |
| 2 | rose   | 321  |
| 3 | jeff   | 666  |
+----+----------+--------+
3 rows in set (0.00 sec)
 The applet code is as follows: 
import pymysql
user = input("username:")
pwd = input("password:")
config={
  "host":"127.0.0.1",
  "user":"root",
  "password":"LBLB1212@@",
  "database":"dbforpymysql"
}
db = pymysql.connect(**config)
cursor = db.cursor(cursor=pymysql.cursors.DictCursor)
sql = "select * from userinfo where username='%s' and passwd='%s'" %(user,pwd)
result=cursor.execute(sql)
cursor.close()
db.close()
if result:
  print(' Login Successful ')
else:
  print(' Login failed ')
# Run results of correct login 
username:frank
password:123
result: 1
 Login Successful 
# Running results of incorrect login 
username:frank
password:1231231
result: 0
 Login failed 
 It seems that there is nothing wrong with it, but try the following methods 
----------------------------------------------
username:' or 1=1 -- 
password:123
result: 3
 Login Successful 
----------------------------------------------
 Yi ~ I also logged in successfully .
 Why? You can see 1 Next to the current execution sql Statement: 
select * from userinfo where username='' or 1=1 -- ' and passwd='123'
 Here -- The following will be annotated, so where1 Will succeed, which is equal to viewing the contents of all rows, and the return value is not equal to 0 , so the login was successful. 
 The solution is to write variables or arguments directly to the execute Can be: 
result=cursor.execute(sql,(user,pwd))
 After typing something like ' or 1=1 --  You won't log in successfully.

executemany (): Used to insert multiple pieces of data at the same time: 


import pymysql
config={
  "host":"127.0.0.1",
  "user":"root",
  "password":"LBLB1212@@",
  "database":"dbforpymysql"
}
db = pymysql.connect(**config)
cursor = db.cursor()
sql = "INSERT INTO userinfo(username,passwd) VALUES(%s,%s)"
cursor.executemany(sql,[("tom","123"),("alex",'321')])
db.commit() # Submit data 
cursor.close()
db.close()

Both execute () and executemany () return the number of rows affected: 


 Download tar Bag 
wget https://pypi.python.org/packages/29/f8/919a28976bf0557b7819fd6935bfd839118aff913407ca58346e14fa6c86/PyMySQL-0.7.11.tar.gz#md5=167f28514f4c20cbc6b1ddf831ade772
 Extract and expand tar Bag 
tar xf PyMySQL-0.7.11.tar.gz
0

When there is a self-increasing primary key in the table, you can use lastrowid to get the last self-increasing ID: 


 Download tar Bag 
wget https://pypi.python.org/packages/29/f8/919a28976bf0557b7819fd6935bfd839118aff913407ca58346e14fa6c86/PyMySQL-0.7.11.tar.gz#md5=167f28514f4c20cbc6b1ddf831ade772
 Extract and expand tar Bag 
tar xf PyMySQL-0.7.11.tar.gz
1

2. Query operation of database

Here are three binding methods:

fetchone (): Obtain the next 1 row of data, and the first row is the first; fetchall (): Get all row data sources fetchmany (4): Get the next 4 rows of data

Let's look at the contents of the table first: 


 Download tar Bag 
wget https://pypi.python.org/packages/29/f8/919a28976bf0557b7819fd6935bfd839118aff913407ca58346e14fa6c86/PyMySQL-0.7.11.tar.gz#md5=167f28514f4c20cbc6b1ddf831ade772
 Extract and expand tar Bag 
tar xf PyMySQL-0.7.11.tar.gz
2

Using fetchone (): 


import pymysql
config={
  "host":"127.0.0.1",
  "user":"root",
  "password":"LBLB1212@@",
  "database":"dbforpymysql"
}
db = pymysql.connect(**config)
cursor = db.cursor()
sql = "SELECT * FROM userinfo"
cursor.execute(sql)
res = cursor.fetchone() # No. 1 1 Secondary execution 
print(res)
res = cursor.fetchone() # No. 1 2 Secondary execution 
print(res)
cursor.close()
db.close()

# Running result 
(1, 'frank', '123')
(2, 'rose', '321')

Using fetchall (): 


 Download tar Bag 
wget https://pypi.python.org/packages/29/f8/919a28976bf0557b7819fd6935bfd839118aff913407ca58346e14fa6c86/PyMySQL-0.7.11.tar.gz#md5=167f28514f4c20cbc6b1ddf831ade772
 Extract and expand tar Bag 
tar xf PyMySQL-0.7.11.tar.gz
4

As you can see, when you get it for the second time, you don't get any data, which is similar to the file reading operation.

By default, the return value we get is a tuple, and 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 every row of data will generate a dictionary: 


 Download tar Bag 
wget https://pypi.python.org/packages/29/f8/919a28976bf0557b7819fd6935bfd839118aff913407ca58346e14fa6c86/PyMySQL-0.7.11.tar.gz#md5=167f28514f4c20cbc6b1ddf831ade772
 Extract and expand tar Bag 
tar xf PyMySQL-0.7.11.tar.gz
5

Use fetchall to get the data of all rows, and every row is generated into a dictionary and placed in the list: 


 Download tar Bag 
wget https://pypi.python.org/packages/29/f8/919a28976bf0557b7819fd6935bfd839118aff913407ca58346e14fa6c86/PyMySQL-0.7.11.tar.gz#md5=167f28514f4c20cbc6b1ddf831ade772
 Extract and expand tar Bag 
tar xf PyMySQL-0.7.11.tar.gz
6

In this way, the obtained content can be easily understood and used!

When getting row data, it can be understood that at the beginning, there is a row pointer pointing to the top of the first row, and when getting a row, it moves down one row, so when the row pointer reaches the last row, it can no longer get the contents of the row, so we can use the following methods to move the row pointer: 


 Download tar Bag 
wget https://pypi.python.org/packages/29/f8/919a28976bf0557b7819fd6935bfd839118aff913407ca58346e14fa6c86/PyMySQL-0.7.11.tar.gz#md5=167f28514f4c20cbc6b1ddf831ade772
 Extract and expand tar Bag 
tar xf PyMySQL-0.7.11.tar.gz
7

The first value is the number of rows moved, the integer is moving down, and the negative number is moving up. mode specifies whether to move relative to the current position or relative to the first row

For example: 


sql = "SELECT * FROM userinfo"
cursor.execute(sql)
res = cursor.fetchall()
print(res)
cursor.scroll(0,mode='absolute') # Moved relative to the first line 0 That is, the row pointer is moved to the first row 
res = cursor.fetchall() # No. 1 2 Content obtained for the second time 
print(res)

# Running result 
[{'id': 1, 'username': 'frank', 'passwd': '123'}, {'id': 2, 'username': 'rose', 'passwd': '321'}, {'id': 3, 'username': 'jeff', 'passwd': '666'}, {'id': 5, 'username': 'bob', 'passwd': '123'}, {'id': 8, 'username': 'jack', 'passwd': '123'}, {'id': 10, 'username': 'zed', 'passwd': '123'}]
[{'id': 1, 'username': 'frank', 'passwd': '123'}, {'id': 2, 'username': 'rose', 'passwd': '321'}, {'id': 3, 'username': 'jeff', 'passwd': '666'}, {'id': 5, 'username': 'bob', 'passwd': '123'}, {'id': 8, 'username': 'jack', 'passwd': '123'}, {'id': 10, 'username': 'zed', 'passwd': '123'}]

Context manager

The context manager is supported in the file operation of python, and can also be used when operating the database: 


 Download tar Bag 
wget https://pypi.python.org/packages/29/f8/919a28976bf0557b7819fd6935bfd839118aff913407ca58346e14fa6c86/PyMySQL-0.7.11.tar.gz#md5=167f28514f4c20cbc6b1ddf831ade772
 Extract and expand tar Bag 
tar xf PyMySQL-0.7.11.tar.gz
9

Context managers make code more readable.

Related articles: