pythonsqlite3总结_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()

参考链接：

https://my.oschina.net/letiantian/blog/217770

标签：execute,Plugin,Python,笔记,cursor,sqlite3,close,conn