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