def addConnection(self, driver, dbName, user, password, host, port):
"""
Public method to add a database connection.
@param driver name of the Qt database driver (string)
@param dbName name of the database (string)
@param user user name (string)
@param password password (string)
@param host host name (string)
@param port port number (integer)
@return SQL error object (QSqlError)
"""
err = QSqlError()
self.__class__.cCount += 1
db = QSqlDatabase.addDatabase(driver.upper(), "Browser{0:d}".format(self.__class__.cCount))
db.setDatabaseName(dbName)
db.setHostName(host)
db.setPort(port)
if not db.open(user, password):
err = db.lastError()
db = QSqlDatabase()
QSqlDatabase.removeDatabase("Browser{0:d}".format(self.__class__.cCount))
self.connections.refresh()
return err
def __init__(self, messenger: Messenger, backend: str, db_name: str) -> None:
#print("connecting to DB %s" % db_name)
self.messenger = messenger
self.backend = backend
if backend == 'PGSQL':
self.database = QSqlDatabase.addDatabase("QPSQL7", db_name) #TODO find out if I want QPSQL or QPSQL7
self.database.setHostName("localhost")
self.database.setDatabaseName(db_name)
self.database.setUserName("postgres")
#self.database.setPassword("")
elif backend == 'SQLITE':
self.database = QSqlDatabase.addDatabase("QSQLITE", db_name)
self.database.setDatabaseName('%s.sqlite3' % db_name)
else:
self.messenger.message_fatal("invalid DB backend: %s" % backend)
connected = False
ok = self.database.open()
if not ok:
self.messenger.message_fatal("DB open error:" + self.database.lastError().text())
else:
self.cursor = QSqlQuery(self.database)
ok = self.cursor.exec_()
#self.execute('SET time_zone = "+00:00";')
self.messenger.message_debug("Connected to DB %s" % db_name)
connected = True
def refresh(self):
"""
Public slot to refresh the connection tree.
"""
self.__connectionTree.clear()
self.cleared.emit()
connectionNames = QSqlDatabase.connectionNames()
foundActiveDb = False
for name in connectionNames:
root = QTreeWidgetItem(self.__connectionTree)
db = QSqlDatabase.database(name, False)
root.setText(0, self.__dbCaption(db))
if name == self.__activeDb:
foundActiveDb = True
self.__setActive(root)
if db.isOpen():
tables = db.tables()
for table in tables:
itm = QTreeWidgetItem(root)
itm.setText(0, table)
if not foundActiveDb and connectionNames:
self.__activeDb = connectionNames[0]
self.__setActive(self.__connectionTree.topLevelItem(0))
def read_database(self, file_name: str):
# noinspection PyTypeChecker,PyCallByClass
self.database = QSqlDatabase.addDatabase('QSQLITE', file_name)
if self.database.lastError().type() != QSqlError.NoError:
raise SqlError(
self.database.lastError()
)
def createConnection():
db = QSqlDatabase.addDatabase('QSQLITE')
db.setDatabaseName('poop.db') #to have only in memory make this (':memory:')
if not db.open():
QMessageBox.critical(None, "Cannot open database",
"Unable to establish a database connection.\n"
"This example needs SQLite support. Please read the Qt SQL "
"driver documentation for information how to build it.\n\n"
"Click Cancel to exit.",
QMessageBox.Cancel)
return False
query = QSqlQuery()
# query.exec_("create table vgenesdb(id int primary key, "
# "seqname varchar(20), sequence varchar(500))")
# query.exec_("create table vgenesdb(id integer, seqname text, sequence text)")
# query.exec_('drop table if exists vgenesdb')
# query.exec_("create table vgenesdb(id integer, SeqName text, DNA text, Protein text)") #, SeqLen integer, GeneType text, Isotype text, Vgene1 text, Vgene2 text, Vgene3 text, Dgene1 text, Dgene2 text, Dgene3 text, Jgene1 text, Jgene2 text, Jgene3 text, StopCodon text, ReadingFrame integer, Productive text, Strand text, VSeqEnd text, VDJunction text, Dregion text, DJJunction text, Jseqbeg text, VJjunction text, FR1from integer, FR1To integer, FR1length integer, FR1matched integer, FR1mis integer, FR1gaps integer, FR1PercentID real, FR2from integer, FR2To integer, FR2length integer, FR2matched integer, FR2mis integer, FR2gaps integer, FR2PercentID real, FR3from integer, FR3To integer, FR3length integer, FR3matched integer, FR3mis integer, FR3gaps integer, FR3PercentID real, CDR1from integer, CDR1To integer, CDR1length integer, CDR1matched integer, CDR1mis integer, CDr1gaps integer, CDR1PercentID real, CDR2from integer, CDR2To integer, CDR2length integer, CDR2matched integer, CDR2mis integer, CDR2gaps integer, CDR2PercentID real, IgBlastAlignMent text, TotMut integer, PercentIdentity real, CDR3DNA text, CDR3AA text, CDR3Length integer, CDR3pI real, CDR3MolWgt real, Specificity text, Species text, Project text, GroupID text, SubGroup text, ClonalPool integer, ClonalRank integer, GermlineSequence text, VLocus text, JLocus text, DLocus text, DateEntered text, Comments text, Quality text, InsDel1 text, InsDel2 text)")
# query.exec_('delete from vgenesdb where SeqName = TestSeq')
# query.exec_("insert into vgenesdb values(102, 'fromOther', 'NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNTCCCTGAGACTCTCCTGTGAAGCCTCTGGATTCACGTTTGGCGGCAATGGCATGAGCTGGGTCCGCCAGGCTCCAGGGAAGGGACTGGAGTGGGTCGCAGGTATCAGTGGTATTAGTGGGAATACATATTATTTAGGCTCCGTGAAGGGCCGGTTCACCATCTCCAGAGACAATCCGAAGAGGACGTTATATCTACAAATGAATCGTCTGAGAGTCGAGGACACGGCCATTTATTACTGTGCGAAAGATCGTTTGATAGGAACAGATGGCGTCTCTTTCTTTGACCAATGGGGCCAGGGAACCCTGGTCACCGTCTCCTCAG', 'test')")
# query.exec_(record)
return True
def __init__(self, parent=None):
"""
Constructor
@param parent reference to the parent widget (QWidget)
"""
super(SqlBrowserWidget, self).__init__(parent)
self.setupUi(self)
self.table.addAction(self.insertRowAction)
self.table.addAction(self.deleteRowAction)
if len(QSqlDatabase.drivers()) == 0:
E5MessageBox.information(
self,
self.tr("No database drivers found"),
self.tr(
"""This tool requires at least one Qt database driver. """
"""Please check the Qt documentation how to build the """
"""Qt SQL plugins."""
),
)
self.connections.tableActivated.connect(self.on_connections_tableActivated)
self.connections.schemaRequested.connect(self.on_connections_schemaRequested)
self.connections.cleared.connect(self.on_connections_cleared)
self.statusMessage.emit(self.tr("Ready"))
def dbConnect(self):
if self.checkFile(self.sDBF):
sConnection = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ={MS Access};DBQ="+self.sDBF+";PWD="+self.sPW
self.db = QSqlDatabase.addDatabase("QODBC")
self.db.setDatabaseName(sConnection)
print(sConnection)
if not self.db.open():
#catch db error
if self.db.lastError().number() == 0:
sErrMsg = "There is no error occured"
elif self.db.lastError().number() == 1:
sErrMsg = "Connection error"
elif self.db.lastError().number() == 2:
sErrMsg = "SQL statement syntax error"
elif self.db.lastError().number() == 3:
sErrMsg = "Transaction failed error"
elif self.db.lastError().number() == 4:
sErrMsg = "Unknown error"
else:
sErrMsg = "Unable to establish a database connection.\n" \
"This example needs database support. Please read the Qt SQL " \
"driver documentation for information how to build it.\n\n" \
"Click Cancel to exit."
QMessageBox.critical(None, "Open database",sErrMsg + ' ' + str(self.db.lastError().text()),QMessageBox.Cancel)
return False
#catch db error, return False
#no db error, return True
return True
def __init__( self, service, host, port, dbname, uid, pwd):
self.Fehler = None
# 19.07.18: GUID als Name, da unter QGIS3.2 ein Löschen der DB bei offenen Recordset zum Absturz führt
self.ConNameByGIUD=str(uuid.uuid4())
self.QSqlDB = QSqlDatabase.addDatabase("QPSQL", self.ConNameByGIUD )
"""
if service == "":
self.QSqlDB.setHostName(host)
self.QSqlDB.setPort(int(port))
else:
self.QSqlDB.setService(service)
self.QSqlDB.setDatabaseName(dbname)
self.QSqlDB.setUserName(uid)
self.QSqlDB.setPassword(pwd)
"""
# 19.08.18:
# irgendwie ist/war das doppelt, die Uri wird für andere Programmteile benötigt
# und in der alten Version wurde die auch zur DB-Anbindung genutzt: db.setConnectOptions( conninfo0 )
self.uri = QgsDataSourceUri()
if service == "":
self.uri.setConnection( host, port, dbname, uid, pwd )
else:
self.uri.setConnection(service, dbname, uid, pwd )
self.QSqlDB.setConnectOptions( self.uri.connectionInfo() )
def createConnection():
sFilename = "D:\pyrs.mdb"
if checkFile(sFilename):
#begin-if file exists then open db
#save to log-QMessageBox.information(None, "Check file", "File exists and is readable")
sPassword = "******"
sConnection = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ={MS Access};DBQ="+sFilename+";PWD="+sPassword
db = QSqlDatabase.addDatabase('QODBC')
db.setDatabaseName(sConnection)
if not db.open():
#catch db error
if db.lastError().number() == 0:
sErrMsg = "There is no error occured"
elif db.lastError().number() == 1:
sErrMsg = "Connection error"
elif db.lastError().number() == 2:
sErrMsg = "SQL statement syntax error"
elif db.lastError().number() == 3:
sErrMsg = "Transaction failed error"
elif db.lastError().number() == 4:
sErrMsg = "Unknown error"
else:
sErrMsg = "Unable to establish a database connection.\n" \
"This example needs database support. Please read the Qt SQL " \
"driver documentation for information how to build it.\n\n" \
"Click Cancel to exit."
QMessageBox.critical(None, "Open database",sErrMsg + ' ' + str(db.lastError().text()),QMessageBox.Cancel)
return False
#end catch db error
#end if file exists
#end file exists
return True
def __init__(self):
db = QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName("DB\db.sqlite")
if not db.open():
QMessageBox.critical(
None, "Cannot open database", "Unable to establish a database connection.", QMessageBox.Ok
)
def __init__(self, reference, parent=None):
super(Collection, self).__init__(parent)
self.reference = reference
self.db = QSqlDatabase.addDatabase('QSQLITE')
self._pages = None
self.fileName = None
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 create_db(self):
self.db = QSqlDatabase.addDatabase('QSQLITE')
self.db.setDatabaseName('sports.db')
if not self.db.open():
return False
query = QSqlQuery(db=self.db)
q1 = ("CREATE TABLE sentences(" +
"id INTEGER, " +
"content TEXT, " +
"page INTEGER, " +
"PRIMARY KEY(id))")
query.exec_(q1)
q2 = ("CREATE TABLE words(" +
"id INTEGER, " +
"word TEXT, " +
"sentence_id INTEGER, " +
"PRIMARY KEY(id), "
"FOREIGN KEY (sentence_id) REFERENCES sentences(id))")
query.exec_(q2)
q3 = ("CREATE TABLE domains(" +
"id INTEGER, " +
"name TEXT, " +
"word_id INTEGER, " +
"PRIMARY KEY(id), " +
"FOREIGN KEY (word_id) REFERENCES words(id))")
query.exec_(q3)
return True
def __init__(self, parent=None):
"""
Constructor
@param parent reference to the parent widget (QWidget)
"""
super(SqlConnectionDialog, self).__init__(parent)
self.setupUi(self)
self.databasePicker.setMode(E5PathPickerModes.OpenFileMode)
self.okButton = self.buttonBox.button(QDialogButtonBox.Ok)
drivers = QSqlDatabase.drivers()
# remove compatibility names
if "QMYSQL3" in drivers:
drivers.remove("QMYSQL3")
if "QOCI8" in drivers:
drivers.remove("QOCI8")
if "QODBC3" in drivers:
drivers.remove("QODBC3")
if "QPSQL7" in drivers:
drivers.remove("QPSQL7")
if "QTDS7" in drivers:
drivers.remove("QTDS7")
self.driverCombo.addItems(drivers)
self.__updateDialog()
msh = self.minimumSizeHint()
self.resize(max(self.width(), msh.width()), msh.height())
def open(self, path: str):
_translate = QCoreApplication.translate
self.file = QFile(path)
if self.file.exists():
file_path = self.file.fileName() # type: str
open_gnl_file_result = self.open_gnl_file(file_path, self.tmp_directory)
logger.debug(open_gnl_file_result)
logger.debug(isinstance(open_gnl_file_result, ZippedDocument))
if isinstance(open_gnl_file_result, ZippedDocument):
zip_file = open_gnl_file_result # type: ZipFile
# noinspection PyTypeChecker,PyCallByClass
self.database = QSqlDatabase.cloneDatabase(
zip_file.database,
':memory:'
)
self.init_database()
zip_file.close()
self.document_created.emit()
else:
self.error_occurred.emit(open_gnl_file_result)
else:
self.file = None
selected_file_does_not_exist = _translate(
"DocumentService",
"The selected file ({}) does not exist."
)
self.error_occurred.emit(
DocumentError(
selected_file_does_not_exist.format(
path
)
)
)
def __init__(self, parent=None):
"""
Constructor
@param parent reference to the parent widget (QWidget)
"""
super(SqlConnectionDialog, self).__init__(parent)
self.setupUi(self)
self.databaseFileButton.setIcon(UI.PixmapCache.getIcon("open.png"))
self.databaseFileCompleter = E5FileCompleter()
self.okButton = self.buttonBox.button(QDialogButtonBox.Ok)
drivers = QSqlDatabase.drivers()
# remove compatibility names
if "QMYSQL3" in drivers:
drivers.remove("QMYSQL3")
if "QOCI8" in drivers:
drivers.remove("QOCI8")
if "QODBC3" in drivers:
drivers.remove("QODBC3")
if "QPSQL7" in drivers:
drivers.remove("QPSQL7")
if "QTDS7" in drivers:
drivers.remove("QTDS7")
self.driverCombo.addItems(drivers)
self.__updateDialog()
msh = self.minimumSizeHint()
self.resize(max(self.width(), msh.width()), msh.height())
def currentDatabase(self):
"""
Public method to get the current database.
@return reference to the current database (QSqlDatabase)
"""
return QSqlDatabase.database(self.__activeDb)
def createConnection(self):
self.con = QSqlDatabase.addDatabase('QODBC')
# self.con.setConnectOptions('CharSet="cp1251"')
self.con.setDatabaseName('farmadb_mdb')
if not self.con.open():
QMessageBox.critical(None, "Cannot open database", "Cannot open database.", QMessageBox.Cancel)
return False
return True
def __init__(self, parent=None):
super(MainDatabase, self).__init__(parent)
self._database = QSqlDatabase('QSQLITE')
self._databaseName = ''
self._databaseQuery = ''
self._model = ''
self._databaseQueryResult = []
def createConnection():
db = QSqlDatabase.addDatabase('QODBC')
db.setDatabaseName('farmadb_mdb')
if not db.open():
QMessageBox.critical(None, "Cannot open database",
"Unable to establish a database connection.",
QMessageBox.Cancel)
return False
return True
def init(db_path):
"""Initialize the SQL database connection."""
database = QSqlDatabase.addDatabase('QSQLITE')
if not database.isValid():
raise SqlException('Failed to add database. '
'Are sqlite and Qt sqlite support installed?')
database.setDatabaseName(db_path)
if not database.open():
raise SqlException("Failed to open sqlite database at {}: {}"
.format(db_path, database.lastError().text()))
def openDataBase():
db = QSqlDatabase.addDatabase('QSQLITE')
dataPath = QStandardPaths.writableLocation(QStandardPaths.AppLocalDataLocation)
if not QDir(dataPath).mkpath('.'):
raise Exception('Failed to create data directory {}'.format(dataPath))
dbPath = dataPath + '/infinitecopy_items.sql'
print('Using item database "{}".'.format(dbPath))
db.setDatabaseName(dbPath)
db.open()
def createDB():
# 添加数据库
db = QSqlDatabase.addDatabase('QSQLITE')
# 设置数据库名称
db.setDatabaseName('./db/database.db')
# 判断是否打开
if not db.open():
QMessageBox.critical(None, ("Cannot open database"),
("Unable to establish a database connection. \n"
"This example needs SQLite support. Please read "
"the Qt SQL driver documentation for information "
"how to build it.\n\n"
"Click Cancel to exit."),
QMessageBox.Cancel)
return False
# 声明数据库查询对象
query = QSqlQuery()
# 创建表
query.exec("create table student(id int primary key, name vchar, sex vchar, age int, deparment vchar)")
#添加记录
query.exec("insert into student values(1,'张三1','男',20,'计算机')")
query.exec("insert into student values(2,'李四1','男',19,'经管')")
query.exec("insert into student values(3,'王五1','男',22,'机械')")
query.exec("insert into student values(4,'赵六1','男',21,'法律')")
query.exec("insert into student values(5,'小明1','男',20,'英语')")
query.exec("insert into student values(6,'小李1','女',19,'计算机')")
query.exec("insert into student values(7,'小张1','男',20,'机械')")
query.exec("insert into student values(8,'小刚1','男',19,'经管')")
query.exec("insert into student values(9,'张三2','男',21,'计算机')")
query.exec("insert into student values(10,'张三3','女',20,'法律')")
query.exec("insert into student values(11,'王五2','男',19,'经管')")
query.exec("insert into student values(12,'张三4','男',20,'计算机')")
query.exec("insert into student values(13,'小李2','男',20,'机械')")
query.exec("insert into student values(14,'李四2','女',19,'经管')")
query.exec("insert into student values(15,'赵六3','男',21,'英语')")
query.exec("insert into student values(16,'李四2','男',19,'法律')")
query.exec("insert into student values(17,'小张2','女',22,'经管')")
query.exec("insert into student values(18,'李四3','男',21,'英语')")
query.exec("insert into student values(19,'小李3','女',19,'法律')")
query.exec("insert into student values(20,'王五3','女',20,'机械')")
query.exec("insert into student values(21,'张三4','男',22,'计算机')")
query.exec("insert into student values(22,'小李2','男',20,'法律')")
query.exec("insert into student values(23,'张三5','男',19,'经管')")
query.exec("insert into student values(24,'小张3','女',20,'计算机')")
query.exec("insert into student values(25,'李四4','男',22,'英语')")
query.exec("insert into student values(26,'赵六2','男',20,'机械')")
query.exec("insert into student values(27,'小李3','女',19,'英语')")
query.exec("insert into student values(28,'王五4','男',21,'经管')")
# 关闭数据库
db.close()
return True
def version():
"""Return the sqlite version string."""
try:
if not QSqlDatabase.database().isOpen():
init(':memory:')
ver = Query("select sqlite_version()").run().value()
close()
return ver
return Query("select sqlite_version()").run().value()
except SqlError as e:
return 'UNAVAILABLE ({})'.format(e)
def get_model(self):
self.db = QSqlDatabase.addDatabase('QSQLITE')
self.db.setDatabaseName('sports.db')
self.db.open()
model = QSqlQueryModel()
query_str = """ select name, count(name) as counts from domains group by name order by counts desc
"""
model.setQuery(query_str, db=self.db)
model.setHeaderData(0, Qt.Horizontal, "Word")
model.setHeaderData(1, Qt.Horizontal, "Count")
return model
def init(db_path):
"""Initialize the SQL database connection."""
database = QSqlDatabase.addDatabase('QSQLITE')
if not database.isValid():
raise SqlError('Failed to add database. '
'Are sqlite and Qt sqlite support installed?',
environmental=True)
database.setDatabaseName(db_path)
if not database.open():
error = database.lastError()
raise SqliteError("Failed to open sqlite database at {}: {}"
.format(db_path, error.text()), error)
def song_delete(self):
index = self.ui.tableView_Songs.selectedIndexes()
if len(index) > 0:
if QMessageBox.question(None,
"Confirm delete",
"Are you sure to delete '%s' song?" % index[0].sibling(index[0].row(), 1).data(),
QMessageBox.Yes | QMessageBox.No) == QMessageBox.Yes:
assert isinstance(index[0], QModelIndex)
row_id = index[0].sibling(index[0].row(), 0).data()
db = QSqlDatabase()
current_db = db.database()
assert isinstance(current_db, QSqlDatabase)
current_db.transaction()
error_occurred = False
error_text = ""
song_artist_query = QSqlQuery()
song_artist_query.prepare('DELETE FROM t_song_artist WHERE id_song = ' + str(row_id))
if not song_artist_query.exec_():
error_occurred = True
error_text = song_artist_query.lastError().text()
song_query = QSqlQuery()
song_query.prepare('DELETE FROM t_song WHERE id = ' + str(row_id))
if not song_query.exec_():
error_occurred = True
error_text = song_query.lastError().text()
if not error_occurred:
current_db.commit()
self.fill_songs_table()
else:
current_db.rollback()
QMessageBox.critical(None,
"Database transaction failed",
"The database reported an error: %s" % error_text,
QMessageBox.Ok)
def __init__(self, querystr, forward_only=True):
"""Prepare a new sql query.
Args:
querystr: String to prepare query from.
forward_only: Optimization for queries that will only step forward.
Must be false for completion queries.
"""
super().__init__(QSqlDatabase.database())
log.sql.debug('Preparing SQL query: "{}"'.format(querystr))
if not self.prepare(querystr):
raise SqliteError.from_query('prepare', querystr, self.lastError())
self.setForwardOnly(forward_only)
def __uiStartUp(self):
"""
Private slot to do some actions after the UI has started and the main
loop is up.
"""
for warning in self.__warnings:
E5MessageBox.warning(
self,
self.tr("SQL Browser startup problem"),
warning)
if len(QSqlDatabase.connectionNames()) == 0:
self.__browser.addConnectionByDialog()
def __init__(self, querystr, forward_only=True):
"""Prepare a new SQL query.
Args:
querystr: String to prepare query from.
forward_only: Optimization for queries that will only step forward.
Must be false for completion queries.
"""
self.query = QSqlQuery(QSqlDatabase.database())
log.sql.vdebug(f'Preparing: {querystr}') # type: ignore[attr-defined]
ok = self.query.prepare(querystr)
self._check_ok('prepare', ok)
self.query.setForwardOnly(forward_only)
def __init__(self, db_name, table_name):
super().__init__()
# uic.loadUi('tablesEditForm.ui', self)
self.setupUi(self)
self.close_btn.clicked.connect(self.close)
self.delete_btn.clicked.connect(self.delete_item)
self.add_btn.clicked.connect(self.add_item)
self.db = QSqlDatabase.addDatabase('QSQLITE')
self.db.setDatabaseName(db_name)
self.db.open()
self.db_model = QSqlRelationalTableModel(self)
self.db_model.setTable(table_name)
self.db_model.select()
self.tableView.setModel(self.db_model)
def migrate_database():
initial_migration_level = get_migration_level()
migration_paths = glob(os.path.join(config.MIGRATIONS_PATH, "*.sql"))
for migration in map(lambda p: Migration(p), sorted(migration_paths)):
if migration.already_performed(initial_migration_level):
continue
with open(migration.path, "r") as f:
QSqlDatabase.database().transaction()
log_attempting_migration(migration)
for statement in f.read().split(";"):
query = QSqlQuery()
if not query.exec_(statement):
fail_migration(query, migration)
update_schema_migrations_level(migration)
QSqlDatabase.database().commit()
log_successful_migration(migration)
def addAsset(self):
row = (self.assetView.currentIndex().row()
if self.assetView.currentIndex().isValid() else 0)
QSqlDatabase.database().transaction()
self.assetModel.insertRow(row)
index = self.assetModel.index(row, NAME)
self.assetView.setCurrentIndex(index)
assetid = 1
query = QSqlQuery()
query.exec_("SELECT MAX(id) FROM assets")
if query.next():
assetid = query.value(0)
query.prepare("INSERT INTO logs (assetid, date, actionid) "
"VALUES (:assetid, :date, :actionid)")
query.bindValue(":assetid", assetid + 1)
query.bindValue(":date", QDate.currentDate())
query.bindValue(":actionid", ACQUIRED)
query.exec_()
QSqlDatabase.database().commit()
#self.logModel.select()
self.assetView.edit(index)
def init(db_path):
"""Initialize the SQL database connection."""
database = QSqlDatabase.addDatabase('QSQLITE')
if not database.isValid():
raise SqlError(
'Failed to add database. '
'Are sqlite and Qt sqlite support installed?',
environmental=True)
database.setDatabaseName(db_path)
if not database.open():
error = database.lastError()
raise SqliteError(
"Failed to open sqlite database at {}: {}".format(
db_path, error.text()), error)
def __init__(self, querystr, forward_only=True):
"""Prepare a new sql query.
Args:
querystr: String to prepare query from.
forward_only: Optimization for queries that will only step forward.
Must be false for completion queries.
"""
self.query = QSqlQuery(QSqlDatabase.database())
log.sql.debug('Preparing SQL query: "{}"'.format(querystr))
ok = self.query.prepare(querystr)
self._check_ok('prepare', ok)
self.query.setForwardOnly(forward_only)
def createConnection(databaseName):
# Create and open a database connection.
connection = QSqlDatabase.addDatabase("QSQLITE")
connection.setDatabaseName(databaseName)
if not connection.open():
QMessageBox.warning(
None,
"ContactBook",
f"Database Error: {connection.lastError().text()}",
)
return False
_createContactsTable()
return True
def __init_db(self):
self.__db = QSqlDatabase.addDatabase(Config.DB_TYPE)
self.db.setDatabaseName(self.__db_path)
if not self.db.isValid():
Tools.write_log(self.db.lastError().text())
if self.db.isOpenError():
Tools.write_log(self.db.lastError().text())
self.db.open()
tables = self.db.tables()
if not tables:
self.__create_db()
def createConnection(self, DBFilename):
db = QSqlDatabase.addDatabase('QSQLITE')
# db.setDatabaseName(':memory:')
db.setDatabaseName(DBFilename)
if not db.open():
QMessageBox.critical(None, "Cannot open database",
"Unable to establish a database connection.\n",
QMessageBox.Cancel)
return False
return True
def __init__(self, db_path: Path):
super(QDialog, self).__init__()
self.ui = Ui_SelectAircraftDialog()
self.ui.setupUi(self)
self.db = QSqlDatabase.addDatabase("QSQLITE")
self.db.setDatabaseName(str(db_path))
assert self.db.open()
self.selected_status = self.STATUSES[0]
self.ui.cbStatus.addItems(self.STATUSES)
self.selected_aircraft = None
self.selected_directory = None
def addTable(self, database):
""" This function creates the table widget, checks the
Database connection and run a SQL Query in function of
the database name"""
self.tableWidget = QTableView()
self.tableWidget.setStyleSheet(
"font-family: arial; background-color: #F8F8FF;")
# Checking connection
if QSqlDatabase.contains():
db = QSqlDatabase.database()
db.setDatabaseName('database.sqlite')
db.open()
else:
db = QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName('database.sqlite')
db.open()
# Setting the SQL Query
model = QSqlQueryModel()
model.setQuery(
f'''SELECT id, date, concept, value
FROM {database}''', db)
# Modeling and setting the Widget Position in the grid
self.tableWidget.setModel(model)
self.mainLayout.addWidget(self.tableWidget, 5, 0, 1, 3)
def display():
app = QApplication(sys.argv)
db = QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName("proto-base.db")
db.open()
projectModel = QSqlQueryModel()
projectModel.setQuery("select * from test",db)
projectView = QTableView()
projectView.setModel(projectModel)
projectView.show()
app.exec_()
def __init__(self, cesta_nazvu):
super().__init__()
self.setupUi(self)
self.cesta_nazvu=cesta_nazvu
#otevreni databaze
db = QSqlDatabase.addDatabase("QSQLITE","db")
db.setDatabaseName(cesta_nazvu)
db.open()
# vytvori model databaza a nacte data
projectModel = QSqlQueryModel()
projectModel.setQuery('select cb,X,Y,Z,kod from gps_sour',db)
# projectModel.setQuery('select Stanovisko,Orientace,Delka,Zenitka,Smer, Kod from mereni',db)
self.columnView.setModel(projectModel)
self.columnView.setColumnWidth(0,1)
db.close()
del db
del projectModel
QSqlDatabase.removeDatabase("db")
self.show()
self.exec()
def carregarTable(self):
db = QSqlDatabase ().addDatabase ('QSQLITE')
db.setDatabaseName ('Litterarius.db')
conexao = db.connectionName ()
if db.open ():
query = QSqlQueryModel (self)
query.setQuery ("SELECT"
" titulo, editoras.editora,"
" qtde_estoque, vl_unitario, consignado"
" FROM livros"
" INNER JOIN editoras ON livros.editoras_fk = editoras.editoras_id")
model = QSqlTableModel (self, db)
model.setQuery (query.query ())
model.select ()
self.ui.tableView.setModel (model)
self.ui.tableView.show ()
db.close()
def create_connection():
"""Establish a database connection."""
db = QSqlDatabase.addDatabase('QSQLITE')
db.setDatabaseName(':memory:')
if not db.open():
QMessageBox.critical(
None, "Cannot open database",
"Unable to establish a database connection.\n\n"
"Click Cancel to exit.", QMessageBox.Cancel)
return False
query = QSqlQuery()
query.exec_("create table subtitle(id integer primary key, "
"filename varchar(20), count int, time int, frequency int)")
return True
def init_database(self):
self.db = QSqlDatabase.addDatabase('QSQLITE')
self.db.setDatabaseName(self.db_name)
self.db.open()
self.model = QSqlTableModel(self, self.db)
self.model.setQuery(QSqlQuery("""SELECT sorts.sort AS 'Сорт', roastings.name AS 'Обжарка',
conditions.name AS 'Состояние',
sorts.taste AS 'Вкус', sorts.price AS 'Цена, руб',
sorts.size AS 'Объём, грамм'
FROM roastings, conditions JOIN sorts
ON roastings.id = sorts.roasting
AND conditions.id = sorts.condition""", self.db))
self.model.select()
self.tableView.setModel(self.model)
self.tableView.setEditTriggers(QAbstractItemView.NoEditTriggers)
def initialize(self, dbtype=DB_TYPE_MEMORY, dbfile=DB_IN_MEMORY):
if dbtype != Database.DB_TYPE_MEMORY:
self.db_file = dbfile
if self.db != None:
QSqlDatabase.removeDatabase(self.db_name)
self.close()
self.db = QSqlDatabase.addDatabase("QSQLITE", self.db_name)
self.db.setDatabaseName(self.db_file)
if not self.db.open():
print(
"\n ** Error opening DB: SQLite driver not loaded. DB name: %s\n"
% self.db_file)
print("\n Available drivers: ", QSqlDatabase.drivers())
sys.exit(-1)
db_status, db_error = self.is_db_ok()
if db_status is False:
print("db.initialize() error:", db_error)
return False, db_error
self._create_tables()
return True, None
def search(self, id):
self.db = QSqlDatabase.addDatabase('QSQLITE')
self.db.setDatabaseName('databases/goods.sqlite')
self.db.open()
q = QSqlQuery(self.db)
q.exec_(f"select id, name, price from goods where id={id}")
# rec = q.record()
info = []
if q.next():
for i in range(3):
info.append(q.value(i))
return info
def __init__(self, libraryPath, parent=None, db=QSqlDatabase()):
super(desuplayerModel, self).__init__(parent, db)
self.setTable('tracks')
self.setEditStrategy(QSqlTableModel.OnFieldChange)
self.select()
self._lock_edits = True
for index, name in enumerate(headers):
self.setHeaderData(index, Qt.Horizontal, name)
self.libraryPath = libraryPath
self.sortColumn = -1
self.sortOrder = None
def insert_default_values(db_name, username, password):
"""We can use statements, but I prefer to use python API.
"""
db = db_open(username=username, password=password)
insert(db, 'protocols', {'protocol': '07-260_bcipatients'})
insert(db, 'protocols', {'protocol': '14-090_children'})
insert(db, 'protocols', {'protocol': '14-420_adults'})
insert(db, 'protocols', {'protocol': 'ORCHIID'})
insert(db, 'experimenters', {'experimenter': 'Mariska'})
insert(db, 'experimenters', {'experimenter': 'Erik'})
insert(db, 'experimenters', {'experimenter': 'Giovanni'})
insert(db, 'experimenters', {'experimenter': 'Mariana'})
insert(db, 'experimenters', {'experimenter': 'Elmar'})
insert(db, 'aliases', {'person': 1, 'code': 'guij'})
insert(db, 'aliases', {'person': 1, 'code': 'gennep'})
insert(db, 'aliases', {'person': 2, 'code': 'meppel'})
insert(db, 'aliases', {'person': 2, 'code': 'marrum'})
db.close()
del db # delete database before removing connection
QSqlDatabase.removeDatabase(CONNECTION_NAME)
def connection():
con = QSqlDatabase.addDatabase('QSQLITE')
con.setDatabaseName("storage/" + db_name)
if not con.open():
QMessageBox.critical(
None,
"App Name - Error!",
"Database Error: %s" % con.lastError().databaseText(),
)
sys.exit(1)
if con.isOpen():
print('Connected.')
return con
def __init__(self, querystr, forward_only=True):
"""Prepare a new sql query.
Args:
querystr: String to prepare query from.
forward_only: Optimization for queries that will only step forward.
Must be false for completion queries.
"""
super().__init__(QSqlDatabase.database())
log.sql.debug('Preparing SQL query: "{}"'.format(querystr))
if not self.prepare(querystr):
raise SqlException('Failed to prepare query "{}": "{}"'.format(
querystr,
self.lastError().text()))
self.setForwardOnly(forward_only)
def __init__(self,
dbname: str,
username: str,
password: str,
port,
table_name,
hostname: str = "127.0.0.1"):
self.__db = QSqlDatabase.addDatabase("QPSQL")
self.__table_name = table_name
self.__hostname = hostname
self.__dbname = dbname
self.__port = port
self.__username = username
self.__password = password
self.__status = False
def createConnection():
connString = f'DRIVER={{SQL Server}};' \
f'SERVER={SERVER_NAME};' \
f'DATABASE={DATABASE_NAME}'
global db
db = QSqlDatabase.addDatabase('QODBC')
db.setDatabaseName(connString)
if db.open():
print('connect to SQL Server successfully')
return True
else:
print('connection failed')
return False
def initializedModel(self):
self.db = QSqlDatabase.addDatabase("QSQLITE")
self.db.setDatabaseName("project.db")
if not self.db.open():
return False
self.queryModel.setHeaderData(0, Qt.Horizontal, "Hospital Name")
self.queryModel.setHeaderData(1, Qt.Horizontal, "Address")
self.queryModel.setHeaderData(3, Qt.Horizontal, "City")
self.queryModel.setHeaderData(3, Qt.Horizontal, "State")
self.queryModel.setHeaderData(3, Qt.Horizontal, "Contact Details")
# Get all the records of the table
sql = "SELECT * FROM hospital"
self.queryModel.setQuery(sql, self.db)
self.totalRecordCount = self.queryModel.rowCount()
def createDB():
db = QSqlDatabase.addDatabase('QSQLITE')
# 指定SQLite数据库的文件名
db.setDatabaseName('./db/database.db')
if not db.open():
print('无法建立与数据库的连接')
return False
query = QSqlQuery()
query.exec(
'create table people(id int primary key,name varchar(10),address varchar(50))'
)
query.exec('insert into people values(1,"李宁","Shenyang")')
query.exec('insert into people values(2,"超人","克星")')
db.close()
return True
def createConnection():
db = QSqlDatabase.addDatabase('QSQLITE')
# Absolute path: is not working if the exe moved to another drive/folder -> fixed using relative path
# db.setDatabaseName("I:\_Python\PyQT\SiteandHorizon\\New\Database\SiteHorizon.db")
db.setDatabaseName("Database\SiteHorizon.db")
if not db.open():
QMessageBox.critical(
None, "Cannot open database",
"Unable to establish a database connection.\n"
"This example needs SQLite support. Please read the Qt SQL "
"driver documentation for information how to build it.\n\n"
"Click Cancel to exit.", QMessageBox.Cancel)
return False
return True
def createConnection(databaseName):
"""Crea y abre una conexión de base de datos.."""
connection = QSqlDatabase.addDatabase("QSQLITE")
connection.setDatabaseName(databaseName)
if not connection.open():
QMessageBox.warning(
None,
"Database de Contactos 2021",
f"Database Error: {connection.lastError().text()}",
)
return False
_createContactsTable()
return True
def connect_to_db():
logger = logging.getLogger(__name__)
logger.info('Connecting to DB: {}'.format(config.db_path()))
db = QSqlDatabase.addDatabase('QSQLITE')
db.setDatabaseName(config.db_path())
if not db.open():
QMessageBox.critical(
None,
'Cannot open database',
'Unable to open database\n\nClick Cancel to exit',
QMessageBox.Cancel
)
return db
def create_connection():
connection_string = f'DRIVER={{SQL Server}};' \
f'SERVER={SERVER_NAME};' \
f'DATABASE={DATABASE};'
global db
db = QSqlDatabase.addDatabase('QODBC')
db.setDatabaseName(connection_string)
if db.open():
print('Successful connection')
return True
else:
print('Connection error')
return False
def createConnection(self):
database = QSqlDatabase.addDatabase("QSQLITE") # SQLite version 3
database.setDatabaseName("files/accounts.db")
if not database.open():
print("Unable to open data source file.")
sys.exit(1) # Error code 1 - signifies error
# Check if the tables we need exist in the database
tables_needed = {'accounts', 'countries'}
tables_not_found = tables_needed - set(database.tables())
if tables_not_found:
QMessageBox.critical(None, 'Error',
f'The following tables tables are missing from the database: {tables_not_found}')
sys.exit(1) # Error code 1 - signifies error