要使用MySQL库进行事务管理,请遵循以下步骤:
mysql-connector-python
库。要安装它,请在命令行中运行以下命令:pip install mysql-connector-python
import mysql.connector
from mysql.connector import Error
def create_conn():
try:
conn = mysql.connector.connect(
host='your_host',
user='your_username',
password='your_password',
database='your_database'
)
if conn.is_connected():
print("Connection to MySQL database was successful!")
return conn
except Error as e:
print(f"Error: {e}")
return None
def execute_query(conn, query):
try:
cursor = conn.cursor()
cursor.execute(query)
# 提交事务
conn.commit()
print("Query executed successfully!")
except Error as e:
# 发生错误时回滚事务
conn.rollback()
print(f"Error: {e}")
def main():
conn = create_conn()
if conn is not None:
try:
# 创建表
create_table_query = """CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE
);"""
execute_query(conn, create_table_query)
# 插入数据
insert_query = "INSERT INTO users (name, email) VALUES (%s, %s);"
data = ("John Doe", "john.doe@example.com")
execute_query(conn, insert_query, data)
except Error as e:
print(f"Error: {e}")
finally:
if conn.is_connected():
conn.close()
print("Connection to MySQL database was closed.")
if __name__ == "__main__":
main()
在这个例子中,我们首先创建了一个连接到MySQL数据库的函数create_conn()
。然后,我们创建了一个execute_query()
函数,该函数执行SQL查询并使用事务处理。在main()
函数中,我们执行了一组需要事务支持的SQL查询,如创建表和插入数据。如果在执行过程中发生错误,事务将回滚以确保数据的一致性。