· 7 years ago · Oct 17, 2018, 03:46 PM
1# create-or-check-table_v03.py
2# Date 2018/Oct/17
3# Edited by Pong
4# Features: Check a database is created or not,
5# and make further process
6# https://docs.python.org/2/library/sqlite3.html
7# !!!!! Remeber to commit after execute create, insert command
8
9import os
10import sqlite3
11print '\n'
12
13our_db = 'sqlite.db2' # database name
14
15def initial_call():
16 global our_db
17 # checking the current location and
18 cwd = os.getcwd()
19 print '[current location is ]: ' + cwd #debug, print this py current location
20 db_location = cwd + '/' + our_db
21
22 command_create_table01 = '''create table table01 (
23 ID INTEGER PRIMARY KEY AUTOINCREMENT,
24 IP TEXT NOT NULL,Hostname TEXT,
25 Command TEXT NOT NULL
26 )
27 '''
28 command_create_table02 = '''create table table02 (
29 ID INTEGER,
30 Port INTEGER,
31 Service TEXT,
32 Version TEXT
33 )
34 '''
35
36
37
38 if os.path.exists(db_location):
39 print '[+] the a.txt file is exists, Good! '
40 # if exists, connect to database,
41 conn=sqlite3.connect(our_db)
42 print '[+] Connect to the db file, Successful'
43 print '[!] Going to Check 2 table is created before ????????'
44
45 # if table(s) is not exist, create for them
46 try:
47 print '[01] This is table01'
48 print 'table01 result:\n'
49 command = 'Select * from table01 '
50 conn.execute(command)
51 except:
52 # the command is copy from the "else" part
53 conn.execute(command_create_table01)
54
55
56 # if table(s) is not exist, create for them
57 try:
58 print '[02] This is table02'
59 print 'table02 result:\n'
60 command = 'Select * from table02'
61 conn.execute(command)
62 except:
63 # the command is copy from the "else" part
64 conn.execute(command_create_table02)
65
66 print '[++] No error arise ++++++++++++++++++++++++'
67
68
69 else:
70 # If 2 tables are not created, create now
71 print '[!] XXX the db file is not exists XXX'
72 conn=sqlite3.connect(our_db)
73 print '[C] Create to the db file, Successful'
74 print '[!] Creating 2 tables to the db now'
75 conn.execute(command_create_table01)
76 print '[!] Creating the 2nd table '
77 conn.execute(command_create_table02)
78 print '[+] 2 Table is successful created '
79
80
81
82
83
84# Delete the db file if want a function like that
85# These code is just a demo
86#
87# import subprocess
88# del_sqlite_everytime = 'del C:\\Users\\tim7\\Desktop\\testing_del\\a.txt'
89# del_sql = 'del ' + tep
90# subprocess.check_output(del_sql,shell=True)
91# print 'command : ' +del_sql + '\n'
92
93
94# call the initial call function
95initial_call()
96
97# Clear result
98print '-----'*8
99
100def insert_record_table01(_IP,_Hostname,_Command):
101 global our_db
102 conn=sqlite3.connect(our_db)
103 temp = conn.execute('INSERT INTO table01 ("IP","Hostname","Command") VALUES (?,?,?)',(_IP,_Hostname,_Command) )
104 # temp = conn.execute('INSERT INTO table01 ("IP","Hostname","Command") VALUES ("192.16',"hosesss","test cmd")')
105 print temp.lastrowid
106 result = conn.execute("select * from table01")
107 print result.fetchall()
108 conn.commit()
109
110
111
112insert_record_table01('192.168.230.130','','fake command for testing')
113insert_record_table01('192.168.230.131','','fake command for testing_02')
114
115# Test constratin and last row record
116
117conn=sqlite3.connect(our_db)
118result = conn.execute("select * from table01")
119print