python 数据库封装完整 案例
时间: 2023-10-13 07:12:24 浏览: 34
以下是一个使用Python封装数据库的完整案例,该案例使用了SQLite3数据库。
1. 创建数据库连接
```python
import sqlite3
class Database:
def __init__(self, db_name):
self.db_name = db_name
self.conn = None
self.cursor = None
def connect(self):
self.conn = sqlite3.connect(self.db_name)
self.cursor = self.conn.cursor()
def disconnect(self):
self.conn.close()
db = Database('MyDatabase.db')
db.connect()
```
2. 创建表格
```python
class Table:
def __init__(self, db, table_name, columns):
self.db = db
self.table_name = table_name
self.columns = columns
def create(self):
self.db.cursor.execute(f"CREATE TABLE IF NOT EXISTS {self.table_name} ({self.columns})")
self.db.conn.commit()
users_table = Table(db, 'users', 'id INTEGER PRIMARY KEY, name TEXT, email TEXT')
users_table.create()
```
3. 插入数据
```python
class User:
def __init__(self, db, table_name):
self.db = db
self.table_name = table_name
def insert(self, data):
columns = ', '.join(data.keys())
values = ', '.join(['?' for _ in range(len(data))])
sql = f"INSERT INTO {self.table_name} ({columns}) VALUES ({values})"
self.db.cursor.execute(sql, tuple(data.values()))
self.db.conn.commit()
user = User(db, 'users')
user.insert({'name': 'John Doe', 'email': 'johndoe@example.com'})
```
4. 查询数据
```python
class Query:
def __init__(self, db, table_name):
self.db = db
self.table_name = table_name
def select_all(self):
sql = f"SELECT * FROM {self.table_name}"
self.db.cursor.execute(sql)
return self.db.cursor.fetchall()
query = Query(db, 'users')
users = query.select_all()
print(users)
```
5. 更新数据
```python
class Update:
def __init__(self, db, table_name):
self.db = db
self.table_name = table_name
def update(self, data, where=None):
set_clause = ', '.join([f"{k} = ?" for k in data.keys()])
where_clause = ''
if where:
where_clause = f" WHERE {where}"
sql = f"UPDATE {self.table_name} SET {set_clause}{where_clause}"
self.db.cursor.execute(sql, tuple(data.values()))
self.db.conn.commit()
update = Update(db, 'users')
update.update({'email': 'janedoe@example.com'}, where="name='Jane Doe'")
```
6. 删除数据
```python
class Delete:
def __init__(self, db, table_name):
self.db = db
self.table_name = table_name
def delete(self, where=None):
where_clause = ''
if where:
where_clause = f" WHERE {where}"
sql = f"DELETE FROM {self.table_name}{where_clause}"
self.db.cursor.execute(sql)
self.db.conn.commit()
delete = Delete(db, 'users')
delete.delete(where="name='John Doe'")
```
7. 关闭数据库连接
```python
db.disconnect()
```