Details of Python operating MySQL database instance [installation, connection, addition, deletion, modification, query, etc.]

Time:2020-9-22

This paper describes how Python operates MySQL database. For your reference, the details are as follows:

1. Installation

There are many libraries to connect to MySQL database through python. Here we use the official MySQL connector / Python library. Its official website is: https://dev.mysql.com/doc/connector-python/en/ 。

Install through the PIP command:


pip install mysql-connector-python

The latest version is installed by default. I installed 8.0.17, which corresponds to the 8.0 version of MySQL. MySQL unifies the large version number of its related tools, which must be the same or higher to be compatible. For example, if I use MySQL 8.0, if I use MySQL connector lower than 8, an error will be reported. In fact, when installing MySQL with lower versions, you will be prompted as follows:

mysql.connector.errors.NotSupportedError: Authentication plugin ‘caching_sha2_password’ is not supported

This is because MySQL 8.0 uses use strong password encryption for authentication, which is strong password encryption, while the lower version of MySQL connector uses the old mysql_ native_ Password encryption method, resulting in unable to connect, so pay attention to using the version compatible with the database.

2. Connection

You can connect the database through the connect() method of the connector class, and pass in the parameters such as server, port number, user name, password, database, etc., in which the server and port number can be omitted, and the default is localhost:3306 。


import mysql.connector
db = mysql.connector.connect(
  host='localhost',
  port='3306',
  user="root",
  password="123456",
  database="test"
)

3. Database and table operation

The operation of database and data table belongs to schema definition language (DDL). The execution of all DDL statements depends on a data structure called cursor. After getting the cursor object from the connect object, the database and table operations can be carried out. For example, create a database, data table

#Get the cursor of the database
cursor = db.cursor()
#Create database
cursor.execute("CREATE DATABASE mydatabase")
#Create data table
dbcursor.execute("CREATE TABLE customers (name varchar(255),address varchar(255))")
#Modify table operation
dbcursor.execute('ALTER TABLE customers ADD COLUMN id INT PRIMARY KEY AUTO_INCREMENT')
#Query and print all tables in the database
cursor.execute("show tables")
for table in cursor:
  print(table)

4. Addition, deletion and modification

Insert, delete and modify operations are still implemented through the cursor object. SQL operations are performed through the cursor execute() method. The first parameter is the SQL statement to be executed, and the second parameter is the variable to be filled in the statement.

After executing all SQL operations, remember to commit the operation transaction to the database through the commit() of the database object. If you need to undo, roll back the operation through the rollback() method.

Variables in SQL statements can be used as placeholders in the form of% s, and then filled in variables in the form of tuples in Python during execution, as shown below:

It is worth noting that no matter what type of data is passed in as string type, and then the string will be converted to the corresponding type when SQL operation is executed. Therefore, the place holder here is% s instead of% d or% F, etc.

#SQL statement to execute
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
#Fill in the data as tuples
val = ('Mike', 'Main street 20')
#Perform the operation
cursor.execute(sql, val)
#Commit transaction
db.commit()

You can also fill in variables in the form of a dictionary in Python, and the corresponding variable name is required for the place holder in the SQL statement

#Specify the variable name in the SQL statement
sql = "INSERT INTO customers (name, address) VALUES (%(name)s, %(address)s)"
#Fill in the data in the form of a dictionary
val = {
  'name': 'Alice',
  'address': 'Center street 22'
}
cursor.execute(sql, val)

If you need to insert more than one piece of data at a time, you can use the executemany() method to pass multiple pieces of data to the second parameter as an array.

The number of successful operations can be returned through the rowcount property of cursor, and the lastrowid attribute is the ID of the last row successfully inserted

sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
#Fill the data as an array
val = [
 ('Peter', 'Lowstreet 4'),
 ('Amy', 'Apple st 652'),
 ('Hannah', 'Mountain 21'),
]
cursor.executemany(sql, val)
Print ("successfully inserted% d pieces of data, the ID of the last entry is% d)"( cursor.rowcount , cursor.lastrowid )

The method of modifying and deleting data is similar to that of inserting. You only need to pass the corresponding SQL statement and variable value to the execute() function. We can see that the operation of MySQL connector library is very close to the native SQL language.

#Modify data
sql = "UPDATE customers SET address=%s WHERE name=%s"
val = ('Center street 21', 'Mike')
cursor.execute(sql, val)
#Delete data
sql = "DELETE FROM customers WHERE name=%s"
val = ('Hannah',)
cursor.execute(sql, val)

5. Inquiry

The query operation is similar to the previous one. The corresponding SQL statement is executed through execute() and the corresponding data can be filled in when executing. After the query is finished, the result set is saved in the cursor. The cursor can be directly expanded as an iterator to obtain the corresponding fields of each data in the result set. You can also get all or one result set by fetchall(), fetchone() methods of cursor.

#Query the data with ID between 6 and 8 in the customers table and return the name and address fields
query = "SELECT name,address FROM customers WHERE id BETWEEN %s AND %s"
cursor.execute(query, (6, 8))
#Loop out each data in the result set and print it
for (name, address) in cursor:
  Print (% s address is% s "% (name, address))
#The output results are as follows:
#Peter's address is lowstreet
#Amy's home address is apple st 652
#Hannah's address is mountain 21

More complex query operations can be carried out through native SQL statements, such as setting query conditions through where, sorting fields by order by, setting the number of returned results by limit, offsetting the query result set by offset, and joining for table join operations

More interested readers about Python related content can view the special topics of this website: summary of common Python database operation skills, summary of Python mathematical operation skills, python data structure and algorithm tutorial, summary of Python function use skills, summary of Python character string operation skills, python introduction and advanced classic tutorial, and python files and directories Summary of operation skills

I hope this article will be helpful to python programming.

Recommended Today

Network foundation (1)

Blog reference http://www.cnblogs.com/gsk99/p/4920699.html http://www.cnblogs.com/iloverain/p/5619665.html http://blog.csdn.net/leo_walker/article/details/51627760 TCP diagram TCP structure Network interface layer The physical layer defines some characteristics related to the interface of transmission media, i.e. mechanical characteristics, electrical characteristics, functional characteristics and process characteristics, and needs to complete the conversion between parallel transmission and serial transmission. The data link layer provides transparent and reliable […]