python对数据库增删改查

Python中可以通过许多数据库API来实现对数据库的增删改查操作，其中比较常用的有：

1. MySQLdb：用于连接MySQL数据库的Python库
2. sqlite3：Python自带的轻量级数据库，适用于小型项目
3. psycopg2：用于连接PostgreSQL数据库的Python库

下面分别介绍如何使用这些库进行增删改查操作：

1. MySQLdb：

连接数据库：

import MySQLdb

conn = MySQLdb.connect(host='localhost', user='root', passwd='password', db='database_name')

查询数据：

cursor = conn.cursor()
cursor.execute("SELECT * FROM table_name")
result = cursor.fetchall()

插入数据：

cursor = conn.cursor()
sql = "INSERT INTO table_name (column1, column2, column3) VALUES (%s, %s, %s)"
val = ("value1", "value2", "value3")
cursor.execute(sql, val)
conn.commit()

更新数据：

cursor = conn.cursor()
sql = "UPDATE table_name SET column1 = %s WHERE column2 = %s"
val = ("new_value", "old_value")
cursor.execute(sql, val)
conn.commit()

删除数据：

cursor = conn.cursor()
sql = "DELETE FROM table_name WHERE column1 = %s"
val = ("value_to_delete",)
cursor.execute(sql, val)
conn.commit()

2. sqlite3：

连接数据库：

import sqlite3

conn = sqlite3.connect('database_name.db')

查询数据：

cursor = conn.cursor()
cursor.execute("SELECT * FROM table_name")
result = cursor.fetchall()

插入数据：

cursor = conn.cursor()
sql = "INSERT INTO table_name (column1, column2, column3) VALUES (?, ?, ?)"
val = ("value1", "value2", "value3")
cursor.execute(sql, val)
conn.commit()

更新数据：

cursor = conn.cursor()
sql = "UPDATE table_name SET column1 = ? WHERE column2 = ?"
val = ("new_value", "old_value")
cursor.execute(sql, val)
conn.commit()

删除数据：

cursor = conn.cursor()
sql = "DELETE FROM table_name WHERE column1 = ?"
val = ("value_to_delete",)
cursor.execute(sql, val)
conn.commit()

3. psycopg2：

连接数据库：

import psycopg2

conn = psycopg2.connect(host='localhost', user='postgres', password='password', dbname='database_name')

查询数据：

cursor = conn.cursor()
cursor.execute("SELECT * FROM table_name")
result = cursor.fetchall()

插入数据：

cursor = conn.cursor()
sql = "INSERT INTO table_name (column1, column2, column3) VALUES (%s, %s, %s)"
val = ("value1", "value2", "value3")
cursor.execute(sql, val)
conn.commit()

更新数据：

cursor = conn.cursor()
sql = "UPDATE table_name SET column1 = %s WHERE column2 = %s"
val = ("new_value", "old_value")
cursor.execute(sql, val)
conn.commit()

删除数据：

cursor = conn.cursor()
sql = "DELETE FROM table_name WHERE column1 = %s"
val = ("value_to_delete",)
cursor.execute(sql, val)
conn.commit()