pythonsqlite3总结_Python实践代码总结第14集(数据库存储-sqlite)

1. Sqlite创建数据库

defcreate_db(db_path):

conn = sqlite3.connect(db_path)

conn.close()

print('created db successfully')

2.Sqlite创建表

defcreate_table_create(table_name,db_path):

conn = sqlite3.connect(db_path)

str = " CREATE TABLE IF NOT EXISTS {}"\

"(U_ID Text PRIMARY KEY NOT NULL,"\

"Tweet_ID Text NOT NULL,"\

" created_at TEXT ,"\

"text TEXT ,"\

"in_reply_to_screen_name TEXT,"\

"retweet_count TEXT,"\

"favorite_count TEXT,"\

"source TEXT,"\

"id_str TEXT,"\

"is_retweet TEXT);".format(table_name)

conn.execute(str)

conn.close()

3.Sqlite插入数据

definsert(count,db_path,tablename,Tweet_ID,created_at,text,in_reply_to_screen_name,retweet_count,favorite_count,source,id_str,is_retweet):

conn = sqlite3.connect(db_path)

executing = 'INSERT INTO {} (U_ID,Tweet_ID,created_at,text,in_reply_to_screen_name,retweet_count,favorite_count,source,id_str,is_retweet) \

VALUES ("{}","{}","{}","{}","{}","{}","{}","{}","{}","{}")'.format(tablename,count,Tweet_ID,created_at,text,in_reply_to_screen_name,retweet_count,favorite_count,source,id_str,is_retweet)

print(executing)

conn.execute(executing)

conn.commit()

conn.close()

4. Sqlite读取数据

defread_table(sql,db_dir):

conn = sqlite3.connect(db_dir)

print("Opened database successfully");

df = pd.read_sql(sql, conn)

conn.close()

returndf

defselect(feature,table,db_dir):

sql='SELECT {} FROM {}'.format(feature,table)

high_response_after = read_table(sql,db_dir)

returnhigh_response_after