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
