It is well known that to execute SQL statements in python programs you need to use a third-party module: pymysql.
Below, I will give you a brief overview of the general process of installing to using the pymysql third-party libraries.
Installation of pymysql
The system installs pymysql via pip:
pip3 install pymysql
The pymysql module can be installed on the system using the following command:
sudo pip3 install pymysql
Connecting to a database in a python program:
The process can be divided into 6 steps, next I will give you a brief description of the process and code.
1. Import module
import pymysql
2. Create a database connection object
host: IP address of the database, the local domain name is localhost and the local IP is 127.0.0.1.
port: the port of the database, default 3306
user: the user name of the database
password: password for the database username
database: name of the database to be used after connection
charset: the character set of the database
con = (host,port,user,password,database,charset)
Note: connect = Connect = Connection in pymysql
3. Use the database connection object to call the cursor () method to create a cursor
cur = ()
Note: When creating a cursor will open an implicit thing by default, in the implementation of the operation need to commit after the add, delete and change submit, if not submit the default thing for the rollback rollback
4. Write SQL statement strings and execute the SQL statements.
sql = ''' SQL statements for add, delete, and retrieve ''' (sql,parameters) # execute method parameters can be used tuple tuple, list list, dictionary dict three ways to pass parameters, generally use the tuple or list way.
When you need to get the results after displaying the query you can fetch the result tuple after the query by fetchall(), fetchmany(), fetchall() methods.
# Get a piece of data in the query result () # Get the specified data in the query result (item count (of a consignment etc)) # Get all the data in the query result () # Note: This approach is comparable to removing items from a storehouse, which are removed once and then gone. # Use this method will have a counter by default, record the index value of the tuple from the query results, every time you take out the index value +1
5. Commit the transaction and close the cursor
# Transactions need to be committed after adding, deleting or changing data, otherwise all operations are invalidated. # Submission of transactions () # Close the cursor ()
6. Close the database connection
()
This is the whole content of this article.