一、创建数据库和建表happy
'''导入sqlite3库''' import sqlite3 '''创建数据库''' conn = sqlite3.connect('demo.db') #demo.db不存在时在py同一目录下自动创建demo.db '''创建游标''' cursor = conn.cursor() '''建表''' create_table_sql = '''create table happy( username text, password text, id int)''' cursor.execute(create_table_sql) #执行这个语句 '''关闭连接''' conn.commit() cursor.close()
二、插入数据:执行语句即可
username = "ytouch" password = "123456" user_id = 1 insert_data_sql = '''insert into happy (username,password,id) values (:st_name,:st_username,:st_id)''' cursor.execute(insert_data_sql,{'st_name':username,'st_username':password,'st_id':user_id})
三、修改数据:执行以下语句就可以
update_sql = 'update happy set password = ? where username = ?' cursor.execute(update_sql,("4578623","ytouch"))
四、查询数据:执行以下语句就可以
'''查询语句''' search_sql = "select username,password from happy" '''执行语句''' results = cursor.execute(search_sql) '''遍历打印输出''' all_results = results.fetchall() for happys in all_results: print(type(happys)) #type:tuple print(happys)
五、删除数据
1.删除指定数据
delete_sql = 'delete from happy where id = 25'
2.删除一整个表格
delete_all_sql = 'drop table happy' #删除一整个表格