python mysql操作

pip install mysql-connector-python


import mysql.connector

# 配置数据库连接参数
config = {
    'user': 'your_username',
    'password': 'your_password',
    'host': 'your_host',
    'database': 'your_database',
    'raise_on_warnings': True
}

# 建立连接
try:
    cnx = mysql.connector.connect(**config)
    print("Connection established")
except mysql.connector.Error as err:
    print(f"Error: {err}")

# 创建游标对象
cursor = cnx.cursor()


# 执行查询
query = ("SELECT * FROM your_table WHERE some_column = %s")
val = ("some_value",)
cursor.execute(query, val)

# 获取所有结果
results = cursor.fetchall()
for result in results:
    print(result)
    
# 关闭游标和连接
cursor.close()
cnx.close()

# 插入数据
insert_query = "INSERT INTO your_table (column1, column2) VALUES (%s, %s)"
insert_values = (value1, value2)

try:
    cursor.execute(insert_query, insert_values)
    cnx.commit()  # 提交事务
except mysql.connector.Error as err:
    print(f"Error: {err}")
    cnx.rollback()  # 发生错误时回滚

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


# 更新数据
update_query = "UPDATE your_table SET column1 = %s WHERE column2 = %s"
update_values = (new_value1, condition_value2)

for result in results:
    line += 1
    if result[1] == 0:
        update_query = f"UPDATE d_user SET uid = {line + 10000}, usericon = '{female}' WHERE uid = {result[0]} "
        cursor.execute(update_query)
        female += 1
        if female > 6:
            female = 1
    else:
        update_query = f"UPDATE d_user SET uid = {line + 10000}, usericon = '{male}'  WHERE uid = {result[0]} "
        cursor.execute(update_query)
        male += 1
        if male > 11:
            male = 7
cnx.commit()

try:
    cursor.execute(update_query, update_values)
    cnx.commit()
except mysql.connector.Error as err:
    print(f"Error: {err}")
    cnx.rollback()

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


# 删除数据
delete_query = "DELETE FROM your_table WHERE column1 = %s"
delete_values = (value_to_delete,)

try:
    cursor.execute(delete_query, delete_values)
    cnx.commit()
except mysql.connector.Error as err:
    print(f"Error: {err}")
    cnx.rollback()

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