/
DB.py
executable file
·166 lines (147 loc) · 3.91 KB
/
DB.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
#!/usr/bin/python
import time
from os import path, listdir
from sys import stderr
from shutil import copy2
from Reddit import Comment, Post
try: import sqlite3
except ImportError: import sqlite as sqlite3
SCHEMA = {
'posts' :
'id text primary key,' +
'shorturl text,' +
'url text,' +
'subreddit text,' +
'author text,' +
'title text',
'config' :
'key text primary key,' +
'value text',
}
DB_FILE = 'posts.db'
class DB:
def __init__(self):
self.logger = stderr
if path.exists(DB_FILE):
self.debug('__init__: using database file: %s' % DB_FILE)
else:
self.debug('__init__: database file (%s) not found, creating...' % DB_FILE)
self.conn = None
self.conn = sqlite3.connect(DB_FILE) #TODO CHANGE BACK, encoding='utf-8')
self.conn.text_factory = lambda x: unicode(x, "utf-8", "ignore")
# Don't create tables if not supplied.
if SCHEMA != None and SCHEMA != {} and len(SCHEMA) > 0:
# Create table for every schema given.
for key in SCHEMA:
self.create_table(key, SCHEMA[key])
def debug(self, text):
tstamp = time.strftime('[%Y-%m-%dT%H:%M:%SZ]', time.gmtime())
text = '%s DB: %s' % (tstamp, text)
self.logger.write('%s\n' % text)
if self.logger != stderr:
stderr.write('%s\n' % text)
def create_table(self, table_name, schema):
cur = self.conn.cursor()
query = '''create table if not exists %s (%s)''' % (table_name, schema)
cur.execute(query)
self.commit()
cur.close()
def commit(self):
try_again = True
while try_again:
try:
self.conn.commit()
try_again = False
except:
time.sleep(1)
def insert(self, table, values):
cur = self.conn.cursor()
try:
questions = ''
for i in xrange(0, len(values)):
if questions != '': questions += ','
questions += '?'
exec_string = '''insert into %s values (%s)''' % (table, questions)
result = cur.execute(exec_string, values)
last_row_id = cur.lastrowid
cur.close()
return last_row_id
except sqlite3.IntegrityError:
cur.close()
return -1
def delete(self, table, where, values=[]):
cur = self.conn.cursor()
q = '''
delete from %s
where %s
''' % (table, where)
cur.execute(q, values)
def get_cursor(self):
return self.conn.cursor()
def count(self, table, where, values=[]):
return self.select_one('count(*)', table, where, values=values)
def select(self, what, table, where='', values=[]):
cur = self.conn.cursor()
query = '''
select %s
from %s
''' % (what, table)
if where != '':
query += 'where %s' % (where)
cur.execute(query, values)
results = []
for result in cur:
results.append(result)
cur.close()
return results
def select_one(self, what, table, where='', values=[]):
cur = self.conn.cursor()
if where != '':
where = 'where %s' % where
query = '''
select %s
from %s
%s
''' % (what, table, where)
execur = cur.execute(query, values)
one = execur.fetchone()
cur.close()
return one[0]
def execute(self, statement):
cur = self.conn.cursor()
result = cur.execute(statement)
return result
def get_config(self, key):
cur = self.conn.cursor()
query = '''
select value
from config
where key = "%s"
''' % key
try:
execur = cur.execute(query)
result = execur.fetchone()[0]
cur.close()
except Exception, e:
self.debug('failed to get config key "%s": %s' % (key, str(e)))
return None
return result
def set_config(self, key, value):
cur = self.conn.cursor()
query = '''
insert or replace into config (key, value)
values ("%s", "%s")
''' % (key, value)
try:
execur = cur.execute(query)
result = execur.fetchone()
self.commit()
cur.close()
except Exception, e:
self.debug('failed to set config key "%s" to value "%s": %s' % (key, value, str(e)))
if __name__ == '__main__':
db = DB()
try: db.add_user('4_pr0n')
except: pass
db.set_last_since_id('4_pr0n', 'ccs4ule')
print db.get_last_since_id('4_pr0n')