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 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, reference, parent=None):
super(Collection, self).__init__(parent)
self.reference = reference
self.db = QSqlDatabase.addDatabase('QSQLITE')
self._pages = None
self.fileName = None
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, 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 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 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 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 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 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 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 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 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(db_path):
"""Initialize the SQL database connection."""
database = QSqlDatabase.addDatabase('QSQLITE')
if not database.isValid():
raise KnownError('Failed to add database. Are sqlite and Qt sqlite '
'support installed?')
database.setDatabaseName(db_path)
if not database.open():
error = database.lastError()
msg = "Failed to open sqlite database at {}: {}".format(db_path,
error.text())
raise_sqlite_error(msg, error)
# Enable write-ahead-logging and reduce disk write frequency
# see https://sqlite.org/pragma.html and issues #2930 and #3507
Query("PRAGMA journal_mode=WAL").run()
Query("PRAGMA synchronous=NORMAL").run()
def __init__(self):
super(QMainWindow, self).__init__()
self.ui = Ui_MainWindow()
self.ui.setupUi(self)
self.ui.txtNumber.setValidator(QIntValidator())
self.ui.txtName.textChanged.connect(self.onFilterChanged)
self.ui.txtNumber.textChanged.connect(self.onFilterChanged)
self.ui.btnNewMarble.clicked.connect(self.onNewMarble)
self.ui.btnDeleteMarble.clicked.connect(self.onDeleteMarble)
self.ui.btnSell.clicked.connect(self.onSell)
self.ui.lstMarble.doubleClicked.connect(self.onMarbleDoubleClicked)
self.ui.btnViewSales.clicked.connect(self.showSales)
self.queryModel = QSqlQueryModel()
self.ui.lstMarble.setModel(self.queryModel)
self.db = QSqlDatabase.addDatabase('QSQLITE')
self.db.setDatabaseName('inventory.db')
self.db.open()
self.execSql('''PRAGMA foreign_keys = ON;''')
self.execSql('''CREATE TABLE IF NOT EXISTS `slabs` (
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
`name` TEXT NOT NULL,
`num` INTEGER NOT NULL,
`amount` INTEGER NOT NULL,
`dimL` INTEGER NOT NULL,
`dimW` INTEGER NOT NULL,
`dimH` INTEGER NOT NULL,
`notes` TEXT NOT NULL
);''')
self.execSql('''CREATE TABLE IF NOT EXISTS `sales` (
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
`slabid` INTEGER NOT NULL,
`date` INTEGER NOT NULL,
`amount` INTEGER NOT NULL,
`leftover` INTEGER NOT NULL,
`buyer` TEXT NOT NULL,
`notes` TEXT NOT NULL,
FOREIGN KEY(slabid) REFERENCES slabs(id)
);''')
self.onFilterChanged()
self.ui.lstMarble.setColumnHidden(0, True)
def createTableAndInit():
# 添加数据库
db = QSqlDatabase.addDatabase('QSQLITE')
# 设置数据库名称
db.setDatabaseName('./db/database.db')
# 判断是否打开
if not db.open():
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,'经管')")
return True
def __init__(self, fields, parent=None, db=None):
super().__init__(parent)
if db:
self.db = db
else:
self.db = QSqlDatabase.addDatabase('QSQLITE', "reference")
self.fileName = None
self.userFields = [field.name for field in fields.userFields]
self.sections = []
ref_region = self.__createReferenceSection(None, fields.region)
ref_country = self.__createReferenceSection(ref_region, fields.country,
self.tr("C"), True)
self.__createReferenceSection(ref_country, fields.period,
self.tr("P"), True)
self.__createReferenceSection(ref_country, fields.emitent)
self.__createReferenceSection(ref_country, fields.ruler)
self.__createReferenceSection(ref_country, fields.unit, self.tr("U"))
self.__createReferenceSection(ref_country, fields.mint)
self.__createReferenceSection(ref_country, fields.series,
self.tr("S"), True)
self.__createReferenceSection(None, fields.grade, self.tr("G"))
self.__createReferenceSection(None, fields.material, self.tr("M"))
self.__createReferenceSection(None, fields.shape, self.tr("F"))
self.__createReferenceSection(None, fields.quality, self.tr("Q"))
self.__createReferenceSection(None, fields.rarity, self.tr("R"))
self.__createReferenceSection(None, fields.obvrev)
self.__createReferenceSection(None, fields.type, self.tr("T"))
self.__createReferenceSection(None, fields.defect, self.tr("D"))
self.__createReferenceSection(None, fields.format)
self.__createReferenceSection(None, fields.condition)
if 'payplace' in self.userFields or 'saleplace' in self.userFields:
ref_place = ReferenceSection('place', self.tr("Place"))
self.sections.append(ref_place)
if 'obversecolor' in self.userFields or 'reversecolor' in self.userFields:
ref_color = ReferenceSection('color', self.tr("Color"))
self.sections.append(ref_color)
if 'edge' in self.userFields or 'signaturetype' in self.userFields:
ref_edge = ReferenceSection('edge', self.tr("Edge"))
self.sections.append(ref_edge)
def openDatabase(databaseName='fmdb.sqlite'):
db = QSqlDatabase.addDatabase('QSQLITE')
#TODO: make this a variable in the profile
dbPath = os.getcwd() + '/fm.sqlite'
db.setDatabaseName(dbPath)
if not db.open():
QMessageBox.critical(None," OMG ERROR! withz the database: " + databaseName,
QMessageBox.Cancel)
return False
query = QSqlQuery()
if not os.path.exists(dbPath):
for i in open(os.path.join(os.getcwd(), "misc/fakturabackup.sqlite")):
query.exec_(i)
result = query.exec_('select * from bill')
print(result)
return True
def createDB():
db = QSqlDatabase.addDatabase('QSQLITE')
db.setDatabaseName('./db/database.db')
if not db.open():
QMessageBox.critical(None, ("无法打开数据库"),
( "无法建立到数据库的连接,这个例子需要SQLite 支持，请检查数据库配置。\n\n"
"点击取消按钮退出应用。"),
QMessageBox.Cancel )
return False
query = QSqlQuery()
query.exec_("create table people(id int primary key, name varchar(20), address varchar(30))")
query.exec_("insert into people values(1, 'zhangsan1', 'BeiJing')")
query.exec_("insert into people values(2, 'lisi1', 'TianJing')")
query.exec_("insert into people values(3, 'wangwu1', 'HenNan')")
query.exec_("insert into people values(4, 'lisi2', 'HeBei')")
query.exec_("insert into people values(5, 'wangwu2', 'shanghai')")
db.close()
return True
def createConfiguration():
db = QSqlDatabase.addDatabase('QSQLITE')
db.setDatabaseName(':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 Sensorconfig(id int primary key, "
"name varchar(20), num integer, range integer, "
"angle integer, color varchar(20))")
query.exec_("insert into Sensorconfig values(001, 'sensor1', 30, 1, 60, 'blue')")
query.exec_("insert into Sensorconfig values(002, 'sensor2', 30, 1, 30, 'green')")
return True
def __init__(self):
db = QSqlDatabase.addDatabase('QSQLITE')
db.setDatabaseName('database\db.sqlite')
if not db.open():
QMessageBox.critical(None,
"Cannot open database",
"Unable to establish a database connection.",
QMessageBox.Ok)
else:
query = QSqlQuery()
query.prepare("PRAGMA foreign_keys = ON")
query.exec_()
query.prepare("PRAGMA foreign_keys")
query.exec_()
if query.last():
if query.value(0) != 1:
QMessageBox.critical(None,
"Cannot set database options",
"Foreign keys support not available.",
QMessageBox.Ok)
def __init__(self,dbPath,tblName,parent=None):
super(MainWindowView, self).__init__(parent)
self.setupUi(self)
self.addBtn.clicked.connect(self.addBtnFunc)
self.updBtn.clicked.connect(self.updBtnFunc)
self.delBtn.clicked.connect(self.delBtnFunc)
self.dbPath="test.db"
self.curTable="test2"
###tableView与model绑定
self.tableModel=QSqlTableModel(self,QSqlDatabase.addDatabase('QSQLITE'))
self.tableModel.setEditStrategy(QSqlTableModel.OnManualSubmit)
self.tableView.setModel(self.tableModel)
###self.model数据初始化
self.tableModel.database().setDatabaseName(self.dbPath)
self.tableModel.database().open()
self.tableModel.setTable(self.curTable)
self.tableModel.select()
def __init__(self,parent=None):
logger.debug("_init__:begin")
super(WinsWidgetView, self).__init__(parent)
self.setupUi(self)
self.dbPath="test.db"
self.curTable="test2"
self.setObjectName('winsWidget')
self.setWindowFlags(Qt.FramelessWindowHint)
self.setWindowTitle('wins的小工具')
self.setWindowIcon(QIcon('icons/titleIcon.png'))
self.tableModel=QSqlTableModel(self,QSqlDatabase.addDatabase('QSQLITE'))
self.initThread()
self.set_buttons()
self.set_labels()
self.set_lines()
self.load_tableview()
self.init_bottom()
logger.debug("_init__:end")
def __init__(self, parent=None):
super(Reference, self).__init__(parent)
self.db = QSqlDatabase.addDatabase('QSQLITE', "reference")
ref_country = ReferenceSection('country', self.tr("Country"), self.tr("C"))
ref_type = ReferenceSection('type', self.tr("Type"), self.tr("T"))
ref_grade = ReferenceSection('grade', self.tr("Grade"), self.tr("G"))
ref_place = ReferenceSection('place', self.tr("Place"))
ref_material = ReferenceSection('material', self.tr("Material"), self.tr("M"))
ref_shape = ReferenceSection('shape', self.tr("Shape"), self.tr("F"))
ref_obvrev = ReferenceSection('obvrev', self.tr("ObvRev"))
ref_edge = ReferenceSection('edge', self.tr("Edge"), self.tr("E"))
ref_unit = CrossReferenceSection('unit', ref_country, self.tr("Unit"), self.tr("U"))
ref_mint = CrossReferenceSection('mint', ref_country, self.tr("Mint"))
ref_period = CrossReferenceSection('period', ref_country, self.tr("Period"), self.tr("P"))
ref_series = CrossReferenceSection('series', ref_country, self.tr("Series"), self.tr("S"))
ref_quality = ReferenceSection('quality', self.tr("Quality"), self.tr("Q"))
ref_defect = ReferenceSection('defect', self.tr("Defect"), self.tr("D"))
ref_rarity = ReferenceSection('rarity', self.tr("Rarity"), self.tr("R"))
self.sections = [
ref_grade,
ref_material,
ref_defect,
ref_shape,
ref_quality,
ref_country,
ref_rarity,
ref_edge,
ref_obvrev,
ref_type,
ref_unit,
ref_mint,
ref_period,
ref_series,
ref_place,
]
def setTableView(self):
print('*** step2 SetTableView' )
self.db = QSqlDatabase.addDatabase('QSQLITE')
# 设置数据库名称
self.db.setDatabaseName('./db/database.db')
# 打开数据库
self.db.open()
# 声明查询模型
self.queryModel = QSqlQueryModel(self)
# 设置当前页
self.currentPage = 1;
# 得到总记录数
self.totalRecrodCount = self.getTotalRecordCount()
# 得到总页数
self.totalPage = self.getPageCount()
# 刷新状态
self.updateStatus()
# 设置总页数文本
self.setTotalPageLabel()
# 设置总记录数
self.setTotalRecordLabel()
# 记录查询
self.recordQuery(0)
# 设置模型
self.tableView.setModel(self.queryModel)
print('totalRecrodCount=' + str(self.totalRecrodCount) )
print('totalPage=' + str(self.totalPage) )
# 设置表格表头
self.queryModel.setHeaderData(0,Qt.Horizontal,"编号")
self.queryModel.setHeaderData(1,Qt.Horizontal,"姓名")
self.queryModel.setHeaderData(2,Qt.Horizontal,"性别")
self.queryModel.setHeaderData(3,Qt.Horizontal,"年龄")
self.queryModel.setHeaderData(4,Qt.Horizontal,"院系")
def setUpConnect(self):
self.conn = sqlite3.connect('pyofsaledb.db')
self.cur = self.conn.cursor()
self.db = QSqlDatabase.addDatabase("QSQLITE")
self.db.setDatabaseName("pyofsaledb.db")
self.db.open()
def __init__(self, dbname):
self.db_connection = QSqlDatabase.addDatabase("QSQLITE")
self.db_connection.setDatabaseName(dbname)
self.db_connection.open()
self.DBquery = QSqlQuery()
print("[DELEGATE] Setting data to model at index %s..." % index.row())
value = editor.value()
variant = QVariant(value)
model.setData(index, variant)
class ReadOnlyDelegate(QItemDelegate):
def createEditor(self, parent, option, index):
lb = QLabel(parent)
return lb
if __name__ == '__main__':
app = QApplication(sys.argv)
db = QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName("db\database.db")
if not db.open():
sys.exit(-1)
model = ImportSqlTableModel()
delegate = CustomDelegate()
w1 = QTableView()
w1.setWindowTitle("QTableView #1")
w1.setModel(model)
w1.setItemDelegateForColumn(3, delegate)
w1.show()
w2 = QTableView()
w2.setWindowTitle("QTableView #2")
w2.setModel(model)
def db_connect(self):
self.db = QSqlDatabase.addDatabase('QSQLITE')
self.db.setDatabaseName('./test.db')
if not self.db.open():
QMessageBox.critical(self, 'Database Connection',
self.db.lastError().text())
"""
Wyszukuje po wszystkich kolumnach tabeli
:param text:
"""
search = QRegExp(text,
Qt.CaseInsensitive,
QRegExp.RegExp
)
self.proxy_p.setFilterRegExp(search)
# Odpowiedzialne za kolumnę, po której filtruje
self.proxy_p.setFilterKeyColumn(1)
if __name__ == '__main__':
# Parametry połączenia z bazą
db = QSqlDatabase.addDatabase('QMYSQL')
db.setHostName(HOST)
db.setDatabaseName("NC6H7fYEuE")
db.setUserName(USER)
db.setPassword(PASSWORD)
if db.open():
print('Otworzono bazę danych')
else:
print(db.lastError().text())
print(db.drivers())
app = QApplication(sys.argv)
Program = QWidget()
Program.setFixedSize(1000, 600)
ex = ServEmpl(Program, db)
ex.setFixedSize(1000, 600)
# -*- coding: utf-8 -*-
import sys, re
from PyQt5 import uic, QtWidgets
from PyQt5.QtWidgets import QMessageBox
from PyQt5.QtSql import QSqlDatabase, QSqlQuery, QSqlTableModel
db = QSqlDatabase.addDatabase(
'QMYSQL'
) #driver de la base de datos en este caso mysql tambien funciona para mariadb
db.setHostName(
"186.147.72.83"
) #servidor de conexion de la base de datos, por defecto el puerto es 3306
db.setDatabaseName("casino") #nombre de la base de datos
db.setUserName("usuariocasino") #usuario para conectarse a la base de datos
db.setPassword("casino") #contraseña del usuario para la base de datos
#validaciones de conexion a la base de datos
def insertarDatos(total_transaccion, detalle_transacion):
estado = db.open()
if estado == False:
print("no se pudo conectar")
#QMessageBox.warning("error",db.lastError().text(),QMessageBox.Discard)
print("error", db.lastError().text())
else:
print("conexion a base de datos correcta")
#total_transaccion=entradaganancia.text()
#aqui se hace una inserccion a la base da datos en la tabla transacion la tabla se define despues del "insert into "
sql = "INSERT INTO transacciones(total_transaccion,detalle_transacion) VALUES(:total_transaccion,:detalle_transacion)"
consulta = QSqlQuery()
consulta.prepare(sql)
from PyQt5.QtSql import QSqlDatabase
db = QSqlDatabase.addDatabase('QSQLITE')
db.setDatabaseName('d:/sqlite/mydb.db')
db.open()
def open_db(self):
db = QSqlDatabase.addDatabase('QSQLITE')
db.setDatabaseName(current_path + 'sfc.db')
db.open()
return db
