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

Today, I would like to share with you the relevant knowledge of how to use PyMySQL. The content is detailed and the logic is clear. I believe most people still know too much about this knowledge, so share this article for your reference. I hope you can get something after reading this article. Let's take a look at it.

When we use MySQL, we can manipulate the tables in the database in the client terminal of MySQL. At the same time, we can also use visual tools such as navicat to manipulate data tables. However, this is just manipulating individual data, and if we want to insert 100000 pieces of data, we certainly can't do that. We can write a loop to insert automatically, so PyMySQL is an interface that uses Python to directly manipulate the database.

Use case for PyMySQL:

1 query the information of the tables in the database:

# requirements: query the information of info table in database person # 1. Guide package import pymysqltry: # 2. The service to connect to the MySQL database connc = pymysql.Connect (user= "root", # The first four arguments is based on DB-API 2.0 recommendation. Password= "4412", host='127.0.0.1', # mysql server IP, default is 127.0.0.1/localhost, or write real ip database='person', port=3306, charset= "utf8") # 3. Create a cursor object cur = connc.cursor () # 4. Write the SQL statement sql = 'select * from info;' # 5. Use the cursor object to call SQL cur.execute (sql) # 6. Get the query result result= cur.fetchall () print (result) # 7. Close the cursor object cur.close () # 8. Close the connection connc.close () except Exception as e: print (e)

2 add data:

Most of the steps are the same as before, annotated directly in the program:

# demand: # add data Liu Dehua 56 male data to database person-- info table # modify data Xiao Wang's name to database person-- info table # delete data Zhang San database person-- info table # 1. Guide package import pymysql# 2. Connect to the MySQL service connc = pymysql.Connect (user= "root", # The first four arguments is based on DB-API 2.0 recommendation. Password= "4412", host='127.0.0.1', # mysql server IP, default is 127.0.0.1/localhost, or write real ip database='person', port=3306, charset= "utf8") # 3. Create a cursor object cur = connc.cursor () try: # 4. Write, add, delete SQL statements # add data Andy Lau 56 male sql = 'insert into info values (% s,% s)' add_data = [0, "Andy Lau", 56, "male"] # 5. Use cursor objects to execute the SQL statement cur.execute (sql, add_data) # 6. Submit operation connc.commit () except Exception as e: print (e) # operation failed, data rollback connc.rollback () finally: # 7. Close the cursor object cur.close () # 8. Close the connection connc.close () print ("over!")

3 modify data:

# demand: # add data Liu Dehua 56 male data to database person-- info table # modify data Xiao Wang's name to database person-- info table # delete data Zhang San database person-- info table # 1. Guide package import pymysql# 2. Connect to the MySQL service connc = pymysql.Connect (user= "root", # The first four arguments is based on DB-API 2.0 recommendation. Password= "4412", host='127.0.0.1', # mysql server IP, default is 127.0.0.1/localhost, or write real ip database='person', port=3306, charset= "utf8") # 3. Create a cursor object cur = connc.cursor () try: # 4. Write, add, delete SQL statements # modify data Li Si's father sql = 'update info set name=%s where name= "Li Si"' update_data = ["Li Si's father"] # 5. Use cursor objects to execute the SQL statement cur.execute (sql, update_data) # 6. Submit operation connc.commit () except Exception as e: print (e) # operation failed, data rollback connc.rollback () finally: # 7. Close the cursor object cur.close () # 8. Close the connection connc.close () print ("over!")

4 Delete data:

# demand: # add data Liu Dehua 56 male data to database person-- info table # modify data Xiao Wang's name to database person-- info table # delete data Zhang San database person-- info table # 1. Guide package import pymysql# 2. Connect to the MySQL service connc = pymysql.Connect (user= "root", # The first four arguments is based on DB-API 2.0 recommendation. Password= "4412", host='127.0.0.1', # mysql server IP, default is 127.0.0.1/localhost, or write real ip database='person', port=3306, charset= "utf8") # 3. Create a cursor object cur = connc.cursor () try: # 4. Write, add, delete SQL statements # modify data Li Si's father sql = 'update info set name=%s where name= "Li Si"' update_data = ["Li Si's father"] # 5. Use cursor objects to execute the SQL statement cur.execute (sql, update_data) # 6. Submit operation connc.commit () except Exception as e: print (e) # operation failed, data rollback connc.rollback () finally: # 7. Close the cursor object cur.close () # 8. Close the connection connc.close () print ("over!") These are all the contents of the article "how to use PyMySQL". Thank you for reading! I believe you will gain a lot after reading this article. The editor will update different knowledge for you every day. If you want to learn more knowledge, please pay attention to 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.