1. 数据管理和查询
居民信息管理系统
import mysql.connector
from tabulate import tabulate
def create_connection(host_name, user_name, user_password, db_name):
connection = None
try:
connection = mysql.connector.connect(
host=host_name,
user=user_name,
passwd=user_password,
database=db_name
)
print("Connection to MySQL DB successful")
except Error as e:
print(f"The error '{e}' occurred")
return connection
def execute_query(connection, query):
cursor = connection.cursor()
try:
cursor.execute(query)
connection.commit()
print("Query executed successfully")
except Error as e:
print(f"The error '{e}' occurred")
def read_query(connection, query):
cursor = connection.cursor()
result = None
try:
cursor.execute(query)
result = cursor.fetchall()
return result
except Error as e:
print(f"The error '{e}' occurred")
# 连接到数据库
connection = create_connection("localhost", "root", "yourpassword", "property_management")
# 查询所有居民信息
def get_all_residents():
query = "SELECT * FROM residents"
results = read_query(connection, query)
print(tabulate(results, headers=['ID', 'Name', 'Apartment', 'Phone'], tablefmt='grid'))
# 添加新居民
def add_resident(name, apartment, phone):
query = f"INSERT INTO residents (name, apartment, phone) VALUES ('{name}', '{apartment}', '{phone}')"
execute_query(connection, query)
# 删除居民
def delete_resident(id):
query = f"DELETE FROM residents WHERE id = {id}"
execute_query(connection, query)
# 更新居民信息
def update_resident(id, name, apartment, phone):
query = f"UPDATE residents SET name = '{name}', apartment = '{apartment}', phone = '{phone}' WHERE id = {id}"
execute_query(connection, query)
# 示例调用
get_all_residents()
add_resident('张三', 'A栋101', '123456789')
update_resident(1, '李四', 'B栋202', '987654321')
delete_resident(2)2. 自动化任务
定期发送缴费提醒
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
import datetime
# 发送邮件
def send_email(subject, body, to_email):
from_email = "your_email@example.com"
from_password = "your_password"
msg = MIMEMultipart()
msg['From'] = from_email
msg['To'] = to_email
msg['Subject'] = subject
msg.attach(MIMEText(body, 'plain'))
server = smtplib.SMTP('smtp.example.com', 587)
server.starttls()
server.login(from_email, from_password)
text = msg.as_string()
server.sendmail(from_email, to_email, text)
server.quit()
# 获取需要发送提醒的居民
def get_residents_for_reminder():
query = "SELECT * FROM residents WHERE last_payment < DATE_SUB(CURDATE(), INTERVAL 1 MONTH)"
results = read_query(connection, query)
return results
# 发送缴费提醒
def send_payment_reminders():
residents = get_residents_for_reminder()
for resident in residents:
name = resident[1]
email = resident[4] # 假设email在residents表中的第5列
subject = "缴费提醒"
body = f"尊敬的{name}，您的物业管理费已到期，请尽快缴纳。"
send_email(subject, body, email)
# 每月定期运行
send_payment_reminders()3. 报表生成
生成月度费用报表
import pandas as pd
def generate_monthly_report():
query = """
SELECT
r.name,
r.apartment,
p.amount,
p.payment_date
FROM
residents r
JOIN
payments p ON r.id = p.resident_id
WHERE
p.payment_date >= DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, '%Y-%m-01') AND
p.payment_date < DATE_FORMAT(CURDATE(), '%Y-%m-01')
"""
results = read_query(connection, query)
df = pd.DataFrame(results, columns=['Name', 'Apartment', 'Amount', 'Payment Date'])
report_path = f"monthly_report_{datetime.datetime.now().strftime('%Y%m')}.xlsx"
df.to_excel(report_path, index=False)
print(f"Report generated: {report_path}")
# 生成月度报表
generate_monthly_report()4. 通知系统
紧急通知系统
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
def send_emergency_notification(subject, body, to_emails):
from_email = "your_email@example.com"
from_password = "your_password"
msg = MIMEMultipart()
msg['From'] = from_email
msg['To'] = ', '.join(to_emails)
msg['Subject'] = subject
msg.attach(MIMEText(body, 'plain'))
server = smtplib.SMTP('smtp.example.com', 587)
server.starttls()
server.login(from_email, from_password)
text = msg.as_string()
server.sendmail(from_email, to_emails, text)
server.quit()
# 获取所有居民的邮箱
def get_all_emails():
query = "SELECT email FROM residents"
results = read_query(connection, query)
emails = [row[0] for row in results if row[0]]
return emails
# 发送紧急通知
def send_emergency_alert():
subject = "紧急通知：消防演习"
body = "尊敬的居民，今天下午将进行消防演习，请大家注意安全。"
emails = get_all_emails()
send_emergency_notification(subject, body, emails)
# 发送紧急通知
send_emergency_alert()5. 维修请求管理系统
提交维修请求
def add_maintenance_request(resident_id, description):
query = f"INSERT INTO maintenance_requests (resident_id, description, status) VALUES ({resident_id}, '{description}', 'Pending')"
execute_query(connection, query)
# 提交维修请求
add_maintenance_request(1, "卫生间漏水")
5.2 查看未处理的维修请求
def get_pending_requests():
query = "SELECT * FROM maintenance_requests WHERE status = 'Pending'"
results = read_query(connection, query)
print(tabulate(results, headers=['ID', 'Resident ID', 'Description', 'Status', 'Created At'], tablefmt='grid'))
# 查看未处理的维修请求
get_pending_requests()6. 访客管理系统
记录访客
def log_visitor(name, contact, purpose, resident_id):
query = f"INSERT INTO visitors (name, contact, purpose, resident_id, entry_time) VALUES ('{name}', '{contact}', '{purpose}', {resident_id}, NOW())"
execute_query(connection, query)
# 记录访客
log_visitor('王五', '123456789', '拜访朋友', 1)
6.2 查看最近的访客记录
def get_recent_visitors():
query = "SELECT * FROM visitors ORDER BY entry_time DESC LIMIT 10"
results = read_query(connection, query)
print(tabulate(results, headers=['ID', 'Name', 'Contact', 'Purpose', 'Resident ID', 'Entry Time'], tablefmt='grid'))
# 查看最近的访客记录
get_recent_visitors()7. 设施预约系统
预约设施
def book_facility(resident_id, facility_id, start_time, end_time):
query = f"INSERT INTO facility_bookings (resident_id, facility_id, start_time, end_time) VALUES ({resident_id}, {facility_id}, '{start_time}', '{end_time}')"
execute_query(connection, query)
# 预约设施
book_facility(1, 1, '2023-10-15 10:00:00', '2023-10-15 12:00:00')
7.2 查看设施预订情况
def get_facility_bookings(facility_id):
query = f"SELECT * FROM facility_bookings WHERE facility_id = {facility_id} ORDER BY start_time"
results = read_query(connection, query)
print(tabulate(results, headers=['ID', 'Resident ID', 'Facility ID', 'Start Time', 'End Time'], tablefmt='grid'))
# 查看设施预订情况
get_facility_bookings(1)8. 停车管理系统
8.1 记录车辆进出
def log_vehicle_entry_exit(vehicle_id, action, time=None):
if not time:
time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
query = f"INSERT INTO vehicle_logs (vehicle_id, action, time) VALUES ({vehicle_id}, '{action}', '{time}')"
execute_query(connection, query)
# 记录车辆进入
log_vehicle_entry_exit(1, 'entry')
# 记录车辆离开
log_vehicle_entry_exit(1, 'exit')8.2 查看车辆进出记录
def get_vehicle_logs(vehicle_id):
query = f"SELECT * FROM vehicle_logs WHERE vehicle_id = {vehicle_id} ORDER BY time"
results = read_query(connection, query)
print(tabulate(results, headers=['ID', 'Vehicle ID', 'Action', 'Time'], tablefmt='grid'))
# 查看车辆进出记录
get_vehicle_logs(1)9. 公告发布系统
9.1 发布公告
def post_announcement(title, content):
query = f"INSERT INTO announcements (title, content, posted_at) VALUES ('{title}', '{content}', NOW())"
execute_query(connection, query)
# 发布公告
post_announcement('中秋节活动通知', '尊敬的居民，中秋节将举行庆祝活动，请大家积极参与。')9.2 查看最新公告
def get_latest_announcements():
query = "SELECT * FROM announcements ORDER BY posted_at DESC LIMIT 10"
results = read_query(connection, query)
print(tabulate(results, headers=['ID', 'Title', 'Content', 'Posted At'], tablefmt='grid'))
# 查看最新公告
get_latest_announcements()10. 数据备份
备份数据库
import os
import subprocess
def backup_database():
backup_dir = "/path/to/backup/directory"
if not os.path.exists(backup_dir):
os.makedirs(backup_dir)
backup_file = os.path.join(backup_dir, f"db_backup_{datetime.datetime.now().strftime('%Y%m%d_%H%M%S')}.sql")
command = f"mysqldump -u root -pyourpassword property_management > {backup_file}"
subprocess.run(command, shell=True)
print(f"Database backed up to {backup_file}")
# 备份数据库
backup_database()总结
以上脚本涵盖了小区物业人员常用的多种功能，包括居民信息管理、缴费提醒、报表生成、紧急通知、维修请求管理、访客管理、设施预约、停车管理、公告发布和数据备份。
最后： 下方这份完整的软件测试视频教程已经整理上传完成，需要的朋友们可以自行领取【保证100%免费】
软件测试面试文档
我们学习必然是为了找到高薪的工作，下面这些面试题是来自阿里、腾讯、字节等一线互联网大厂最新的面试资料，并且有字节大佬给出了权威的解答，刷完这一套面试资料相信大家都能找到满意的工作。
扫一扫
768
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
