导入数据库import sqlit3
连接数据库conn = sqlite3.connent("test.db")
创建游标cursor = connn.cursor()
创建表sql = "create table login(id varchar(20) primary key, name varchar(30), password varchar(30))"
cursor.execute(sql) 常用增删改查# 查看表结构cur.execute("PRAGMA table_info(jobs)")
# 表中增加字段cur.execute("alter table WeakManager add release_time text")
# 重命名表alter table tema rename to team2;
# 删除表中字段password（不支持删除列）alter table wx_userInfo drop column password;
# 删除表cur.execute("drop table WeakManager")
# 删除数据delete from wx_userInfo where id=3;
# 添加数据insert into table_name(v1, v2) values (v1, v2);
cur.execute("insert into people values (?, ?)", (who, age))
cur.execute("select * from people where name=:who and age=:age",{"who": who, "age": age})
-- 修改数据update wx_userInfo set name='bbb',nickName='bbb' where openID='xxxxxx';
-- 在原有数据上增加数据update wx_userInfo set control_order=control_order||'on_off=0' where _id=40;
-- 查询所以数据select * from wx_userInfo;-- 查询指定数量条数select * from wx_userInfo limit 3;
-- 查询升序输出记录select * from wx_userInfo order by id asc;
-- 查询降序输出记录select * from wx_userInfo order by id desc;
-- 条件查询select * from wx_userInfo where id in('3');select * from wx_userInfo where id between 0 and 2;
-- 查询指定字段记录数量统计select count(nickName) from wx_userInfo;select count(unionID) from wx_userInfo;
-- 查询指定字段的数据(将指定字段可能重复的数据去掉，列出所有不同的数据）select distinct unionID from wx_userInfo;select distinct id from wx_userInfo;
-- 查询最后一条数据，order by id desc 倒序，然后查找最后一条
limit 0,1select id, room_id,control_order,date_time from control_table order by id desc limit 0,1;结束操作# 1.提交事务conn.commit()# 2.关闭游标cursor.close()# 3.关闭连接conn.close()
|
