def check_db():
_hasdb = False
if not os.path.exists(db_path):
os.mkdir(db_path)
logger.warn('db directory does not exist, create new')
if os.path.isfile(db_filepath):
if os.path.getsize(db_filepath) > 100:
with open(db_filepath) as fd:
db_header = fd.read(100)
if db_header[:16] == 'SQLite format 3\x00':
qdata = query_db('select count(*) from demklog')
if qdata and qdata[0] > 0:
return True
else:
try:
conn, cur = connect_sqlite(db_filepath)
cur.execute(create_log_table)
close_sqlite(conn)
_hasdb = True
except:
logger.error('Error on create table demklog. ')
return False
return _hasdb
def run_main():
"""
主函数
"""
database_path = 'E:/python/lecture03/lecture03_proj/src/database/' #数据库地址
zip_filename = 'soccer.zip' #数据文件
zip_filepath = os.path.join(database_path, zip_filename)
database_filename = get_dataset_filename(zip_filepath)
print database_filename
database_filepath = os.path.join(database_path, database_filename)
print "解压zip..."
# unzip(zip_filepath,database_filepath)
print "解压完成.."
print database_filepath
#连接数据库
conn, cur = connect_sqlite(database_filepath)
#运行task1
player_df = run_task1(cur)
run_task2(player_df)
# 分析结束，关闭数据库
close_sqlite(conn)
# 删除解压数据，清理空间
if os.path.exists(database_filepath):
os.remove(database_filepath)
def run_main():
"""
主函数
"""
# 声明变量
database_path = './database' # 数据库路径
zip_filename = 'soccer.zip' # zip文件名
zip_filepath = os.path.join(database_path, zip_filename) # zip文件路径
database_filename = get_dataset_filename(zip_filepath) # 数据库文件名（在zip中）
database_filepath = os.path.join(database_path,
database_filename) # 数据库文件路径
print "解压zip...",
unzip(zip_filepath, database_path)
print "完成."
# 连接数据库
conn, cur = connect_sqlite(database_filepath)
# 运行task1
player_df = run_task1(cur)
# 运行task2
run_task2(player_df)
# 分析结束，关闭数据库
close_sqlite(conn)
# 删除解压数据，清理空间
if os.path.exists(database_filepath):
os.remove(database_filepath)
def query_db(sql, value=[]):
conn, cur = connect_sqlite(db_filepath)
#print value, sql
cur.execute(sql, value)
data = cur.fetchall() #cur.fetchone()
print 'row data=', data
close_sqlite(conn)
return data
def read_db(num=1000):
conn, cur = connect_sqlite(db_file)
sql = 'select * from demklog order by _id desc limit ' + str(num)
cur.execute(sql)
data = cur.fetchall()
close_sqlite(conn)
return data
def write_db(myrow):
conn = None
try:
conn, cur = connect_sqlite(db_filepath)
cur.execute(insert_log_sql, myrow)
conn.commit()
except Exception as e:
logger.error(e)
logger.error(myrow)
finally:
close_sqlite(conn)
def read_db_2_dataframe(sql, limit_num=1000):
'''
, pickup db data and then write to a csv file.
default sql = 'select * from demklog order by zcm '
default limit rows =1000
'''
if (not sql == None) and sql.strip().lower().endswith('limit %d'):
sql += ' limit ' + str(limit_num)
conn, cur = connect_sqlite(db_file)
df = pd.read_sql(sql, conn)
close_sqlite(conn)
#----------write to csv file------------
#df.to_csv(output, index=None)
print 'get df=', df.head(100)
return df