Python sqlite3操作笔记

时间：2023-03-08 21:02:31

创建数据库

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- -*- 

import sqlite3

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

conn.text_factory=str

c = conn.cursor()

users = (,'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()




参考链接：


https://my.oschina.net/letiantian/blog/217770
相关文章