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)