python项目:房源信息管理(附代码)
数据库信息
把爬取的二手房源信息,导入mysql当中。(需要文件的私聊我就行)
#-*- cdoeing = utf-8 -*-
#@Time : 2020-12-16
#name : LBOcean
import xlrd
import pymysql
# import importlib
# importlib.reload(sys) #出现了reload错误使用
# 连接数据库
try:
db = pymysql.connect(host="localhost",
user="root",
passwd="110120",
db="hous",
charset='utf8')
except ConnectionError as ex:
print("could not connect to mysql server", ex)
def open_excel():
try:
book = xlrd.open_workbook("二手房源.xlsx") # 文件名，把文件与py文件放在同一目录下
except FileNotFoundError as e:
print("open excel file failed!", e)
try:
sheet = book.sheet_by_name("Sheet") # execl里面的worksheet1
return sheet
except ValueError as e:
print("locate worksheet in excel failed!", e)
def search_count():
cursor = db.cursor()
select = "select count(id) from project" # 获取表中xxxxx记录数
cursor.execute(select) # 执行sql语句
line_count = cursor.fetchone()
print(line_count[0])
def insert_data():
sheet = open_excel()
cursor = db.cursor()
for i in range(1, sheet.nrows): # 第一行是标题名，对应表中的字段名所以应该从第二行开始，计算机以0开始计数，所以值是1
name = sheet.cell(i, 0).value # 取第i行第0列
data1 = sheet.cell(i, 1).value # 取第i行第1列，下面依次类推
data2 = sheet.cell(i, 2).value
data3 = sheet.cell(i, 3).value
data4 = sheet.cell(i, 4).value
data5 = sheet.cell(i, 5).value
data6 = sheet.cell(i, 6).value
value = (name, data1, data2, data3, data4, data5, data6)
print(type(name), type(data1), type(data2), type(data3), type(data4), type(data5), type(data6))
print(value)
sql = "INSERT INTO mashine(小区名称,户型,面积,朝向,楼层,年代,总价) VALUES(%s,%s,%s,%s,%s,%s,%s)"
cursor.execute(sql, value) # 执行sql语句
db.commit()
cursor.close() # 关闭连接
insert_data()
db.close() # 关闭数据
print("ok ")
main_test.py 主函数里的代码(下面的代码)
#-*- cdoeing = utf-8 -*-
#@Time : 2020-12-16
#name : LBOcean
import xlrd
import test
from Homeworkfile.Select import Inster_Data, Changehous, Deletehous
from Homeworkfile.test import Menu, SelectHousMenu
def main(): # 主函数
arry = [0, 1, 2, 3, 4, 5,6,7,8]
Menu() # 先打印菜单
i = True
while True:
a = input("请输入： ")
if a.isdigit() and int(a) in arry:
a = int(a)
while a:
if a == 1:
SelectHousMenu()#查询房源信息输入
Menu()
break
if a == 2:
Inster_Data()#添加房源信息输入
Menu()
break
if a == 3:
Changehous()# 修改房源信息输入
Menu()
break
if a == 4:
Deletehous()#删除房源信息输入
Menu()
break
elif a > 4 or a < 0:
print("输入有误！")
break
if a == 0: # 按0退出进程
print("系统已退出！")
return i==False
else:
print("请输入0--5！")
main()
Select.py 查询文件的代码(下面的代码),增删改查都可以实现
#-*- cdoeing = utf-8 -*-
#@Time : 2020-12-16
#name : LBOcean
import pymysql
def Inster_Data():
"""
插入数据到mysql表中
:return:
"""
db = pymysql.connect(host='localhost', user='root', password='110120', port=3306, db='hous')#输入连接mysql
cursor = db.cursor()#创建游标
residential = str(input("小区:")) # 输
house = str(input("户型:")) # 入
area = float(input("面积:")) # 插
orientation = str(input("朝向:")) # 入
floor = str(input("楼层:")) # 的
years = str(input("年代:")) # 内
totalprice = float(input("总价:"))# 容
#sql语句
sql = 'INSERT INTO mashine(residential ,house ,area ,orientation ,floor ,years ,totalprice) values(%s,%s,%s,%s,%s,%s,%s)'
try:
"""
执行sql语句并检查有没有异常
"""
if cursor.execute(sql, (residential, house, area, orientation, floor, years, totalprice)):
print('插入数据成功')
db.commit()#提交数据到mysql中
except:
print('插入数据失败')
db.rollback()#事务回滚
db.close()#关闭mysql
class Db():
def __init__(self, host="localhost", user="root",
passwd="070902", port="3306", database="hous"):
self.db = pymysql.connect(host, user, passwd, database, )
self.cursor = self.db.cursor() # 创建游标对象
def Query_Data():
"""
查询所有数据
:return:
"""
host = "localhost" # ip:默认是localhost
user = "root" # 用户名
passwd = "110120" # 用户密码
port = "3306" # 端口号
database = "hous" # 数据库名称
db = pymysql.connect(host, user, passwd, database) # 连接mysql
cursor = db.cursor() # 创建游标
sql = 'select * from mashine'
"""
sql原生语句
"""
try:
# 执行sql语句
cursor.execute(sql)
result = cursor.fetchall() # 把查询的所有信息放在result中
for row in result: # 遍历result并输出结果
print(row)
print("查询完成")
except:
print("查询数据失败")
db.rollback()#事务回滚
db.close()#关闭mysql
def Residential_Data():
host = "localhost" # ip:默认是localhost
user = "root" # 用户名
passwd = "110120" # 用户密码
port = "3306" # 端口号
database = "hous" # 数据库名称
db = pymysql.connect(host, user, passwd, database) # 连接mysql
cursor = db.cursor() # 创建游标
residential = input("请输入小区名称:")
sql = "select * from mashine where residential ='{}'".format(residential)
"""
sql原生语句
"""
cursor.execute(sql) # 执行sql语句
result = cursor.fetchall() # 把查询的所有信息放在result中
if len(result) == 0 : # 如果查出来的数据长度为0,就相当于没有查到
print("对不起,没有该小区的房间!")
else : # 如果长度不为0,则输出结果
for row in result:
print(row)
db.close()
def House_Data():
host = "localhost" # ip:默认是localhost
user = "root" # 用户名
passwd = "110120" # 用户密码
port = "3306" # 端口号
database = "hous" # 数据库名称
db = pymysql.connect(host, user, passwd, database) # 连接mysql
cursor = db.cursor() # 创建游标
try:
houses = str(input("请输入户型:"))
sql = "select * from mashine where house ='{}'".format(houses) # 编写sql语句
cursor.execute(sql)# 执行sql语句
result = cursor.fetchall()# 把查询的所有信息放在result中
if len(result) == 0: # 如果查出来的数据长度为0,就相当于没有查到
print("对不起,没有该户型的房间!")
else:# 如果长度不为0,则输出结果
for row in result:
print(row)
except:
print("对不起,没有该户型的房间!")
db.close()
def Area_Data():
host = "localhost" # ip:默认是localhost
user = "root" # 用户名
passwd = "110120" # 用户密码
port = "3306" # 端口号
database = "hous" # 数据库名称
db = pymysql.connect(host, user, passwd, database) # 连接mysql
cursor = db.cursor() # 创建游标
try:
print("查询范围(最小值-最大值)之间的房间")
areas = int(input("请输入面积最小值:"))
areae = int(input("请输入面积最大值:"))
sql = "select * from mashine where area >='{}' and area <='{}'".format(areas, areae)
cursor.execute(sql)
result = cursor.fetchall()
if len(result) == 0:
print("对不起,没有该面积的房间!")
else:
for row in result:
print(row)
except:
print("对不起,没有该面积的房间!")
db.close()
def Orientation_Data():
host = "localhost" # ip:默认是localhost
user = "root" # 用户名
passwd = "110120" # 用户密码
port = "3306" # 端口号
database = "hous" # 数据库名称
db = pymysql.connect(host, user, passwd, database) # 连接mysql
cursor = db.cursor() # 创建游标
try:
orientation = str(input("请输入朝向:"))
sql = "select * from mashine where orientation='{}'".format(orientation)
cursor.execute(sql)
result = cursor.fetchall()
if len(result) == 0:
print("对不起,没有该朝向的房间!")
else:
for row in result:
print(row)
except:
print("对不起,没有该朝向的房间!")
db.close()
def Floor_Data():
host = "localhost" # ip:默认是localhost
user = "root" # 用户名
passwd = "110120" # 用户密码
port = "3306" # 端口号
database = "hous" # 数据库名称
db = pymysql.connect(host, user, passwd, database) # 连接mysql
cursor = db.cursor() # 创建游标
try:
floor = str(input("请输入楼层:"))
sql = "select * from mashine where floor='{}'".format(floor)
cursor.execute(sql)
result = cursor.fetchall()
if len(result) == 0:
print("对不起,没有该楼层的房间!")
else:
for row in result:
print(row)
except:
print("对不起,没有该楼层的房间!")
db.close()
def Years_Data():
host = "localhost" # ip:默认是localhost
user = "root" # 用户名
passwd = "110120" # 用户密码
port = "3306" # 端口号
database = "hous" # 数据库名称
db = pymysql.connect(host, user, passwd, database) # 连接mysql
cursor = db.cursor() # 创建游标
try:
years = str(input("请输入年代:"))
sql = "select * from mashine where years='{}'".format(years)
cursor.execute(sql)
result = cursor.fetchall()
if len(result) == 0:
print("对不起,没有该年代的房间!")
else:
for row in result:
print(row)
except Exception as e:
print("对不起,没有该年代的房间!")
db.close()
def Totalprice_Data():
host = "localhost" # ip:默认是localhost
user = "root" # 用户名
passwd = "110120" # 用户密码
port = "3306" # 端口号
database = "hous" # 数据库名称
db = pymysql.connect(host, user, passwd, database) # 连接mysql
cursor = db.cursor() # 创建游标
try:
print("查询范围(最小值-最大值)之间的房子")
totalprices = int(input("请输入总价最小值:"))
totalpricee = int(input("请输入总价最大值:"))
sql = "select * from mashine where totalprice >='{}' and totalprice <='{}'".format(totalprices, totalpricee)
cursor.execute(sql)
result = cursor.fetchall()
if len(result) == 0:
print("对不起,没有这么贵的房间!")
else:
for row in result:
print(row)
except:
print("对不起,没有这么贵的房间!")
db.close()
def Changehous():
host = "localhost" # ip:默认是localhost
user = "root" # 用户名
passwd = "110120" # 用户密码
port = "3306" # 端口号
database = "hous" # 数据库名称
db = pymysql.connect(host, user, passwd, database) # 连接mysql
cursor = db.cursor() # 创建游标
title = ['residential','house','area','orientation','floor','years','totalprice']
field = str(input("请输入要修改的字段名:"))
if field in title:
name = str(input("请输入要修改的内容"))
id = int(input("请修改要更新内容的id:"))
if field == title[0]:
sql = "update mashine set residential='{}' where id='{}'".format(name,id)
elif field == title[1]:
sql = "update mashine set house='{}' where id='{}'".format(name, id)
elif field == title[2]:
sql = "update mashine set area='{}' where id='{}'".format(name, id)
elif field == title[3]:
sql = "update mashine set orientation='{}' where id='{}'".format(name, id)
elif field == title[4]:
sql = "update mashine set floor='{}' where id='{}'".format(name, id)
elif field == title[5]:
sql = "update mashine set years='{}' where id='{}'".format(name, id)
elif field == title[6]:
sql = "update mashine set totalprice='{}' where id='{}'".format(name, id)
else:
print("没有该字段")
try:
cursor.execute(sql)
db.commit()
print("修改成功")
except Exception as e:
print("修改失败")
finally:
db.close()
def Deletehous():
"""
删除数据库中字段信息
:return:
"""
host = "localhost" # ip:默认是localhost
user = "root" # 用户名
passwd = "110120" # 用户密码
port = "3306" # 端口号
database = "hous" # 数据库名称
db = pymysql.connect(host, user, passwd, database) # 连接mysql
cursor = db.cursor() # 创建游标
try:
id = str(input("请输入你要删除id:"))
# SQL 删除语句
sql = "delete from mashine where id = '{}'".format(id)
# 执行SQL语句
cursor.execute(sql)
db.commit()# 提交
print("删除成功")
except Exception as e:
print("删除失败",e)
# 关闭链接
db.close()
cursor.close()
test.py 选择框(下面代码)
#-*- cdoeing = utf-8 -*-
#@Time : 2020-12-16
#name : LBOcean
import test
from Homeworkfile.Select import Inster_Data, Changehous, Deletehous
from Homeworkfile.test import Menu, SelectHousMenu
def main(): # 主函数
arry = [0, 1, 2, 3, 4, 5,6,7,8]
Menu() # 先打印菜单
i = True
while True:
a = input("请输入： ")
if a.isdigit() and int(a) in arry:
a = int(a)
while a:
if a == 1:
SelectHousMenu()#查询房源信息输入
Menu()
break
if a == 2:
Inster_Data()#添加房源信息输入
Menu()
break
if a == 3:
Changehous()# 修改房源信息输入
Menu()
break
if a == 4:
Deletehous()#删除房源信息输入
Menu()
break
elif a > 4 or a < 0:
print("输入有误！")
break
if a == 0: # 按0退出进程
print("系统已退出！")
return i==False
else:
print("请输入0--5！")
main()
下面是演示
多学一样本事,少说一句求人的话。
