Shulou(Shulou.com)06/02 Report--

This article mainly explains "Python operation MySQL database example analysis", interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn "Python operation MySQL database instance analysis" it!

1. Installation

There are many libraries to connect to the MySQL database through Python. The officially recommended MySQL Connector/Python library is used here, and 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, and I install 8.0.17, which corresponds to the 8.0 version of MySQL. MySQL unifies the large version numbers of its related tools, which must be the same or higher to be compatible. For example, I am using MySQL8.0. If I use a mysql-connector less than 8, I will report an error. As a matter of fact, if you are prompted to install pip install mysql-connector in some old documents, an earlier version will be installed and an error will be reported when connecting to MySQL as follows:

Mysql.connector.errors.NotSupportedError: Authentication plugin 'caching_sha2_password' is not supported

This is because mysql8.0 uses Use Strong Password Encryption for Authentication, that is, strong password encryption, while the lower version of mysql-connector uses the old mysql_native_password encryption, which makes it impossible to connect, so be careful to use a version that is compatible with the database.

2. Connect

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

Import mysql.connectordb = 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, you can operate the database and table. For example, create a database, data table

# get the cursorcursor of the database = db.cursor () # create the database cursor.execute ("CREATE DATABASE mydatabase") # create the data table dbcursor.execute ("CREATE TABLE customers (name varchar), address varchar (255)") # modify the 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. Add, delete and correct

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

Remember to commit the operation transaction to the database through the commit () of the database object after performing all the SQL operations, and roll back the operation through the rollback () method if you need to undo it.

Variables in the SQL statement can be used as placeholders in the form of% s, and then filled in as tuples in python, as shown below:

It is worth noting that no matter what type of data is passed in, it is treated as a string type, and then the string is converted to the corresponding type when the SQL operation is performed, so the placeholder here is% s, without% d,% f, and so on.

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

You can also populate variables in the form of dictionaries in python, and placeholders in SQL statements need to use the corresponding variable names.

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

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 as an array to the second parameter.

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

Sql = "INSERT INTO customers (name, address) VALUES (% s,% s)" # populate 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 last id is:% d"% (cursor.rowcount, cursor.lastrowid)

The method of modifying and deleting data is similar to insertion, only the corresponding SQL statements and variable values are passed to the execute () function. You can see that the operation of the 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. Query

The query operation is similar to before, where the corresponding SQL statement is executed through execute (), and the corresponding data is filled in during execution. After the query is finished, the result set is saved in cursor, and cursor can be directly used as an iterator iterator to expand to get the corresponding fields of each data in the result set. You can also get all or one result set through the fetchall () and fetchone () methods of cursor.

# query data with id between 6 and 8 in customers table and return name, address field 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 for (name,address) in cursor: print ("% s home address is% s"% (name) Address)) # the output is as follows: # Peter home address is Lowstreet home Amy home address is Apple st 65 home Hannah home address is Mountain 21

More complex query operations can be performed through native SQL statements, such as setting query conditions through where, sorting fields by Order by, setting the number of results returned by Limit, offsetting the OFFSET query result set, and Join for table join operations.

At this point, I believe that everyone on the "Python operation MySQL database instance analysis" have a deeper understanding, might as well to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.