pymysql is a library used to operate MySQL database in Python. It provides functions for connecting, querying, inserting, updating and other operations with MySQL database.
1. Install PyMySQL
There are 2 ways:
1. pip command installation: pip install PyMySQL;
2. Select File -> Settings -> Interpreter through pycharm to search for PyMySQL and install it.
2. Using PyMySQL
1. Connect to the database
To establish a connection channel for data transmission with the database, you need to use the connect() method under pymysql. The pymysql.connect() method returns the Connection instance under the Connections module. The connect() method passes parameters to the init of the Connection class. Initializing the magic method parameters can also be understood as the connect() method is creating a new Connetion class.
2. Operation database
After using the pymysql.connect() method to establish a connection with the database, you need to use a cursor when you want to operate the database. Call the cursor() method through the connected database (here db) to return a new cursor object. , the cursor object can be used repeatedly before the connection is closed.
cursor = db. cursor()
Database operations require the use of the execute() method provided by an instance of the Cursor class (that is, the cursor object created through db.cursor()) to execute the SQL statement and return the result if successful.
2.1 Query operation
sql = "select * from table" response1 = cursor. execute(sql) response2 = cursor. fetchall
There are two response returns here,
response1 is to receive the return data of cursor.execute(sql). This return may not be the return value you want, because it returns the number found in the query, which is an int type number.
response2 calls the fetchall method: when querying, all rows in the result set are obtained, each row constitutes a tuple, and then these tuples are returned (that is, nested tuples).
When calling the cursor.execute(sql) method to query data, if the execution is successful, the method to obtain the query result set is as follows:
2.2 Insert operation
sql = "insert into user_pwd(username,password) values('vera', '1234')"
cursor. execute(sql)
There is something to note about the table name when inserting data here. It is best to bring the database name when inserting the table name, for example: sql = “insert into database.table(username, password) values(‘xxx\ ‘, ‘123’)”, otherwise the following error may occur:
ps: The data connected to the query is historical data (a method of obtaining real-time data). If the data immediacy is very high, or the data may change at any time, then the use of close will be very important. close is to close the current connection. Note that this is not the same as sqlalchemy’s close. cur.close() closes the cursor, but just closing the cursor is not enough. Creating a new cursor still queries historical data. Only the current db.connect database connection is closed, that is, Only after re-creating the connection after db.close() can the latest data be obtained. After some research, it is actually related to mysql transactions, so another method is: db.commit() submits the transaction, which is just a query. Also commit the current transaction and start a new transaction, and the connection will re-obtain the latest content. Otherwise, the queried data will still be historical data, not the latest data. — In sqlalchemy, this is achieved by creating an engine and a connection pool. There can be multiple connections in a connection pool. Calling session.close() closes the current connection, the same as db.close() here. , but sqlalchemy has a connection pool. If you want to continue to use the connection after closing, the connection pool will automatically create a connection. You can use the session directly. There is no need to re-create the connection. However, pymysql does not work. pymysql creates a separate connection without a connection. Pool concept.
if cursor:
cursor.close()
db. close()
2.3 How to pass values to variables in SQL statements
Use %s instead for variables. Be sure to note that the sql statement is a non-numeric type, and %s also needs to be added in single quotes! ! !
Method 1: When there is 1 variable in sql, you can use the placeholder %s to replace the variable
sql = "insert into xxx values(test) ('%s')" %(test)
cursor.execute(sql)
db.commit
db.close()
Method 2: When there are multiple variables in SQL, use a pair of placeholders to replace the variables. It should be noted that when multiple variable value lists are passed in, the order is from left to right.
sql = "update xxx(table name) set sort1=%s, sort2=%s, sort3=%s where sort4=%s"
cursor.execute(sql,('a','b','c','d'))
db.commit
db.close()
ps: 1. The string replacement method in method 1 can easily give attackers an opportunity to obtain database data through SQL injection attacks. It is recommended to use method 2 to introduce variables in SQL;
2. When using method 1, quotation marks are required if the placeholder value in the query statement is non-numeric. In method 2, no quotation marks are required regardless of whether the placeholder value is a number or not.
2.4format function format string
If you use sql = ‘select * from t_carowner_recharge where order_num = ‘ + order_num directly, the order_num in the spliced sql without quotes will cause a query error.
Format strings via the str.format method
sql = 'select * from t_carowner_recharge where order_num = ' + "'{}'".format(order_num)
It can also be written as
sql = "select * from t_carowner_recharge where order_num = '{}'".format(order_num)
3. Encapsulation and calling of database operation methods
During actual use, it is recommended to encapsulate the addition, deletion, modification, and query methods of the database and call them directly during use to facilitate later script maintenance. The relevant encapsulation code is as follows:
import pymysql
# Encapsulate the database tool class
class SqlUtli(object):
#Add class default value
conn=None
# Connect to the database
@classmethod
def connect_mysql(cls):
# Determine whether conn is empty, if it is creating a connection
if cls.conn is None:
# Configure according to the actual connection information
cls.conn = pymysql.connect(host="xxx",
port=1234,
user="xxx",
password="xxx!",
database="xxx",
charset="utf8")
return cls.conn
#Close database connection
@classmethod
def close_conn(cls):
# Determine whether conn is empty, if it is not empty, it needs to be closed
if cls.conn is not None:
cls.conn.close()
cls.conn = None
# Query operation
@classmethod
def select(cls, sql):
cursor = None
res=None
try:
# Call the create connection method to link to the database
cls.conn = cls.connect_mysql()
# Get the cursor
cursor = cls.conn.cursor()
#Execute sql statement
cursor.execute(sql)
# Get the returned result set, fetchall() (get all the data), fetchmany(size) (size specifies how many pieces of data to get), fetchone() (get the first piece of data)
res = cursor.fetchall()
except Exception as err:
print('An error occurred while querying sql!', print(str(err)))
finally:
# close the cursor
cursor.close()
# Call the close connection method to close the data connection
cls. close_conn()
# Return the sql query result set to res
return res
# Add, update, delete operations, method sharing, different operations are distinguished by the incoming sql
@classmethod
def insert_update_delete(cls, sql):
cursor = None
try:
# Call the create connection method to connect to the database
cls.conn = cls.connect_mysql()
# Get the cursor
cursor = cls.conn.cursor()
# Execute the update statement
cursor.execute(sql)
# Get the number of affected rows
print("Affected rows", cls.conn.affected_rows())
# Submit the transaction
cls.conn.commit()
except Exception as err:
# rollback transaction
cls.conn.rollback()
print("Failed to execute addition, deletion and modification of sql: " + str(err))
finally:
# close the cursor
cursor.close()
#Call the close connection method to close the data connection
cls. close_conn()
In the above code, no matter query or update, the specific query sql needs to be passed in. When variable query is involved, the splicing sql method described above can be used to combine.