pymysql It's a Python Third party package for , Manual installation is required before use
'''
pymysql Basic use steps
Use pymysql Query data
'''
import pymysql
# Create database connection object , Except for the port , All other types should use string type
connect_db=pymysql.connect(host='localhost',port=3306,user='root',password='root',charset='utf8',database='jing_dong')
# Get cursor object , Cursor objects are used to operate the database , Similar to the cursor when a computer writes a document , It can indicate the starting position of the operation
# A database connection object can create multiple （ many times ） Cursor object , Generally, only one will be created for operation at the same time
# Cursor objects are created , An implicit transaction environment is enabled by default
cur=connect_db.cursor()
# Operating the database
# Avoid string quotes and sql Quotation mark conflict in statement , Use three quotes
sql_str='''select * from goods'''
# perform sql sentence , The number of data rows of the operation will be returned
row_count=cur.execute(sql_str)
print(f' Query to {row_count} Bar record ')
# The return value of the query data statement is an ancestor , Or Yuanzu taoyuanzu
# Start at the position indicated by the cursor object , Get a query result
result_one=cur.fetchone() # Return to a ancestor
print(result_one)
print('*'*30)
# Return the cursor to the beginning
cur.rownumber=0
# Start at the position indicated by the cursor object , Get the specified number of records
result_many=cur.fetchmany(7) # Returns an iteratable object of primitive ancestor type , A piece of data is like this ((),)
for result in result_many:
print(result)
print('*'*30)
# Return the cursor to the beginning
cur.rownumber=0
# Get all the data
result_all=cur.fetchall() # Returns an iteratable object of primitive ancestor type , A piece of data is like this ((),)
for result in result_all:
print(result)
# Close cursor object
cur.close()
# Close database objects
connect_db.close()
'''
insert data
'''
import pymysql
# Connect = connect = Connection = connections.Connection
connect_db=pymysql.Connect(host='localhost',port=3306,database='jing_dong',user='root',password='root',charset='utf8')
cur=connect_db.cursor()
sql_str='''insert into goods(name,cate_id,brand_id) values('MacBookPro 15',1,5)'''
t=cur.execute(sql_str)
print(t)
'''
When adding, deleting, or modifying a database , By default, it will operate in the transaction environment , After the operation is completed, submit the operation manually , Otherwise, the default operation of the program is rollback , Then the operation just now will not take effect
But when adding data , Even if the rollback , For the field of the primary key auto_increment Value will also be added 1
'''
# The commit operation of the transaction is completed by the database connection object
# The commit operation of the transaction must be performed before sql After the statement
connect_db.commit()
cur.close()
connect_db.close()
'''
Use pymysql Update data
'''
import pymysql
connect_db=pymysql.Connect(host='localhost',port=3306,user='root',password='root',charset='utf8',database='jing_dong')
cur=connect_db.cursor()
sql_str='''update goods set price=2099 where id=10'''
cur.execute(sql_str)
connect_db.commit()
cur.close()
connect_db.close()
'''
Use pymysql Delete data
'''
import pymysql
connect_db=pymysql.Connection(host='localhost',port=3306,user='root',password='root',charset='utf8',database='jing_dong')
cur=connect_db.cursor()
sql_str='''delete from goods where name=' laptop 17' '''
cur.execute(sql_str)
connect_db.commit()
cur.close()
connect_db.close()
