帮我写一个用python和SQLserver的pymssql库建立的图书管理系统,可以实现由读者登录时,可以实现书籍的信息查询,可以进行借书和还书的功能,由管理员登录时,可以实现管理员对书籍信息的更新,并给出SQLserver的代码
时间: 2024-04-08 09:31:06 浏览: 28
好的,下面是使用Python和pymssql库建立的图书管理系统的示例代码:
Python代码:
```python
import pymssql
# 连接数据库
conn = pymssql.connect(server='localhost', user='username', password='password', database='library')
def reader_login(reader_id):
"""
读者登录
"""
# 实现读者登录逻辑,比如验证读者ID和密码等
# ...
pass
def admin_login(admin_id):
"""
管理员登录
"""
# 实现管理员登录逻辑,比如验证管理员ID和密码等
# ...
pass
def search_books(keyword):
"""
根据关键字查询书籍信息
"""
with conn.cursor(as_dict=True) as cursor:
query = f"SELECT * FROM books WHERE title LIKE '%{keyword}%' OR author LIKE '%{keyword}%'"
cursor.execute(query)
result = cursor.fetchall()
return result
def borrow_book(book_id, reader_id):
"""
借书功能
"""
with conn.cursor() as cursor:
# 检查书籍是否可借
check_query = f"SELECT * FROM books WHERE book_id = {book_id} AND status = 'available'"
cursor.execute(check_query)
book = cursor.fetchone()
if book:
# 更新借阅信息
update_query = f"UPDATE books SET status = 'borrowed', reader_id = {reader_id} WHERE book_id = {book_id}"
cursor.execute(update_query)
conn.commit()
return "借书成功"
else:
return "该书籍不可借"
def return_book(book_id):
"""
还书功能
"""
with conn.cursor() as cursor:
# 检查书籍是否已借出
check_query = f"SELECT * FROM books WHERE book_id = {book_id} AND status = 'borrowed'"
cursor.execute(check_query)
book = cursor.fetchone()
if book:
# 更新还书信息
update_query = f"UPDATE books SET status = 'available', reader_id = NULL WHERE book_id = {book_id}"
cursor.execute(update_query)
conn.commit()
return "还书成功"
else:
return "该书籍未借出"
def update_book_info(book_id, title, author):
"""
更新书籍信息
"""
with conn.cursor() as cursor:
update_query = f"UPDATE books SET title = '{title}', author = '{author}' WHERE book_id = {book_id}"
cursor.execute(update_query)
conn.commit()
return "书籍信息更新成功"
# 关闭数据库连接
conn.close()
```
SQL Server数据库表结构:
```sql
CREATE TABLE books (
book_id INT PRIMARY KEY,
title VARCHAR(100),
author VARCHAR(100),
status VARCHAR(20) DEFAULT 'available',
reader_id INT NULL
)
CREATE TABLE readers (
reader_id INT PRIMARY KEY,
name VARCHAR(50)
)
CREATE TABLE admins (
admin_id INT PRIMARY KEY,
name VARCHAR(50)
)
```
请注意,上述代码是一个简化的示例,实际应用中可能需要更多的错误处理和安全性措施。此外,你需要根据实际情况修改数据库连接参数和执行的SQL查询语句。
希望以上代码能帮到你!如果有任何问题,请随时提问。