def init(self) -> bool:
"""Create the Connection to the Database.
For the Moment just SQLite
Returns:
Return true is the database successfull initialized.
"""
self.database = QSqlDatabase.database()
if self.dbType == "sqlite":
if not self.database.isValid():
self.database = QSqlDatabase.addDatabase("QSQLITE")
if not self.database.isValid():
self.log.error("Cannot add database")
return False
self.database_name = self.__get_sqlite_name()
file = QFile(self.database_name)
if file.exists():
# When using the SQLite driver, open() will create the SQLite
# database if it doesn't exist.
self.log.info("Try open the existing db : {}".format(
self.database_name))
self.database.setDatabaseName(self.database_name)
else:
self.database.setDatabaseName(self.database_name)
self.log.info("Try create db : {}".format(self.database_name))
self.prepare_db()
if not self.database.open():
self.log.error("Cannot open database")
QFile.remove(self.database_name)
return False
elif self.dbType == "psql":
self.database = QSqlDatabase.addDatabase("QPSQL")
elif self.dbType == "odbc":
self.database = QSqlDatabase.addDatabase("QODBC")
self.database.setHostName(self.database_hostname)
self.database.setPort(self.database_port)
self.database.setUserName(self.database_user_name)
self.database.setPassword(self.database_password)
if not self.database.isValid():
self.log.error("Cannot add database")
return False
if not self.database.open():
self.log.error("Cannot open database")
return False
return True
def create_db():
"""Create the playlist's database file and table if they don't exist.
The created database should be accessed using :meth:`PySide2.QtSql.QSqlDatabase.database`, the
name being specified via :const:`project.library.DB_NAME`.
"""
with sqlite3.connect(DB_NAME) as conn:
cursor = conn.cursor()
cursor.execute("""
create table if not exists playlist (
id integer primary key,
title text,
artist text,
album text,
genre text,
date text,
crc32 integer not null,
path text not null
);
""")
cursor.execute("""
create table if not exists credentials (
id integer primary key,
password text
);
""") # The lack of security is a feature ;)
conn.commit()
db = QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName(DB_NAME)
def build():
# Дополнительный путь к SQL драйверу
QApplication.setLibraryPaths(
['./platforms', './plugins',
QApplication.libraryPaths()[2]])
db = QSqlDatabase.addDatabase('QSQLITE')
db.setDatabaseName('db_lib.db3')
if not db.open():
print('БД не существует')
sys.exit(-1)
model = MyTableModel(None, db)
if 'mytable' not in db.tables():
query = QSqlQuery()
query.exec_(
"create table mytable(id integer primary key autoincrement, author text, title text, year text)"
)
model.setTable('mytable')
model.setEditStrategy(QSqlTableModel.OnFieldChange)
model.select()
model.setHeaderData(0, Qt.Horizontal, "id")
model.setHeaderData(1, Qt.Horizontal, "author")
model.setHeaderData(2, Qt.Horizontal, "title")
model.setHeaderData(3, Qt.Horizontal, "year")
return model
def __init__(self):
# 从文件中加载UI定义
# qfile=QFile(r'E:\Pycharm\armus1\ui\armus1.ui')
# qfile.open(QFile.ReadOnly)
# # qfile_stats.close()
# # 从 UI 定义中动态 创建一个相应的窗口对象
# # 注意：里面的控件对象也成为窗口对象的属性了
# # 比如 self.ui.button , self.ui.textEdit
# # loader=QUiLoader()
# self.ui=QUiLoader().load(qfile)
# self.ui.show()
super(Stats,self).__init__()
# self.ui=Ui_armus()
# self.tableView.setModel()
db=QSqlDatabase.addDatabase('QSQLITE')
db.setDatabaseName('universitys.db')
self.spider=Data_Spider()#爬虫连接
self.session=DBSession()#数据库连接
self.setupUi(self)
# self.query=QSqlQuery(db)
self.init_connect_db()
# self.model = QStandardItemModel() # 存储任意结构数据
# self.model=QSqlQueryModel()
self.comboBox.activated[str].connect(self.orderinfo)
self.pushButton.clicked.connect(self.add_college)
self.pushButton_2.clicked.connect(self.update_college)
self.pushButton_4.clicked.connect(self.spiders_info)
self.pushButton_5.clicked.connect(self.spider_info)
# self.pushButton_3.clicked.connect(self.help_info)
self.comboBox_2.activated[str].connect(self.link_collegeurl)
def __init__(self, db_path):
self.tb_name = {}
self.db = ''
# 连接数据库
self.db = QSqlDatabase.addDatabase("QODBC")
self.db.setDatabaseName(
"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};FIL={MS Access};DBQ="
+ db_path)
# db.setUserName('sa')
self.db.setPassword('20121110')
# 根据ok的值判断数据库是否连接成功
# self.ok = db.open()
if self.db.open():
query = QSqlQuery(self.db)
query.exec_(
"SELECT LOGGER_SN, LOGGER_NAME, CHONE_HIGH , CHONE_LOW, CHTWO_HIGH, CHTWO_LOW FROM TO_LOGGER_INFO"
)
while query.next():
# if self.tb_name[query.value(0)]!='H201403057' and self.tb_name[query.value(0)]!='HT20143221':
self.tb_name[query.value(0)] = (query.value(1), query.value(2),
query.value(3), query.value(4),
query.value(5))
del (self.tb_name['H201403057'])
del (self.tb_name['HT20143221'])
def __init__(self):
db_path = os.path.join(os.path.dirname(os.path.realpath(__file__)),
'data.db')
self.db = QSqlDatabase.addDatabase('QSQLITE')
self.db.setDatabaseName(db_path)
self.db.open()
self.query = QSqlQuery()
def __init__(self):
QMainWindow.__init__(self)
Ui_MainWindow.__init__(self)
self.ui = Ui_MainWindow()
self.ui.setupUi(self)
self.ui.dateEdit.setDate(QtCore.QDate.currentDate())
self.ui.dateEdit.setMinimumDate(QtCore.QDate.currentDate())
self.ui.dateEdit.setMaximumDate(QtCore.QDate(8181, 12, 31))
self.ui.AddBtn.clicked.connect(self.save)
self.ui.lineEdit.returnPressed.connect(self.save)
self.db = QSqlDatabase.addDatabase("QSQLITE")
self.db.setDatabaseName("store")
dateMe = QtCore.QDate.currentDate()
self.db.open()
self.que = QSqlQuery("CREATE Table urgent (priority integer, "
"task varchar(256), date datetime);")
if self.que.exec_:
print("Executed successful.")
self.que = QSqlQuery("CREATE Table priority (priority integer, "
"task varchar(256), date datetime);")
if self.que.exec_:
print("Executed successful.")
self.que = QSqlQuery("CREATE Table dueDate (priority integer, "
"task varchar(256), date datetime);")
if self.que.exec_:
print("Executed successful.")
self.load("priority")
self.load("urgent")
self.load("dueDate")
self.ui.priority.itemDoubleClicked.connect(self.editPriority)
self.ui.urgent.itemDoubleClicked.connect(self.editUrgent)
self.ui.dueDate.itemDoubleClicked.connect(self.editDueDate)
def __init__(self, db_type=':memory:'):
self.db = QSqlDatabase.addDatabase('QSQLITE')
self.db.setDatabaseName(db_type)
if not self.db.open():
print("Could not open database:", db_type)
sys.exit(1)
def build(model_type='select'):
# Дополнительный путь к SQL драйверу
QApplication.setLibraryPaths(['./platforms', './plugins'])
db = QSqlDatabase.addDatabase('QSQLITE')
db.setDatabaseName('db25.db3')
if not db.open():
print('БД не существует')
sys.exit(-1)
if model_type == 'query':
return QSqlQueryModel(), db
else:
model = MyTableModel(None, db)
# model = MyTableModel(None, db, model_type)
if 'mytable' not in db.tables():
query = QSqlQuery()
query.exec_(
"create table mytable(id text primary key, title text, podsborka text, izdelie text)")
model.setTable('mytable')
model.setEditStrategy(QSqlTableModel.OnFieldChange)
model.select()
model.setHeaderData(0, Qt.Horizontal, "id")
model.setHeaderData(1, Qt.Horizontal, "title")
model.setHeaderData(2, Qt.Horizontal, "podsborka")
model.setHeaderData(3, Qt.Horizontal, "izdelie")
return model
def init_and_check_db(db_path):
db = QSqlDatabase.addDatabase("QSQLITE", Setup.DB_CONNECTION)
if not db.isValid():
return LedgerInitError(LedgerInitError.DbDriverFailure)
db.setDatabaseName(get_dbfilename(db_path))
db.setConnectOptions("QSQLITE_ENABLE_REGEXP=1")
db.open()
tables = db.tables(QSql.Tables)
if not tables:
db.close()
connection_name = db.connectionName()
init_db_from_sql(get_dbfilename(db_path), db_path + Setup.INIT_SCRIPT_PATH)
QSqlDatabase.removeDatabase(connection_name)
return LedgerInitError(LedgerInitError.EmptyDbInitialized)
schema_version = JalSettings().getValue('SchemaVersion')
if schema_version < Setup.TARGET_SCHEMA:
db.close()
return LedgerInitError(LedgerInitError.OutdatedDbSchema)
elif schema_version > Setup.TARGET_SCHEMA:
db.close()
return LedgerInitError(LedgerInitError.NewerDbSchema)
_ = executeSQL("PRAGMA foreign_keys = ON")
db_triggers_enable()
return LedgerInitError(LedgerInitError.DbInitSuccess)
def get_db():
name = "db-{0}".format(str(QThread.currentThread()))
if QSqlDatabase.contains(name):
return QSqlDatabase.database(name)
else:
db = QSqlDatabase.addDatabase("QSQLITE", name)
db.setDatabaseName("video.db")
return db
def connect_db(self, fname):
if fname.endswith(".db"):
db = QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName(fname)
db.open()
self.ui.label_current_file.setText(fname)
self.db = db
else:
self.ui.label_current_file.setText("Invalid File Selected")
def open_database(database_name):
""" """
conn = QSqlDatabase.addDatabase('QSQLITE')
conn.setDatabaseName(database_name)
if not conn.open():
msg1 = """Cannot open database, Unable to establish a database connection.
Click Cancel to exit. """
alert(conn.databaseName() + msg1)
sys.exit(1)
def __init__(self):
self.db = QSqlDatabase.addDatabase('QSQLITE')
self.db.setDatabaseName('DataBaseName.sqlite3')
# self.db.setDatabaseName(':memory:')
self.db.open()
self.query = QSqlQuery()
# Criando a tabela.
self.create_table()
def __open_collection(self):
collection = QFileDialog.getOpenFileName()[0]
db = QSqlDatabase.addDatabase('QSQLITE')
db.setDatabaseName(collection)
db.open()
self._model = QSqlTableModel(self, db)
self._model.setTable('objects')
self._model.select()
self._view.setModel(self._model)
def connect_db(self, fname):
if fname.endswith(".db"):
db = QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName(fname)
db.open()
self.ui.label_current_file.setText(fname)
self.db = db
self.tables = self.db.tables()
self.populate_combo()
self.show_table()
else:
self.ui.label_current_file.setText("Invalid File Selected")
def setLogs(self):
isLogChanged = self.settings.value('logChanged')
if isLogChanged == 1 or self.initialRun:
self.allLogsModel = QSqlQueryModel()
dbAllLog = QSqlDatabase.addDatabase("QSQLITE")
dbAllLog.setDatabaseName("caio.db")
dbAllLog.open()
self.allLogsModel.setQuery(
"select * from allLogs order by id desc ", dbAllLog)
dbAllLog.close()
print(self.allLogsModel)
self.settings.setValue('logChanged', 0)
QSqlDatabase.removeDatabase("QSQLITE")
self.initialRun = False
def init_and_check_db(db_path):
db = QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName(get_dbfilename(db_path))
db.open()
tables = db.tables(QSql.Tables)
if not tables:
db.close()
loadDbFromSQL(get_dbfilename(db_path), db_path + Setup.INIT_SCRIPT_PATH)
return None, LedgerInitError(LedgerInitError.EmptyDbInitialized)
if readSQL(db, "SELECT value FROM settings WHERE name='SchemaVersion'") != Setup.TARGET_SCHEMA:
db.close()
return None, LedgerInitError(LedgerInitError.WrongSchemaVersion)
return db, LedgerInitError(LedgerInitError.DbInitSuccess)
def updateSqlModel(lecture_no):
db = QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName("face.db")
if db.open() is False:
print("Failed opening db")
query = "SELECT Student.id, rollno, name FROM Student JOIN "\
"Attendance ON Student.id = Attendance.id "\
"AND Attendance.date = (SELECT date('now')) "
if lecture_no != 0:
query += " AND Attendance.lecture_no = " + str(lecture_no)
model.setQuery(query)
def __init__(self):
self.db = QSqlDatabase.addDatabase('QPSQL')
self.db.setHostName('186.217.53.157')
self.db.setPort(5432)
self.db.setDatabaseName('database_name')
self.db.setUserName('dbuser')
self.db.setPassword('123456')
self.db.open()
self.query = QSqlQuery()
# Criando a tabela.
self.create_table()
def open_db(self):
self.conn = QSqlDatabase.addDatabase("QODBC3")
self.conn.setDatabaseName(
"{{Driver={0}}};DSN={1};UID={2};PWD={3}".format(
self.driver, self.name, self.user, self.__pass))
if self.conn.open():
self._output()
return self.conn
else:
print("Была ошибка открытия базы через odbc с именем", self.name)
self._output()
print("Db and Qt errors with space / Ошибки Db и Qt через пробел",
self.conn.lastError().text())
return
def deregisterStudent(self, id):
self.db = QSqlDatabase.addDatabase("QSQLITE")
self.db.setDatabaseName("face.db")
if self.db.open() is False:
print("Failed opening db")
query = QSqlQuery(self.db)
query.prepare("DELETE FROM Student WHERE id = :id")
query.bindValue(":id", int(id))
query.exec_()
path = 'dataset'
imagePaths = [os.path.join(path, f) for f in os.listdir(path)]
[os.remove(x) for x in imagePaths if ('/' + str(id) + ".") in x]
self.getThreadSignal(9, "Requested Record removed completely. Kindly retrain.")
def init_db():
"""
init_db()
Initializes the database.
If tables "books" and "authors" are already in the database, do nothing.
Return value: None or raises ValueError
The error value is the QtSql error instance.
"""
def check(func, *args):
if not func(*args):
raise ValueError(func.__self__.lastError())
db = QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName(":memory:")
check(db.open)
q = QSqlQuery()
check(q.exec_, BOOKS_SQL)
check(q.exec_, AUTHORS_SQL)
check(q.exec_, GENRES_SQL)
check(q.prepare, INSERT_AUTHOR_SQL)
asimovId = add_author(q, "Isaac Asimov", date(1920, 2, 1))
greeneId = add_author(q, "Graham Greene", date(1904, 10, 2))
pratchettId = add_author(q, "Terry Pratchett", date(1948, 4, 28))
check(q.prepare, INSERT_GENRE_SQL)
sfiction = add_genre(q, "Science Fiction")
fiction = add_genre(q, "Fiction")
fantasy = add_genre(q, "Fantasy")
check(q.prepare, INSERT_BOOK_SQL)
add_book(q, "Foundation", 1951, asimovId, sfiction, 3)
add_book(q, "Foundation and Empire", 1952, asimovId, sfiction, 4)
add_book(q, "Second Foundation", 1953, asimovId, sfiction, 3)
add_book(q, "Foundation's Edge", 1982, asimovId, sfiction, 3)
add_book(q, "Foundation and Earth", 1986, asimovId, sfiction, 4)
add_book(q, "Prelude to Foundation", 1988, asimovId, sfiction, 3)
add_book(q, "Forward the Foundation", 1993, asimovId, sfiction, 3)
add_book(q, "The Power and the Glory", 1940, greeneId, fiction, 4)
add_book(q, "The Third Man", 1950, greeneId, fiction, 5)
add_book(q, "Our Man in Havana", 1958, greeneId, fiction, 4)
add_book(q, "Guards! Guards!", 1989, pratchettId, fantasy, 3)
add_book(q, "Night Watch", 2002, pratchettId, fantasy, 3)
add_book(q, "Going Postal", 2004, pratchettId, fantasy, 3)
def __init__(self):
self.db = QSqlDatabase.addDatabase('QPSQL')
self.db.setHostName('localhost')
self.db.setPort(5432)
self.db.setDatabaseName('database_name')
self.db.setUserName('dbuser')
self.db.setPassword('123456')
self.db.open()
self.query = QSqlQuery()
# Removendo a tabela.
self.drop_table(table='table_name')
# Criando a tabela.
self.create_table()
def connectToDatabase(logger):
database = QSqlDatabase.database()
if not database.isValid():
database = QSqlDatabase.addDatabase('QSQLITE')
if not database.isValid():
logger.error('Cannot add database')
write_dir = QDir()
if not write_dir.mkpath('.'):
logger.error('Failed to create writable directory')
# Ensure that we have a writable location on all devices.
filename = '{}/chat-database.sqlite3'.format(write_dir.absolutePath())
# When using the SQLite driver, open() will create the SQLite database if it doesn't exist.
database.setDatabaseName(filename)
if not database.open():
logger.error('Cannot open database')
QFile.remove(filename)
def connectToDatabase():
database = QSqlDatabase.database()
if not database.isValid():
database = QSqlDatabase.addDatabase("QSQLITE")
if not database.isValid():
logger.error("Cannot add database")
writeDir = QDir()
# writeDir = QStandardPaths.writableLocation(QStandardPaths.AppDataLocation)
if not writeDir.mkpath("."):
logger.error("Failed to create writable directory")
# Ensure that we have a writable location on all devices
fileName = writeDir.absolutePath() + "/chat-database.sqlite3"
database.setDatabaseName(fileName)
# open() will create the SQLite database if it doesn't exist
if not database.open():
logger.error("Cannot open database")
QFile.remove(fileName)
def __init__(self, name, applicationWindow, hostname, username, password,
port):
"""
@type name: str
@type connection: MySQLdb.Connection
@type applicationWindow: MainApplicationWindow
"""
self.name = name
self.hostname = hostname
self.username = username
self.password = password
self.port = port
self.applicationWindow = applicationWindow
self.databases = list()
self.currentDatabase = None
self.collations = list()
ctypes.windll.LoadLibrary(
r'D:\wamp64\bin\mysql\mysql5.7.21\lib\libmysql.dll') # 加载驱动库
self.connection = MySQLdb.connect(
host=hostname,
user=username,
passwd=password,
port=port,
cursorclass=MySQLdb.cursors.DictCursor)
db = QSqlDatabase.addDatabase('QMYSQL', name)
db.setHostName(hostname)
db.setUserName(username)
db.setPassword(password)
db.open() # 打开数据库
self.db = db
serverItem = HeidiTreeWidgetItem()
serverItem.setText(0, name)
serverItem.setIcon(0, QIcon('../resources/icons/server.png'))
serverItem.setFlags(Qt.ItemIsEnabled | Qt.ItemIsSelectable)
serverItem.setChildIndicatorPolicy(
QTreeWidgetItem.DontShowIndicatorWhenChildless)
serverItem.itemType = 'server'
self.databaseTreeItem = serverItem
applicationWindow.mainWindow.databaseTree.addTopLevelItem(serverItem)
def main():
db = QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName("foo.sqlite")
if not db.open():
sys.exit(-1)
lib = CDLL(os.path.join(CURRENT_DIR, "libqsqlite.so"))
lib.enable_extension(c_void_p(shiboken2.getCppPointer(db.driver())[0]))
load_spatialite()
query = QSqlQuery()
query.exec_("CREATE TABLE my_line(id INTEGER PRIMARY KEY)")
query.exec_(
"""SELECT AddGeometryColumn("my_line","geom" , 4326, "LINESTRING", 2)"""
)
polygon_wkt = "POLYGON ((11 50,11 51,12 51,12 50,11 50))"
XA = 11
YA = 52
XB = 12
YB = 49
line_wkt = "LINESTRING({0} {1}, {2} {3})".format(XA, YA, XB, YB)
query.prepare("""INSERT INTO my_line VALUES (?,GeomFromText(?, 4326))""")
query.addBindValue(1)
query.addBindValue(line_wkt)
query.exec_()
query.prepare(
"""SELECT astext(st_intersection(geom, GeomFromText(?, 4326))) from my_line WHERE st_intersects(geom, GeomFromText(?, 4326))"""
)
query.addBindValue(polygon_wkt)
query.addBindValue(polygon_wkt)
query.exec_()
while query.next():
for i in range(query.record().count()):
print(query.value(i))
def registerStudent(self, id, rollno, name, stclass, section):
self.db = QSqlDatabase.addDatabase("QSQLITE")
self.db.setDatabaseName("face.db")
if self.db.open() is False:
print("Failed opening db")
query = QSqlQuery(self.db)
query.prepare("Insert Into Student (id, rollno, name, class, section)"
"Values (:id, :rollno, :name, :class, :section)")
query.bindValue(":id", id)
query.bindValue(":rollno", rollno)
query.bindValue(":name", name)
query.bindValue(":class", stclass)
query.bindValue(":section", section)
query.exec_()
# take photos for training
self.startCapture(id)
self.db.close()
def __init__(self):
super().__init__()
self.projectModel = QSqlQueryModel()
self.settings = QSettings('CAIO', 'Preferences')
self.totalLocks = self.settings.value('totalLocks')
self.noOfLocksAdmin = self.settings.value('noOfLocksAdmin')
self.noOfSomeoneElseLocks = self.settings.value('noOfSomeoneElseLocks')
self.noOfNobodyLocks = self.settings.value('noOfNobodyLocks')
self.initialRun = True
self.initialRunCircular = True
self.counter = 0
self.db = QSqlDatabase.addDatabase("QSQLITE")
dbLocation = str(Path.home()) + '/CAIO/caio.db'
self.db.setDatabaseName(dbLocation)
self.setLogs()
# AUTO REFRESH
timer = QTimer(self)
timer.start(1000)
timer.timeout.connect(lambda: self.setWidget())
timer.timeout.connect(lambda: self.setLogs())