How to use Python to operate various functions of MySQL

Connecting to MySQL

In Python, we can use the pymysql library to connect to the MySQL database.

import pymysql

# 连接MySQL
conn = pymysql.connect(host='localhost', user='root', password='123456', database='test', charset='utf8')

# 获取游标
cursor = conn.cursor()

# 执行SQL语句
cursor.execute('SELECT * FROM users')

# 获取结果集
result = cursor.fetchall()
print(result)

# 关闭游标和连接
cursor.close()
conn.close()

In the above code, we first used the pymysql library to connect to the MySQL database and obtain the cursor. Then, we executed a simple SELECT statement and obtained the result set. Finally, we close the cursor and connection.

CRUD

In MySQL, we can use INSERT, DELETE, UPDATE and SELECT statement to complete the add, delete, modify and check operations. In Python, we can also use the pymysql library to perform these operations.

Insert data

import pymysql

# 连接MySQL
conn = pymysql.connect(host='localhost', user='root', password='123456', database='test', charset='utf8')

# 获取游标
cursor = conn.cursor()

# 插入数据
sql = "INSERT INTO users(username, password) VALUES (%s, %s)"
params = ('Tom', '123456')
cursor.execute(sql, params)

# 提交事务
conn.commit()

# 关闭游标和连接
cursor.close()
conn.close()

In the above code, we use the INSERT statement to insert a piece of data into the users table. When executing the execute method, we can use the placeholder %s to represent parameters, and then pass in the corresponding parameters during execution. Finally, we commit the transaction and close the cursor and connection.

Delete data

import pymysql

# 连接MySQL
conn = pymysql.connect(host='localhost', user='root', password='123456', database='test', charset='utf8')

# 获取游标
cursor = conn.cursor()

# 删除数据
sql = "DELETE FROM users WHERE id = %s"
params = (1,)
cursor.execute(sql, params)

# 提交事务
conn.commit()

# 关闭游标和连接
cursor.close()
conn.close()

In the above code, we use the DELETE statement to delete the id of 1 in the users table data. When executing the execute method, we also use the placeholder %s to represent the parameters. Finally, we commit the transaction and close the cursor and connection.

Update data

import pymysql

# 连接MySQL
conn = pymysql.connect(host='localhost', user='root', password='123456', database='test', charset='utf8')

# 获取游标
cursor = conn.cursor()

# 更新数据
sql = "UPDATE users SET password = %s WHERE username = %s"
params = ('654321', 'Tom')
cursor.execute(sql, params)

# 提交事务
conn.commit()

# 关闭游标和连接
cursor.close()
conn.close()

In the above code, we use the UPDATE statement to update the username in the users table to ## The password for #Tom’s data. When executing the execute method, we also use the placeholder %s to represent the parameters. Finally, we commit the transaction and close the cursor and connection.

Query data

import pymysql

# 连接MySQL
conn = pymysql.connect(host='localhost', user='root', password='123456', database='test', charset='utf8')

# 获取游标
cursor = conn.cursor()

# 查询数据
sql = "SELECT * FROM users WHERE username = %s"
params = ('Tom',)
cursor.execute(sql, params)

# 获取结果集
result = cursor.fetchall()
print(result)

# 关闭游标和连接
cursor.close()
conn.close()

In the above code, we use the

SELECT statement to query the username in the users table for Tom's data. When executing the execute method, we also use the placeholder %s to represent the parameters. Finally, we retrieve the result set and close the cursor and connection.

Batch operation

In MySQL, we can use

INSERT, DELETE, UPDATE and SELECT statement to operate data in batches. In Python, we can also use the pymysql library to operate data in batches.

Batch Insert Data

import pymysql

# 连接MySQL
conn = pymysql.connect(host='localhost', user='root', password='123456', database='test', charset='utf8')

# 获取游标
cursor = conn.cursor()

# 批量插入数据
sql = "INSERT INTO users(username, password) VALUES (%s, %s)"
params = [('Tom', '123456'), ('Jerry', '654321'), ('Alice', '111111')]
cursor.executemany(sql, params)

# 提交事务
conn.commit()

# 关闭游标和连接
cursor.close()
conn.close()

In the above code, we use the

executemany method to batch insert multiple pieces of data. When executing the executemany method, we use a tuple list to represent multiple parameters. Finally, we commit the transaction and close the cursor and connection.

Delete data in batches

import pymysql

# 连接MySQL
conn = pymysql.connect(host='localhost', user='root', password='123456', database='test', charset='utf8')

# 获取游标
cursor = conn.cursor()

# 批量删除数据
sql = "DELETE FROM users WHERE id = %s"
params = [(1,), (2,), (3,)]
cursor.executemany(sql, params)

# 提交事务
conn.commit()

# 关闭游标和连接
cursor.close()
conn.close()

In the above code, we use the

executemany method to delete multiple pieces of data in batches. When executing the executemany method, we also use a tuple list to represent multiple parameters. Finally, we commit the transaction and close the cursor and connection.

Batch update data

import pymysql

# 连接MySQL
conn = pymysql.connect(host='localhost', user='root', password='123456', database='test', charset='utf8')

# 获取游标
cursor = conn.cursor()

# 批量更新数据
sql = "UPDATE users SET password = %s WHERE username = %s"
params = [('123456', 'Tom'), ('654321', 'Jerry'), ('111111', 'Alice')]
cursor.executemany(sql, params)

# 提交事务
conn.commit()

# 关闭游标和连接
cursor.close()
conn.close()

The above is the detailed content of How to use Python to operate various functions of MySQL. For more information, please follow other related articles on the PHP Chinese website!