Python accesses MySQL to realize the actual combat record of adding deleting modifying and checking

Preface

We often need to save a large amount of data for later use, and the database is a good solution. Among many databases, MySQL database is relatively simple to get started, syntax is relatively simple, and it is also relatively practical. This article mainly introduces the Python access to MySQL to achieve the related content of adding, deleting, modifying and checking, the following words do not say much, 1 to see the detailed introduction

Open a database connection and create a database and table

The basic syntax is as follows:

execute(query, args=None)
# query Of type string sql Statement 
# args Optional sequence or mapping for query Gets or sets the parameter value of the. 
#  If args Is the sequence, query You must use the %s Make placeholders; 
#  If args For mapping, query You must use the %(key)s Be a placeholder 

Case: Database name learning, table name houses, field name house_location purchasing_year

import pymysql
db = pymysql.connect('localhost', 'root', "password")  #  Open the database connection, password Replace with native database password 
cursor = db.cursor()
cursor.execute('drop database learning;')
cursor.execute('create database learning;')
cursor.execute('use learning')
sql_create = """create table houses (name VARCHAR(100) NOT NULL, house_location VARCHAR(100) NOT NULL, purchasing_year VARCHAR(100) NOT NULL);"""
cursor.execute(sql_create)

Insert

#  Insert 
sql_insert = """insert into houses values(%s,%s,%s);"""
cursor.execute(sql_insert,(' Dream glass ',' South Gate of Heaven ',1995))  #  Insert a single piece of data 
cursor.executemany(sql_insert,[(' Purple English ',' Shu Mountain ',1996),(' Tianhe ',' Stone sinking ',1997),(' Diamond yarn ',' Creek Cave ',1998)])  #  Insert multiple pieces of data 

Query

sql_select = """select * from houses"""
#  Single query 
cursor.execute(sql_select)
while 1:
 result = cursor.fetchone()
 if result is None:
  #  Get all the results 
  break
 print(result)

#  Multiple queries, take 3 Bar data 
cursor.execute(sql_select)
Result = cursor.fetchmany(3)
for res in Result:
 print(res)

#  Multiple queries, take all data 
cursor.execute(sql_select)
Result = cursor.fetchall()
for res in Result:
 print(res)

Update

#  Update 1 Bar data 
sql_update = """update houses set purchasing_year=2000 where name=' Diamond yarn ';"""
cursor.execute(sql_update)
cursor.execute(sql_select)
Result = cursor.fetchall()
for res in Result:
 print(res)
#  Update multiple pieces of data 
sql_update = """update houses set purchasing_year=%s where name=%s;"""
cursor.executemany(sql_update,[(2018,' Dream glass '),(2019,' Purple English ')])
cursor.execute(sql_select)
Result = cursor.fetchall()
for res in Result:
 print(res)
#  Rollback transaction 
db.rollback()
cursor.execute(sql_select)
Result = cursor.fetchall()
for res in Result:
 print(res)

Delete

#  Delete 1 Bar data 
sql_delete = """delete from houses where name=' Dream glass ';"""
cursor.execute(sql_delete)
cursor.execute(sql_select)
Result = cursor.fetchall()
for res in Result:
 print(res)
#  Delete multiple pieces of data 
sql_delete = """delete from houses where name=%s;"""
cursor.executemany(sql_delete,[(' Tianhe '),(' Purple English ')])
cursor.execute(sql_select)
Result = cursor.fetchall()
for res in Result:
 print(res)

Close the cursor and close the database connection

cursor.close()           #  Close the cursor 
db.commit()
db.close()            #  Close the database connection 
print('sql Successful execution ')

Summarize