mysql Connect to database
import pymysql
try: # Capture exception , Prevent abnormal interruption
# db = pymysql.connect()
db = pymysql.connect(host='localhost', # Host name
user='root', # user name
password='123456', # password
database='mysqlpython1')# database
print(' Database connection successful ')
except pymysql.Error as e: # Capture exception
print(" Database connection failed ："+str(e))
Create table
import pymysql
try: # Capture exception , Prevent abnormal interruption
# db = pymysql.connect()
db = pymysql.connect(host='localhost', # Host name
user='root', # user name
password='123456', # password
database='mysqlpython1')# database
print(' Database connection successful ')
cur = db.cursor() # Create a cursor
cur.execute('DROP TABLE IF EXISTS Student')# Before creating a table, check whether it exists , Delete... If it exists
#Student Is the name of the table to be checked
sql = "CREATE TABLE Student(Name CHAR(20) NOT NULL ,Grade CHAR(20),StudentId char(20) )"
# Field Character type Not empty
cur.execute(sql)
print(' Table created successfully ')
except pymysql.Error as e: # Capture exception
print(" Table creation failed ："+str(e))
Insert data into the table
'''mysql Connect to database '''
import pymysql
try: # Capture exception , Prevent abnormal interruption
# db = pymysql.connect()
db = pymysql.connect(host='localhost', # Host name
user='root', # user name
password='123456', # password
database='mysqlpython1') # database
print(' Database connection successful ')
cur = db.cursor() # Create a cursor
sql = " INSERT INTO Student (Name,Grade,StudentId) VALUE (%s,%s,%s) "
# Table name Field Insert content
value = (' Jinchuangxiang ', ' Sophomore in software engineering ', 542013460716)
cur.execute(sql,value)
db.commit() # Database commit Finally, submit
print(' Insert the success ')
except pymysql.Error as e: # Capture exception
print(" Insert the failure ：" + str(e))
db.rollback() # Database recovery
db.close() # Database shutdown
Query the data in the table
'''mysql Connect to database '''
import pymysql
try: # Capture exception , Prevent abnormal interruption
# db = pymysql.connect()
db = pymysql.connect(host='localhost', # Host name
user='root', # user name
password='123456', # password
database='mysqlpython1') # database
print(' Database connection successful ')
cur = db.cursor() # Create a cursor
sql = "SELECT * FROM Student"
# Table name
cur.execute(sql)
results = cur.fetchall()
for row in results:
Name = row[0] # Creating a tuple
Grade = row[1]
StudentId = row[2]
print('Name:%s,Grade:%s,StudentId:%s' % (Name,Grade,StudentId))
except pymysql.Error as e: # Capture exception
print(" The query fails ：" + str(e))
db.close() # Database shutdown
Update the data in the table
'''mysql Connect to database '''
import pymysql
try: # Capture exception , Prevent abnormal interruption
# db = pymysql.connect()
db = pymysql.connect(host='localhost', # Host name
user='root', # user name
password='123456', # password
database='mysqlpython1') # database
print(' Database connection successful ')
cur = db.cursor() # Create a cursor
sql = "UPDATE Student SET StudentId= %s WHERE StudentId=%s"
# Table name Field name New content Old content
value = ('542013460716', '54201')
cur.execute(sql,value)
db.commit()
print(' The update is successful ')
except pymysql.Error as e: # Capture exception
print(" Update failed ：" + str(e))
db.rollback()
db.close() # Database shutdown
Delete data in table
'''mysql Connect to database '''
import pymysql
try: # Capture exception , Prevent abnormal interruption
# db = pymysql.connect()
db = pymysql.connect(host='localhost', # Host name
user='root', # user name
password='123456', # password
database='mysqlpython1') # database
print(' Database connection successful ')
cur = db.cursor() # Create a cursor
sql = "DELETE FROM Student where Name=%s"
value = (' Jin ')
cur.execute(sql,value)
db.commit()
print(' Delete successful ')
except pymysql.Error as e: # Capture exception
print(" Delete failed ：" + str(e))
db.rollback()
db.close() # Database shutdown
Delete table
'''mysql Connect to database '''
import pymysql
try: # Capture exception , Prevent abnormal interruption
# db = pymysql.connect()
db = pymysql.connect(host='localhost', # Host name
user='root', # user name
password='123456', # password
database='mysqlpython1') # database
print(' Database connection successful ')
cur = db.cursor() # Create a cursor
sql = 'DROP TABLE IF EXISTS Student'
# Table name
cur.execute(sql)
db.commit()
print(' Delete successful ')
except pymysql.Error as e: # Capture exception
print(" Delete failed ：" + str(e))
db.rollback()
db.close() # Database shutdown
Reference material ：
Python3 MySQL Database connection – PyMySQL drive | Novice tutorial (runoob.com)
(7 Bar message ) Python Connect MySQL database _Python The top of the stack -CSDN Blog _python Connect mysql database
Python Connect MySQL database 【 Interesting learning Python】_ Bili, Bili _bilibili
