Python如何连接数据库实现增删改查
时间: 2024-12-14 21:06:21 浏览: 7
学习用python链接mysql对数据库进行增删改查(源码)
Python连接数据库并实现增删改查通常会使用像`sqlite3`, `pymysql`, `psycopg2`(PostgreSQL)这样的数据库适配器库。这里以常用的SQLite为例来解释:
1. **安装库**:首先需要通过pip安装`sqlite3`库,对于其他数据库如MySQL或PostgreSQL,则分别安装对应的库。
```bash
pip install sqlite3
```
2. **连接数据库**:
- SQLite:
```python
import sqlite3
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
```
- MySQL或PostgreSQL:
```python
import pymysql
# 连接MySQL
connection = pymysql.connect(host='localhost', user='username', password='password', db='database_name')
# 或者连接PostgreSQL
from psycopg2 import connect
conn = connect(database="database_name", user="username", password="password", host="localhost")
```
3. **创建表**:
```python
cursor.execute("""
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email VARCHAR(50)
);
""")
conn.commit() # 提交到数据库
```
4. **增删改查操作**:
- 插入数据(INSERT):
```python
cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ('John Doe', 'john@example.com'))
conn.commit()
```
- 查询(SELECT):
```python
cursor.execute("SELECT * FROM users WHERE name=?", ('John Doe',))
result = cursor.fetchall()
for row in result:
print(row)
```
- 更新(UPDATE):
```python
cursor.execute("UPDATE users SET email=? WHERE name=?", ('new_email@example.com', 'John Doe'))
conn.commit()
```
- 删除(DELETE):
```python
cursor.execute("DELETE FROM users WHERE name=?", ('John Doe',))
conn.commit()
```
5. **关闭连接**:
```python
cursor.close()
conn.close()
```
阅读全文