class Editorial(object):
def __init__(self):
try:
self.db = Database(
None) #reading all connection data from config.ini
logging.debug('Database connected...')
except:
#if DB server is not accessible should wrap up an close connection here
logging.debug(
'Connection to DB failed, try running admin first...')
self.form = cgi.FieldStorage()
self.user = EditorialUser(self.form, self.db)
self.content = EditorialContent(self.form, self.user, self.db)
def getPage(self):
contenterror = ''
pwerror = ''
if self.user.verifyId(
): #check of username succeeded: prepare content page
if 'title' in self.form.keys(
): #if we are calling the cgi from the content page
contenterror = self.content.ecSubmit(
) #add to content table in DB
# and get message for pageupdate
self.content.updateNewsfile(
HTML_CONTENT_PATH) #update news.html
if 'newpass' in self.form.keys(
): #if we are calling the cgi from the content page AND have checked the changePW box
pwerror = self.user.updatePass(
self.form) #update PW in persons table in DB
self.db.closeDb() #DB server close
logging.debug('Branch1: Database connection closed...')
logging.debug('pwerror: {}'.format(pwerror))
logging.debug('contenterror: {}'.format(contenterror))
print(
HTMLTEMPLATE1.format(
self.user.name,
self.user.pw, #hidden form data
self.content.text, #text will be carried over
contenterror,
pwerror))
else: #bad username: prepare another login prompt
self.db.closeDb()
logging.debug('Branch2: Database connection closed...')
print(HTMLTEMPLATE2)
Common problems here:
http://initd.org/psycopg/docs/faq.html#faq-compile
Dynamic statements:
http://initd.org/psycopg/docs/sql.html#module-psycopg2.sql
'''
from lib.database import Database
class BookGetter:
def __init__(self, database):
self.db = database
def getBooks(self):
query = "SELECT id, title, author FROM books"
return self.db.fetchAll(query)
def getBook(self, id):
query = "SELECT id, title, author FROM books WHERE id = {}; ".format(
id)
return self.db.fetchOne(query)
db = Database()
bookGetter = BookGetter(db)
bookList = bookGetter.getBooks()
for book in bookList:
print(book['id'], '', book['title'])
db.closeDb()
#if DB server is not accessible should wrap up an close connection here
logging.debug('Connection to DB failed, try running admin first...')
ed_form = cgi.FieldStorage()
ed_user = EditorialUser(ed_form, ed_db)
ed_content = EditorialContent(ed_form, ed_user, ed_db)
contenterror = ''
pwerror = ''
if ed_user.verifyId(): #check username
if 'title' in ed_form.keys(): #if we are not coming from the login page
contenterror = ed_content.ecSubmit() #add to content table in DB
# and get message for pageupdate
ed_content.updateNewsfile(HTML_CONTENT_PATH) #update webpage
if 'newpass' in ed_form.keys(): #update persons table in DB
# and get message for pageupdate
pwerror = ed_user.updatePass(ed_form)
ed_db.closeDb() #DB server close
logging.debug('Branch1: Database connection closed...')
logging.debug('pwerror: {}'.format(pwerror))
logging.debug('contenterror: {}'.format(contenterror))
print(
HTMLTEMPLATE1.format(
ed_user.name,
ed_user.pw, #hidden form data
ed_content.text, #text will be carried over
contenterror,
pwerror))
else:
ed_db.closeDb() #DB server close
logging.debug('Branch2: Database connection closed...')
print(HTMLTEMPLATE2)
class Admin(object):
def __init__(self):
try:
self.db = Database(
None) #reading all connection data from config.ini
print('Database connected...')
except:
self.db = Database(
'postgres') #entering on master level using config.ini
self.db.execute(SQL_CREATE_DB)
self.db.closeDb()
self.db = Database(None) #now enter at db level using autocommit
print('Database created and connected...')
print('#' * LINEWIDTH)
#self.db.execute(SQL_DROP_1) #drop tables to start over
#self.db.execute(SQL_DROP_2)
#print('Tables dropped...')
#print('#'*LINEWIDTH)
try: #do both table exist?
self.db.fetchAll(SQL_SELECT_1)
print('Table persons exists...')
except:
self.db.execute(SQL_CREATE_1)
print('Table persons created...')
counter = self.getMaxUserId() #get highest user_id
#add new users based on highest id
for key, value in PERSONS.items(
): #add default rows to persons table
md5key = hashlib.md5(value[0].encode('utf-8'))
data = [key + counter, *value,
md5key.digest()
] #increment id, list unpacking, hashing
self.db.insert(SQL_INSERT_1, data)
print(data, 'inserted')
try:
self.db.fetchAll(SQL_SELECT_2)
print('Table content exists...')
except: #otherwise re-create them
self.db.execute(SQL_CREATE_2)
print('Table content created...')
data = [1, "Start of Journal", "Hello World", 1.0e20, 1]
self.db.insert(SQL_INSERT_2,
data) #add default entry to content table
#self.db.commit()
print('Table write successful...')
print('#' * LINEWIDTH)
result = self.db.fetchAll(SQL_SELECT_1)
print('List of users in DB:')
for id, name, alias, hash in list(result):
print(name, alias)
print('Output successful')
print('#' * LINEWIDTH)
newname = input('New User (exit with enter):')
counter = self.getMaxUserId()
while newname:
if list(self.db.fetchAll(SQL_SELECT_NAME, data=[newname])):
print('Name exists already...')
else:
newalias = input('Please enter also a new alias:')
if list(self.db.fetchAll(SQL_SELECT_ALIAS, data=[newalias])):
print('Alias exists already...')
else:
md5key = hashlib.md5(newname.encode('utf-8'))
counter += 1
#print(counter)
data = [counter, newname, newalias, md5key.digest()]
self.db.insert(SQL_INSERT_1, data)
print('New user {} created successfully'.format(newname))
newname = input('New User (exit with enter):')
print('Manual input completed')
print('#' * LINEWIDTH)
self.db.closeDb()
print('Connection closed')
print('#' * LINEWIDTH)
def getMaxUserId(self): #a non-existing table throws error
result = self.db.fetchOne("""SELECT MAX(user_id) FROM persons""")
#print(result[0])
if result[0] is None or len(
result
) == 0: #an empty table returns [None], empty list check is extra
return 0
else:
return result[0]
