python对sqlite增删改查_python sqlite3 增删改查(最基本的增删改查)

话不多说

一、创建数据库和建表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' #删除一整个表格