class DBManager(QObject):
'''
Manages a connection and initialising tables of an SQLite database
'''
def __init__(self, DatabaseName: str):
'''
Constructor: Initialises a connection to the database
'''
super.__init__(self)
self._schema = {}
self._db = QSqlDatabase(QSqlDriver.SQLite)
self._db.setDatabaseName(DatabaseName)
if not self._db.open():
qCritical("Failed to establish connection with database " +
DatabaseName)
return
self._initSchema()
def _initSchema(self):
'''
Initialises the table schema of the database
'''
for tablename in self._db.tables():
record = self._db.record(tablename)
self._schema[tablename] = record
if __name__=='__main__':
import sys
app=QApplication(sys.argv)
db=QSqlDatabase("QMYSQL")
db.setHostName('57af503e35be5.gz.cdb.myqcloud.com')
db.setDatabaseName('gstar')
db.setUserName('root')
db.setPassword('root123456*')
db.setPort(4402)
if not db.open():
print('open error')
else:
print ('open success!')
print ('db is open', db.isOpen())
print ("db's driver name is", db.driverName())
print ("db's tables", db.tables())
query=QSqlQuery(db)
query.prepare(u"SELECT * FROM material")
s=query.exec_()
while query.next():
print (query.value(0))
print (s)
class DatabaseLogLite(object):
""" Low load only; using SQLite
To store bookmarks, configuration, etc.
AB01 CFG02
"""
def __init__(self):
# ###### STARTUP
super(DatabaseLogLite, self).__init__()
self.litedb = QSqlDatabase("QSQLITE")
db_file = expanduser("~/.eilat/eilat.db")
rebuild = not isfile(db_file)
self.litedb.setDatabaseName(db_file)
self.litedb.open()
if rebuild:
query_mknav = (
"CREATE TABLE navigation (host TEXT NOT NULL," +
" path TEXT, count INTEGER default 0, prefix char(2)," +
" PRIMARY KEY (host, path))")
self.litedb.exec_(query_mknav)
# ###### VALIDATION
# verifies database structure, not datatypes
tables = self.litedb.tables()
tables_ok = [k in tables for k in ['navigation']]
if not all(tables_ok):
raise RuntimeError("tables missing from database")
fnav_ok = [self.litedb.record('navigation').contains(k)
for k in ['host', 'path', 'count', 'prefix']]
if not all(fnav_ok):
raise RuntimeError("bad structure for 'navigation' table")
def model(self, prefix=None):
""" recreate the model each call; opening a new window will not
be needed to use the recent completions
"""
if prefix is None:
query_nav = QSqlQuery(
"select host || path from navigation " +
"order by count desc",
self.litedb)
else: # CFG02
query_nav = QSqlQuery(
"select host || path from navigation " +
"where prefix = '{}' ".format(prefix) +
"order by count desc",
self.litedb)
ret_model = QSqlQueryModel()
ret_model.setQuery(query_nav) # AB01
return ret_model
def store_navigation(self, host, path, prefix):
""" save host, path and increase its count AB01 """
host = host.replace("'", "%27")
path = path.replace("'", "%27")
insert_or_ignore = (
"insert or ignore into navigation (host, path, prefix) " +
"values ('{}', '{}', '{}')".format(host, path, prefix))
update = (
"update navigation set count = count + 1 where " +
"host = '{}' and path = '{}'".format(host, path))
self.litedb.exec_(insert_or_ignore)
self.litedb.exec_(update)
class DatabaseLogLite(object):
""" Low load only; using SQLite
To store bookmarks, configuration, etc.
AB01 CFG02
"""
def __init__(self):
# ###### STARTUP
super(DatabaseLogLite, self).__init__()
self.litedb = QSqlDatabase("QSQLITE")
db_file = expanduser("~/.eilat/eilat.db")
rebuild = not isfile(db_file)
self.litedb.setDatabaseName(db_file)
self.litedb.open()
if rebuild:
query_mknav = (
"CREATE TABLE navigation (host TEXT NOT NULL," +
" path TEXT, count INTEGER default 0, prefix char(2)," +
" PRIMARY KEY (host, path))")
self.litedb.exec_(query_mknav)
# ###### VALIDATION
# verifies database structure, not datatypes
tables = self.litedb.tables()
tables_ok = [k in tables for k in ['navigation']]
if not all(tables_ok):
raise RuntimeError("tables missing from database")
fnav_ok = [
self.litedb.record('navigation').contains(k)
for k in ['host', 'path', 'count', 'prefix']
]
if not all(fnav_ok):
raise RuntimeError("bad structure for 'navigation' table")
def model(self, prefix=None):
""" recreate the model each call; opening a new window will not
be needed to use the recent completions
"""
if prefix is None:
query_nav = QSqlQuery(
"select host || path from navigation " + "order by count desc",
self.litedb)
else: # CFG02
query_nav = QSqlQuery(
"select host || path from navigation " +
"where prefix = '{}' ".format(prefix) + "order by count desc",
self.litedb)
ret_model = QSqlQueryModel()
ret_model.setQuery(query_nav) # AB01
return ret_model
def store_navigation(self, host, path, prefix):
""" save host, path and increase its count AB01 """
host = host.replace("'", "%27")
path = path.replace("'", "%27")
insert_or_ignore = (
"insert or ignore into navigation (host, path, prefix) " +
"values ('{}', '{}', '{}')".format(host, path, prefix))
update = ("update navigation set count = count + 1 where " +
"host = '{}' and path = '{}'".format(host, path))
self.litedb.exec_(insert_or_ignore)
self.litedb.exec_(update)
