Sqlite_笔记

[toc]

sqlite优点

非常方便，无需安装服务器；可以把他当读写本地文件任意处置，但又比读取文件方便因为数据库支持sql语句；

可视化工具

sqlite studio官网直接下载，简单粗暴;
直接把lk_sqlite_db_1.db文件拖入sqlite studio软件里面就能读取里面的数据；

python操作sqlite

打开数据库

import sqlite3

conn=sqlite3.connect('lk_sqlite_db_1.db')

print("打开数据库成功") #如果没有就是创建如有原来有就是打开

创建表

import sqlite3

conn= sqlite3.connect("lk_sqlite_db_1.db")

c=conn.cursor()

c.execute(
    '''
        create table company(
            id int primary key not null,
            name text not null,
            age int not null,
            address char(50),
            salary real
        );
    '''
)

print("创建表成功")

conn.commit()

conn.close()

插入数据

import sqlite3

conn = sqlite3.connect("lk_sqlite_db_1.db")

c=conn.cursor()

c.execute(
    "insert into company(id,name,age,address,salary) values (1,'per',32,'califoniya',20000.0)"
)
c.execute(
    "insert into company(id,name,age,address,salary) values (2,'li',23,'beijing',1200.0)"
)
c.execute(
    "insert into company(id,name,age,address,salary) values (3,'gou',44,'najing',2300.0)"
)

conn.commit()

conn.close()

查询

import sqlite3

conn = sqlite3.connect("lk_sqlite_db_1.db")

c=conn.cursor()

result=c.execute(
    "select * from company"
)

print(type(result))#<class 'sqlite3.Cursor'>

for i in result:
    print(i,type(i)) #(1, 'per', 32, 'califoniya', 20000.0) <class 'tuple'>


conn.close()

更新操作

import sqlite3

conn = sqlite3.connect("lk_sqlite_db_1.db")

c=conn.cursor()

c.execute(
    "update company set salary=20.0 where id=2"
)

conn.commit()

result=c.execute(
    "select * from company where id=2"
)


for i in result:
    print(i,type(i)) #(1, 'per', 32, 'califoniya', 20000.0) <class 'tuple'>


conn.close()

删除操作

import sqlite3

conn = sqlite3.connect("lk_sqlite_db_1.db")

c=conn.cursor()

c.execute(
    "delete from  company where id=2"
)

conn.commit()

result=c.execute(
    "select * from company"
)


for i in result:
    print(i,type(i)) #(1, 'per', 32, 'califoniya', 20000.0) <class 'tuple'>


conn.close()