Python sqlite3操作笔记

创建数据库

def create_tables(dbname):
    conn = sqlite3.connect(dbname)
    print "Opened database successfully";
    c = conn.cursor()
    c.execute('''CREATE TABLE VULNDB
        (Plugin_ID INT PRIMARY KEY     NOT NULL,
        NAME           TEXT    NOT NULL,
        Risk           TEXT     NOT NULL,
        Description    CHAR(1000),
        Solution       CHAR(1000));''')
    print "Table created successfully";
    conn.commit()
    conn.close()

查询或删除

def selectdb():
    conn = sqlite3.connect('vuln.db')
    conn.text_factory=str
    c = conn.cursor()
    cursor = c.execute("SELECT  count(Plugin_ID) from VULNDB")
    for row in cursor:
        print row

c.execute("DELETE from VULNDB where Plugin_ID=34311;")

在sqlite3中插入中文字符

#!/usr/bin/python
# -*- coding:utf-8 -*- 

import sqlite3
conn = sqlite3.connect('test.db')
conn.text_factory=str
c = conn.cursor()

users = (2,'username','high','腾讯qq', 'qq@example.com')
ins="INSERT INTO VULNDB(Plugin_ID,NAME,Risk,Description,Solution) VALUES (?,?,?,?,?)"
c.execute(ins,users)  
conn.commit()
print "Records created successfully";
c.close()
conn.close()

CSV导入数据库

def insertdata(v):
    conn = sqlite3.connect('vuln.db')
    conn.text_factory=str
    c = conn.cursor()
    ins="INSERT INTO VULNDB(Plugin_ID,NAME,Risk,Description,Solution) VALUES (?,?,?,?,?)"
    c.execute(ins,v)
    conn.commit()
    conn.close()
def write():    
    with open("loudong-20180913.csv", "r") as csvFile:
        reader2 = csv.reader(csvFile) # 读取csv文件，返回的是迭代类型
        for item2 in reader2:
            #print item2
            insertdata(item2)        
    csvFile.close()