python3 connects to MySQL database and performs basic operations of adding, deleting, modifying and querying database

This is a detailed description of python connecting to the database. If you continue to use python to connect to the database, please see this template article python3 connection MySQL database template
We need to install pymysql when python3 is connected to MySQL database

pip install pymysql

After installation, you can import pymysql directly to a third-party library

Connect to database

conn = pymysql.connect(
	host="localhost",  # Indicates whether host indicates local MySQL or remote
    user="root",  # User name
    password="root",  # Password
    db="db4",  # Database name to connect to
    charset="utf8mb4",  # The specified character set can solve the Chinese disorder
    cursorclass=pymysql.cursors.DictCursor  # Fixed write, similar to the load driver in jdbc
)

Database addition, deletion, modification and query (simple writing method)
After connecting to the database, we need to add, delete, modify and check the database

"""
//check
"""
sql = "select * from user"

cursor = conn.cursor()  # Create a pointer object named cursor
result = cursor.execute(sql)  # Execute the query operation, and the result returned is the number of affected rows

data = cursor.fetchone()  #This method is to get the first data of the database result, which is represented by the primitive ancestor. If not, it is null (represented by None in python)
datas = cursor.fetchall()  # This method gets all the results of the query, which are represented by the ancestor

# Perform data processing, and close the pointer and link after data processing
curosr.close()
conn.close()

'''
//If there are variable parameters in the sql statement, you can do the following
'''
sql = "select * from user where id = %s" % 23  # String processing of python

'''
//However, this processing method is easy to cause sql injection problems
execute()This method actually leaves the position of the parameter, execute(sql Sentence, Parameter location)
//So we can write as follows
'''

sql = "select * from user where id = %s and username = %s"
cursor = conn.cursor()
result = cursor.execute(sql, (12, 'zhangsan'))
# This can effectively prevent sql injection, or we can write as follows

sql = cursor.mogrify(select * from user where id = %s and username=%s, (12, 'zhangsan'))
cursor = conn.cursor()
result = cursor.execute(sql)
"""
//increase
"""
conn = pymysql.connect(
	host="localhost",
    user="root",
    password="root",
    db="db4", 
    charset="utf8mb4",
    cursorclass=pymysql.cursors.DictCursor
)

cursor = conn.cursor()

sql = "insert into user (username, password) values(%s, %s)"

result = cursor.execute(sql, ("zhangsan", "123456"))

conn.commit()  ''' Note that you need to commit the transaction here to insert successfully '''

cursor.close()
conn.close()
'''It is worth mentioning that. pymysql Also provided executemany()This magical method can insert many pieces of data at a time'''
'''As shown below'''

sql = "insert into user (username, password) values(%s, %s)"
params = [('zhanshen','123'), ('lemon', '123'), ('wangsi', '1234')]
result = cursor.executemany(sql, params)
cursor.close()
conn.close()
'''There params It can be a large list, in which there are various Yuanzu parameters, or it can also be a large Yuanzu, in which there are Yuanzu parameters'''
params = cursor.
"""
//change
"""
conn = pymysql.connect(
	host="localhost",
    user="root",
    password="root",
    db="db4", 
    charset="utf8mb4",
    cursorclass=pymysql.cursors.DictCursor
)

cursor = conn.cursor()

sql = "update user set password = %s where username = %s"

result = cursor.execute(sql, ("zhangsan", "123456"))

conn.commit()  

cursor.close()
conn.close()
''' There's nothing special to note here, and insert In the same way, deletion is also a reason(It's funny to be lazy) '''

Now we can use with as to improve the code

conn = pymysql.connect(
	host="localhost",
    user="root",
    password="root",
    db="db4", 
    charset="utf8mb4",
    cursorclass=pymysql.cursors.DictCursor
)

try:
	with conn.cursor() as cursor:
		sql = "select * from user"
		result = cursor.execute(sql)
		print(result)
	
	with conn.cursor() as cursor:
		sql = "insert into user (username, password) values (%s, %s)"
		result = cursor.execute(sql, ('zhanshen', '123'))
		conn.commit()
finally:
	conn.close()
'''There's wood. It's found with as You don't have to close it cursor,because with as It'll shut down for us'''

41 original articles published, praised 15, 4191 visitors
Private letter follow

Tags: SQL Database Python MySQL

Posted on Fri, 07 Feb 2020 11:45:08 -0500 by EZbb