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

In this article, the editor introduces in detail "how to install and configure MySQL database". The content is detailed, the steps are clear, and the details are handled properly. I hope this article "how to install and configure MySQL database" can help you solve your doubts.

1. What is pymysql?

PyMySQL is a library used to connect to MySQL servers in the Python3.x version, and mysqldb is used in Python2. PyMySql follows the specification of Python database API v2.0 and includes pure-Python MySQL client libraries.

two。 Install PyMySQL $pip install pymysql3.MySQL database installation and configuration

Before using PyMySQL to connect to the MySQL database, make sure that the installation and configuration of the MySQL database is complete, and refer to MySQL installation and MySQL management for details on how to install and configure the MySQL database.

4.1. Connect database operation import pymysql# database server name HOSTNAME = 'node05'# database user name USER =' root'# database name DATABASE = 'cayman'# database password PASSWORD =' Love88me'# Open database connection conn = pymysql.connect (HOSTNAME, USER, PASSWORD DATABASE) # use the cursor () method to create a cursor object cursor = conn.cursor () # use the execute () method to execute the SQL query cursor.execute ("select VERSION ()") # use fetchone () to query a single piece of data data = cursor.fetchone () print (f "Database Version: {data}") # close the database connection conn.close () 4.2. Create table operation import pymysql# set database configuration item HOSTNAME = 'node05'USERNAME =' root'PASSWORD = 'Love88me'DATABASE =' cayman'# Open database connection db = pymysql.connect (HOSTNAME, USERNAME, PASSWORD, DATABASE) # create a flow mark object using cursor object cursor = db.cursor () # execute SQL using execute () method Delete cursor.execute ("DROP TABLE IF EXISTS employee") # use preprocessing statements to create table sql = "" CREATE TABLE employee (id bigint primary key auto_increment, user_name varchar (50) not null, age int, sex char (1), income float) "" # execute sql statement cursor.execute (sql) # close database connection db.close () 4.3.1. Database insert single statement import pymysql# sets database configuration item HOSTNAME = 'node05'USERNAME =' root'PASSWORD = 'Love88me'DATABASE =' cayman'# Open database connection db = pymysql.connect (HOSTNAME, USERNAME, PASSWORD, DATABASE) # create a flow mark object using cursor object cursor = db.cursor () # SQL statement sql = "" insert into employee (user_name, age, sex, income) values ('Feng Qingyang', 64, 'male', 22000) "" try: # execute sql statement cursor.execute (sql) # submit db.commit () except: # roll back db.rollback () if an error occurs # close the database connection db.close () 4.3.2. Database insert multiple statements import pymysql# sets database configuration item HOSTNAME = 'node05'USERNAME =' root'PASSWORD = 'Love88me'DATABASE =' cayman'# Open database connection db = pymysql.connect (HOSTNAME, USERNAME, PASSWORD, DATABASE) # create a flow mark object using cursor object cursor = db.cursor () # SQL statement sql = "insert into employee (user_name, age, sex, income) values (% s,% s,% s % s) "data = ('Fengqingyang', 64, 'male', 22000), ('Linghu Chong', 22, 'male', 14000), ('Ren Yingying', 20, 'male', 10000), ('Oriental Invincible', 32, 'male', 18000), ('Let me go', 56, 'male', 17000), ('Duan Yu', 33, 'male', 19000), ('Wang Yuyan' 26, 'female', 9000), ('Mu Wanqing', 23, 'female', 6000), ('Qiao Feng', 38, 'male', 23000), ('A Zhu', 24, 'female', 5000), ('A Zi', 22, 'female', 5500), ('Xizhu', 35, 'male', 11000), ('Menggu', 25, 'female', 6500), (Mei Chaofeng, 41) 'female', 15000), ('Chen Xuanfeng', 44, 'male', 12000), ('Yang Guo', 28, 'male', 24000), ('Xiao Longnu', 38, 'female', 15000), ('Jiu Mozhi', 44, 'male', 16000)) try: # execute sql statement cursor.executemany (sql Data) # submit db.commit () except: # roll back db.rollback () if an error occurs # close the database connection db.close () 4.4. Database query

The Python query MySQL uses fetchone () to get a single piece of data and the fetchall () method to get multiple pieces of data.

Fetchone (): this method gets the next query result set. The result set is an object

Fetchall (): receives all the returned result rows

Rowcount (): this is a read-only property and returns the number of rows affected after the execution of the execute () method.

4.4.1. Query example

Query all data with income (salary) greater than 20000 in employee table

# 1. Query employee table for employees whose salary is greater than 20000 import pymysql# set database configuration item HOSTNAME = 'node05'USERNAME =' root'PASSWORD = 'Love88me'DATABASE =' cayman'# Open database connection db = pymysql.connect (HOSTNAME, USERNAME, PASSWORD DATABASE) # create a flow mark object using cursor object cursor = db.cursor () # query statement sql = "select * from employee where income >'% d'"% (20000) try: # execute sql statement cursor.execute (sql) # get all the lists that meet the criteria ret = cursor.fetchall () # traversal print result for row in ret: user_name = row [ 1] age = row [2] sex = row [3] income = row [4] print (f "employee: {user_name} Age: {age}, gender: {sex}, salary: {income} ") except: print (" No data or query error! ") # close database connection db.close () 4.5. Database update operation import pymysql# sets database configuration item HOSTNAME = 'node05'USERNAME =' root'PASSWORD = 'Love88me'DATABASE =' cayman'# Open database connection db = pymysql.connect (HOSTNAME, USERNAME, PASSWORD DATABASE) # create a flow mark object using cursor object cursor = db.cursor () # Update statement sql = "update employee set income=income+income*0.1 where sex='%c'"% ('female') try: # execute SQL statement cursor.execute (sql) # submit db.commit () except: # Roll back db.rollback () # close database db.close () 4.6. Delete operation import pymysql# set database connection information HOSTNAME = 'node05'USERNAME =' root'PASSWORD = 'Love88me'DATABASE =' cayman'# Open database connection db = pymysql.connect (HOSTNAME, USERNAME, PASSWORD DATABASE) # use the cursor () method to get the cursor cursor = db.cursor () # to build the delete data SQL statement sql = "delete from employee where user_name ='% s'"% ('Jiumozhi') try: # execute sql statement cursor.execute (sql) # commit db.commit () except: # Roll back db.rollback () # close database connection db.close () 4.7 to perform transaction operation when an exception occurs

In database operation, the transaction mechanism can ensure the consistency of data. The most basic transaction should have four properties: atomicity, consistency, isolation, and persistence. These four attributes are called ACID features.

Atomicity: a transaction is an indivisible unit of work, and all the operations included in the transaction are either done or not done.

Consistency: the transaction must be to change the database from one consistency state to another. Consistency is closely related to atomicity.

Isolation: the execution of one transaction cannot be interfered with by other transactions. That is, the operations and the data used within a transaction are isolated from other concurrent transactions, and the concurrent transactions can not interfere with each other.

Durability: persistence, also known as permanence, means that once a transaction is committed, its changes to the data in the database should be permanent. Other operations or failures that follow should not affect it in any way.

Transactions in Python DB API 2.0 provide two methods, commit and rollback. In database programming that supports transactions, an invisible database transaction is automatically opened when the flow mark is established.

After reading this, the article "how to install and configure MySQL database" has been introduced. If you want to master the knowledge points of this article, you still need to practice and use it yourself. If you want to know more about related articles, welcome to follow the industry information channel.

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.