• 主页
• 运维
• 开发
• 公告
• 登录
• 注册

connect方法

connect()

import sqlite3
 conn = sqlite3.connect('test.db')

创建游标

cursor()

cur = conn.cursor()

 

执行sql语句

execute()

创建表

cur.execute('''
     CREATE TABLE student_info 
         (   number TEXT  PRIMARY KEY           NOT NULL,
             name          TEXT          NOT NULL,
             gander         NUMERIC            NOT NULL,
             birth          TEXT    NOT NULL,
             major         TEXT         NOT NULL,
             phone         TEXT         NOT NULL,
             QQ        TEXT,
             Email    TEXT );
 ''')

增加数据

cur.execute('''
     INSERT INTO student_info 
     (number,name,gander,birth,major,phone,QQ,Email) 
     VALUES
     ('000001','张三','0','1992-03-05','软件工程','13598651245','78678945777','test@163.com');
 ''')

查询数据

cur.execute('''
     SELECT * from student_info
 ''')
 for info in cur:
     print(info)

运行结果：

('000001', '张三', 0, '1992-03-05', '软件工程', '13598651245', '78678945777', 'test@163.com')

('000002', '李四', 1, '1991-07-13', '软件工程', '13965781265', '78978946554', 'lisi@163.com')

更新数据

cur.execute('''
     UPDATE student_info SET phone='13865478956', QQ='78956231456' where rowid=2
 ''')
 conn.commit()
 cur.execute('''
     SELECT * from student_info
 ''')
 for info in cur:
     print(info)

运行结果

('000001', '张三', 0, '1992-03-05', '软件工程', '13598651245', '78678945777', 'test@163.com')

('000002', '李四', 1, '1991-07-13', '软件工程', '13865478956', '78956231456', 'lisi@163.com')

删除数据

cur.execute('''
     DELETE from student_info where rowid=2
 ''')

将第二行数据删除。

 

执行sql脚本

executescript()

定义脚本:schema.sql

CREATE TABLE student_info
        (number TEXT  PRIMARY KEY           NOT NULL,
        name          TEXT          NOT NULL,
        gander         NUMERIC            NOT NULL,
        birth          TEXT    NOT NULL,
        major         TEXT         NOT NULL,
        phone         TEXT         NOT NULL,
        QQ        TEXT,
        Email    TEXT );
 
 INSERT INTO student_info
      (number,name,gander,birth,major,phone,QQ,Email)
      VALUES
     ('000002','李四','1','1991-07-13','软件工程','13965781265','78978946554','lisi@163.com');
 
 INSERT INTO student_info
      (number,name,gander,birth,major,phone,QQ,Email)
      VALUES
     ('000001','张三','0','1992-04-16','软件工程','13978945612','745454557878','test@163.com');

执行：

with open('schema2.sql', 'r', encoding='utf-8') as f:
     sql = f.read()
 cur.executescript(sql)

 

一次执行多条sql语句

purchases = [
     ('000003','小A','1','1992-11-13','软件工程','13963333333','11112223322','A@163.com'),
     ('000004','小B','0','1993-09-11','软件工程','13978955423','78978979879','B@163.com'),
     ('000005','小C','1','1994-02-16','软件工程','13756523321','64465313212','C@163.com'),
             ]
 cur.executemany('INSERT INTO student_info VALUES (?,?,?,?,?,?,?,?)', purchases)

 

获取结果集

fetchall()

获取所有的数据，没有数据返回空列表

cur.execute('select * from student_info')
 print(cur.fetchall())

 

fetchone()

每次获取一条数据，返回完以后，就返回None

cur.execute('select * from student_info')
 print(cur.fetchone())
 print(cur.fetchone())

 

fetchmany(size)

每次获取几条数据

cur.execute('select * from student_info')
 print(cur.fetchmany(2))