class ConnectPostgreSQL: 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 create_table(self): sql = '''CREATE TABLE IF NOT EXISTS table_name ( id SERIAL NOT NULL, name VARCHAR(100) NOT NULL, age INTEGER NOT NULL, gender VARCHAR(10) NOT NULL, PRIMARY KEY(id) );''' self.query.exec_(sql) def drop_table(self, table): sql = f'DROP TABLE IF EXISTS {table};' self.query.exec_(sql) def insert_row(self, data): sql = 'INSERT INTO table_name (name, age, gender) VALUES (?, ?, ?)' self.query.prepare(sql) for index, value in enumerate(data): self.query.addBindValue(data[index]) if self.query.exec_(): self.query.clear() return True print(self.query.lastError()) return False def insert_rows(self, data): for row in data: self.insert_row(data=row) def find_by_id(self, rowid): sql = 'SELECT * FROM table_name WHERE id = ?' self.query.prepare(sql) self.query.addBindValue(rowid) self.query.exec_() if self.query.first(): data = (self.query.value(0), self.query.value(1), self.query.value(2), self.query.value(3)) self.query.clear() return data print(self.query.lastError()) return False def find(self, limit=10): sql = 'SELECT * FROM table_name LIMIT ?' self.query.prepare(sql) self.query.addBindValue(limit) self.query.exec_() result = [] while self.query.next(): data = (self.query.value(0), self.query.value(1), self.query.value(2), self.query.value(3)) result.append(data) self.query.clear() return result def update_row(self, rowid, name, age, gender): sql = 'UPDATE table_name SET name=?, age=?, gender=? WHERE id=?' self.query.prepare(sql) self.query.addBindValue(name) self.query.addBindValue(age) self.query.addBindValue(gender) self.query.addBindValue(rowid) if self.query.exec_(): return True print(self.query.lastError()) return False def remove_row(self, rowid): sql = 'DELETE FROM table_name WHERE id=?' self.query.prepare(sql) self.query.addBindValue(rowid) if self.query.exec_(): return True self.db.rollback() print(self.query.lastError()) return False
query.bindValue(':name', list1) query.bindValue(':count', list2) if not query.execBatch(): print("error with batch", e.lastError().text()) query.finish() else: print("Error with create table good", e.lastError().text()) good_select = "select * from good order by goodname" query.prepare(good_select) query.setForwardOnly(True) if query.exec_(): print("Select is done") lst = [] if query.isActive( ): # запрос находится в активном состоянии, т е ранее вызывались методы exec_, execBatch query.first() # Есть еще seek while query.isValid( ): # если внутренний указатель указывает на какую-либо запись lst.append( query.value('goodname') + ": " + str(query.value('goodcount')) + ' шт.') print("Number of record", query.at(), "about record", query.record()) query.next() for p in lst: print(p) else: print("Error in select") query.finish() window = QtWidgets.QTableView()
class ConnectSQLite: def __init__(self): self.db = QSqlDatabase.addDatabase('QSQLITE') self.db.setDatabaseName('DataBaseName.sqlite3') # self.db.setDatabaseName(':memory:') self.db.open() self.query = QSqlQuery() # Removendo a tabela. self.drop_table(table='table_name') # Criando a tabela. self.create_table() def create_table(self): sql = '''CREATE TABLE IF NOT EXISTS table_name ( id INTEGER PRIMARY KEY, name TEXT, age INTEGER, gender TEXT );''' self.query.exec_(sql) def drop_table(self, table): sql = f'DROP TABLE IF EXISTS {table};' self.query.exec_(sql) def insert_row(self, data): sql = 'INSERT INTO table_name (name, age, gender) VALUES (?, ?, ?)' self.query.prepare(sql) for index, value in enumerate(data): self.query.addBindValue(data[index]) self.query.exec_() def insert_rows(self, data): for row in data: self.insert_row(data=row) def find_by_id(self, rowid): sql = 'SELECT * FROM table_name WHERE id = ?' self.query.prepare(sql) self.query.addBindValue(rowid) self.query.exec_() if self.query.first(): return (self.query.value(0), self.query.value(1), self.query.value(2), self.query.value(3)) return False def find(self, limit=10): sql = 'SELECT * FROM table_name LIMIT ?' self.query.prepare(sql) self.query.addBindValue(limit) self.query.exec_() result = [] while self.query.next(): data = (self.query.value(0), self.query.value(1), self.query.value(2), self.query.value(3)) result.append(data) return result def update_row(self, rowid, name, age, gender): sql = 'UPDATE table_name SET name=?, age=?, gender=? WHERE id=?' self.query.prepare(sql) self.query.addBindValue(name) self.query.addBindValue(age) self.query.addBindValue(gender) self.query.addBindValue(rowid) if self.query.exec_(): return True self.db.rollback() print(self.query.lastError()) return False def remove_row(self, rowid): sql = 'DELETE FROM table_name WHERE id=?' self.query.prepare(sql) self.query.addBindValue(rowid) if self.query.exec_(): return True self.db.rollback() print(self.query.lastError()) return False