def get_data():
"""
从数据库中获得数据
:return:
"""
x_train = []
y_train = []
x_test = []
try:
conn = conn_db()
cur = conn.cursor()
for i in range(2000):
sql = "SELECT shop_id, location_id, per_pay, shop_level FROM ShopInfo WHERE shop_id='" + str(i + 1) + "'"
shop_infos = exe_query(cur, sql)
infos = shop_infos
for m in range(14):
for info in infos:
temp = []
temp.append(int(info[0]))
temp.append(int(info[1]))
temp.append(int(info[2]))
temp.append(int(info[3]))
temp.append(int(m + 489))
x_test.append(temp)
for j in range(489):
for shop_info in shop_infos:
temp = []
temp.append(int(shop_info[0]))
temp.append(int(shop_info[1]))
temp.append(int(shop_info[2]))
temp.append(int(shop_info[3]))
pay = [0, 0]
# view = 0
sql = "SELECT date,sum FROM UserPay WHERE shop_id='" + str(i + 1) + "' AND date='" + str(j) + "'"
user_pays = exe_query(cur, sql)
for user_pay in user_pays:
pay[0] += int(user_pay[0])
pay[1] += int(user_pay[1])
temp.append(int(pay[0]))
x_train.append(temp)
y_train.append(int(pay[1]))
# sql = "SELECT date,sum FROM UserPay WHERE shop_id='" + str(i + 1) + "' AND date='" + str(j) + "'"
# user_views = exe_query(cur, sql)
# for user_view in user_views:
# view += int(user_view[0])
# x_train[5].append(int(view))
# print(shop_info[0], user_pay[0])
except Exception as e:
print(e)
finally:
# conn_close(conn, cur)
return x_train, y_train, x_test
def get_user_pay(address):
"""
获得用户的购物信息并存入数据库
:param address:
:return:
"""
try:
conn = conn_db()
cur = conn.cursor()
sql = "DROP TABLE if EXISTS UserPay"
exe_table(cur, sql)
sql = "CREATE TABLE UserPay(id INT NOT NULL AUTO_INCREMENT,shop_id INT NOT NULL ,date INT,sum INT ," \
"PRIMARY KEY (id) ,FOREIGN KEY (shop_id) REFERENCES ShopInfo(shop_id)) " \
"ENGINE = InnoDB DEFAULT CHARSET = UTF8"
exe_table(cur, sql)
file = open(address)
shop = ''
num = []
for i in range(489):
num.append(0)
while 1:
lines = file.readlines(10000)
if not lines:
user_pay_database(conn, cur, num, shop)
break
for line in lines:
file_content = line.split(",")
if shop == '':
shop = file_content[1]
dates = file_content[2].split(" ")
num[cal_day(dates[0])] += 1
elif shop == file_content[1]:
dates = file_content[2].split(" ")
num[cal_day(dates[0])] += 1
elif shop != file_content[1]:
user_pay_database(conn, cur, num, shop)
shop = file_content[1]
for i in range(489):
num[i] = 0
dates = file_content[2].split(" ")
num[cal_day(dates[0])] += 1
except Exception as e:
print(e)
finally:
conn_close(conn, cur)
def create_data(form):
"""
建立数据库表格
:param form:
:return:
"""
try:
conn = conn_db()
cur = conn.cursor()
sql = "DROP TABLE if EXISTS " + form
exe_table(cur, sql)
sql = "CREATE TABLE " + form + "(id INT NOT NULL AUTO_INCREMENT,content VARCHAR (255),sentence_id INT ," \
"result VARCHAR(255) ,PRIMARY KEY (id)) ENGINE = InnoDB DEFAULT CHARSET = UTF8"
exe_table(cur, sql)
except Exception as e:
print(e)
finally:
return conn, cur
def create_result(form):
"""
建立数据库表格
:param form:
:return:
"""
try:
conn = conn_db()
cur = conn.cursor()
sql = "DROP TABLE if EXISTS " + form
exe_table(cur, sql)
sql = "CREATE TABLE " + form + "(id INT NOT NULL AUTO_INCREMENT,content VARCHAR (255),sentence_id INT ," \
"history float ,military float ,baby float ,world float ,tech float ,game float " \
",society float ,sports float ,travel float ,car float ,food float ,entertainment float " \
",finance float ,fashion float ,discovery float ,story float ,regimen float ,essay float " \
",PRIMARY KEY (id)) ENGINE = InnoDB DEFAULT CHARSET = UTF8"
exe_table(cur, sql)
except Exception as e:
print(e)
finally:
return conn, cur
def shop_info_database(file_content):
"""
将文本文件处理并存入数据库中
:param file_content:
:return:
"""
try:
conn = conn_db()
cur = conn.cursor()
sql = "DROP TABLE if EXISTS ShopInfo"
exe_table(cur, sql)
sql = "CREATE TABLE ShopInfo(shop_id INT NOT NULL AUTO_INCREMENT,city_name VARCHAR (255),location_id INT ," \
"per_pay INT ,score INT ,comment_cnt INT ,shop_level INT ,cate_1_name VARCHAR (255) ,cate_2_name" \
" VARCHAR (255) ,cate_3_name VARCHAR (255) ,PRIMARY KEY (shop_id)) ENGINE = InnoDB DEFAULT CHARSET = UTF8"
exe_table(cur, sql)
for i in range(0, len(file_content)):
contents = file_content[i].split(",")
if len(contents) == 10:
sql = "INSERT INTO ShopInfo VALUES ('" + contents[0] + "','" + contents[1] + "','" + contents[2] + "'" \
",'" + \
contents[3] + "','" + contents[4] + "','" + contents[5] + "','" + contents[6] + "'," \
"'" + contents[
7] + "','" + contents[8] + "','" + contents[9] + "')"
elif len(contents) == 9:
sql = "INSERT INTO ShopInfo VALUES ('" + contents[0] + "','" + contents[1] + "','" + contents[2] + "'" \
",'" + \
contents[3] + "','" + contents[4] + "','" + contents[5] + "','" + contents[6] + "'," \
"'" + contents[
7] + "','" + contents[8] + "',NULL)"
exe_update(conn, cur, sql)
except Exception as e:
print(e)
print(contents[0])
finally:
conn_close(conn, cur)