python操作sqlite3数据库完全源码

下面内容是关于python操作sqlite3数据库完全的内容。
# Name: pySnipnix.py
# Author: pantuts
# Email: pantuts@gmail.com
# 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: tt%s' % args.add[1])

def editSnippets():
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
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:
else:
cmd = 1
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: pantuts@gmail.com]')

if __name__=="__main__":
main()
# close our file
fileN.close()

 

转载于:https://www.cnblogs.com/cottonwood/p/11045862.html