图书借阅系统数据访问
时间: 2023-12-11 17:03:42 浏览: 19
为了实现图书借阅系统的数据访问,我们需要进行以下步骤:
1.设计数据库表结构,包括图书信息表、用户信息表、借阅记录表等。
```sql
CREATE TABLE book_info (
book_id INT PRIMARY KEY AUTO_INCREMENT,
book_name VARCHAR(50) NOT NULL,
author VARCHAR(50) NOT NULL,
publisher VARCHAR(50) NOT NULL,
publish_date DATE NOT NULL,
price FLOAT NOT NULL,
total INT NOT NULL,
remain INT NOT NULL
);
CREATE TABLE user_info (
user_id INT PRIMARY KEY AUTO_INCREMENT,
user_name VARCHAR(50) NOT NULL,
password VARCHAR(50) NOT NULL, email VARCHAR(50) NOT NULL,
phone VARCHAR(20) NOT NULL
);
CREATE TABLE borrow_record (
record_id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
book_id INT NOT NULL,
borrow_date DATE NOT NULL,
return_date DATE NOT NULL,
FOREIGN KEY (user_id) REFERENCES user_info(user_id),
FOREIGN KEY (book_id) REFERENCES book_info(book_id)
);
```
2.连接数据库,使用Python中的MySQLdb模块或者pymysql模块等进行连接。
```python
import pymysql
# 打开数据库连接
db = pymysql.connect(host="localhost", user="root", password="123456", database="library")
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
# 执行 SQL 查询
cursor.execute("SELECT * FROM book_info")
# 获取所有记录列表
results = cursor.fetchall()
# 输出查询结果
for row in results:
book_id = row[0]
book_name = row[1]
author = row[2]
publisher = row[3]
publish_date = row[4]
price = row[5]
total = row[6]
remain = row[7]
print("book_id=%d,book_name=%s,author=%s,publisher=%s,publish_date=%s,price=%f,total=%d,remain=%d" % (book_id, book_name, author, publisher, publish_date, price, total, remain))
# 关闭数据库连接
db.close()
```
3.实现数据的增删改查操作,例如查询图书信息。
```python
def query_book_info(book_name):
# 打开数据库连接
db = pymysql.connect(host="localhost", user="root", password="123456", database="library")
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
# 执行 SQL 查询
sql = "SELECT * FROM book_info WHERE book_name='%s'" % book_name
cursor.execute(sql)
# 获取所有记录列表
results = cursor.fetchall()
# 输出查询结果
for row in results:
book_id = row[0]
book_name = row[1]
author = row[2]
publisher = row[3]
publish_date = row[4]
price = row[5]
total = row[6]
remain = row[7]
print("book_id=%d,book_name=%s,author=%s,publisher=%s,publish_date=%s,price=%f,total=%d,remain=%d" % (book_id, book_name, author, publisher, publish_date, price, total, remain))
# 关闭数据库连接
db.close()
```
相关推荐
![doc](https://img-home.csdnimg.cn/images/20210720083327.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)