python操作sqlite3数据库_python操作sqlite3数据库完全代码详解

# Name: pySnipnix.py

# Author: pantuts

# Email: [email protected]

# Description: Saving your snippets to sqlite3 database.

# Agreement: You can use, modify, or redistribute this tool under

# the terms of GNU General Public License (GPLv3).

# This tool is for educational purposes only. Any damage you make will not affect the author.

# first run: python pySnipnix.py

#!/usr/bin/python

import argparse

import sqlite3

import re

import sys

# important, create the file

fileN = open('database.db', 'a+')

def main():

# add all arguments needed

# for argument that need FILE use [ type=argparse.FileType('r') ]

parser = argparse.ArgumentParser(description=None, usage='python %(prog)s -h --help -f file -s search -a \'title\' \'code here\' -e id -d id -v --version')

parser.add_argument('-f', metavar='filename', type=argparse.FileType('r'), dest='filename', help='File for database')

parser.add_argument('-s', metavar='string', dest='search', help='Search for string in database')

parser.add_argument('-a', metavar='string', dest='add', nargs=2, help='Add snippet. You should use \'\' for long string')

parser.add_argument('-e', metavar='id', type=int, dest='edit', help='Edit snippet')

parser.add_argument('-d', metavar='id', type=int, dest='delete', help='Delete from database')

parser.add_argument('-S', dest='show', action='store_true', help='Show all records')

parser.add_argument('-v', '--version', action='version', version='%(prog)s 1.0', help='Print version')

# parse all arguments to 'args'

args = parser.parse_args()

# database connection

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

cur = conn.cursor()

def createTable():

cmd = 'CREATE TABLE IF NOT EXISTS snippets (id INTEGER PRIMARY KEY AUTOINCREMENT, title VARCHAR(50), code VARCHAR NOT NULL)'

cur.execute(cmd)

conn.commit()

def insertSnippets():

# convert the string to lowercase and then execute

lst = [args.add[0].lower() , args.add[1].lower()]

cmd = 'INSERT INTO snippets VALUES (NULL, ?, ?)'

cur.execute(cmd, lst)

conn.commit()

print('\nNew Snippet...')

print('Snippet Title: \t%s' % args.add[0])

print('Code: \t\t%s' % args.add[1])

def editSnippets():

cur.execute('SELECT * FROM snippets where id=%s' % str(args.edit))

res = cur.fetchone()

if res is None:

print('No record to edit!')

else:

resl = [result for result in res]

print('Current title >> ' + resl[1])

ed1 = input('Title (Leave black, same title): ')

print('Current snippet >> ' + resl[2])

ed2 = input('Code: ')

if ed1 is '':

ed1 = resl[1]

cur.executemany('UPDATE snippets SET title=\'%s\', code=\'%s\' WHERE id=?' % (ed1.lower(), ed2.lower()), str(resl[0]))

conn.commit()

print('Done!\n')

def deleteSnippets():

print('\nDeleting record with ID %s ...' % str(args.delete))

# first find if record exists and return false if not found

cur.execute('SELECT * FROM snippets where id=%s' % str(args.delete))

res = cur.fetchone()

if res is None:

print('No record to delete!')

else:

cmd = 'DELETE FROM snippets where id=%s' % str(args.delete)

cur.execute(cmd)

conn.commit()

print('Deleted!\n')

def showOrSearch(cmd):

# creating conn.create_function explanation: 1st(string to be used inside SQL), 2nd(count of arguments), 3rd(the function created)

def matchPattern(pattern, columnName):

pat = re.compile(pattern)

return pat.search(columnName) is not None

conn.create_function('matchPattern', 2, matchPattern)

if cmd == 2:

cur.execute('SELECT * FROM snippets WHERE matchPattern(\'%s\', title)' % str(args.search.lower()))

else:

cmd = 1

cur.execute('SELECT * FROM snippets')

res = cur.fetchall()

# create empty dict, process filter keys and values

s = {'id':{}, 'title':{}, 'code':{}}

print('\nRecords result...')

for result in res:

s['id'] = result[0]

s['title'] = result[1]

s['code'] = result[2]

print('[%s]\t[ %s ]---------->[ %s ]\n' % (s['id'], s['title'], s['code']))

# invoke creation of table

createTable()

if len(sys.argv) < 1:

parser.print_help()

# do filtering when -f 'filename' is correct

if args.filename is not None:

if args.show:

if args.add or args.delete or args.search or args.edit:

print('\nYou can\'t use other options with -S option')

exit()

else:

args.add = None

args.delete = None

args.search = None

args.edit = None

showOrSearch(1)

elif args.edit:

if args.add or args.delete or args.search:

print('\nYou can\'t use other options with -e option')

exit()

else:

args.search = None

args.add = None

args.delete = None

editSnippets()

else:

if args.add is None and args.delete is None and args.search is None:

print('\n!!!!!You need to specify addional arguments to process the database!!!!!\n')

parser.print_help()

if args.search:

args.edit = None

showOrSearch(2)

if args.add:

args.edit = None

args.search = None

insertSnippets()

if args.delete:

args.search = None

args.edit = None

deleteSnippets()

else:

parser.print_help()

# close our connection to the database

conn.commit()

conn.close()

def by():

print('\n[Script by: pantuts]')

print('[email: [email protected]]')

if __name__=="__main__":

main()

# close our file

fileN.close()