class DataBase:
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 select(self, fields: tuple, table: str, condition=None):
"""
select items from table
For example, to execute the sql state
`select name from Teacher where tid=1`,
use `select(fields=('name',),
table='Teacher', condition='tid=1')`
params
fields: tuple of str, such as ('id', 'name', 'type')
table: str, name of target table
condition: str, such as 'id=1'
returns a list of dict
"""
state = 'select {fields} from {table}'.format(fields=', '.join(fields),
table=table)
if condition is not None:
state += ' where {con}'.format(con=condition)
res = []
self.query.clear()
self.query.exec_(state)
print(state)
while self.query.next():
item = {f: self.query.value(f) for f in fields}
res.append(item)
return res
def insert(self, table: str, kv: dict):
state = 'insert into {t} ({f}) values ({v})'.format(
t=table, f=', '.join(kv.keys()), v=', '.join(kv.values()))
print(state)
return self.query.exec_(state)
def __add_quote(self, s: str):
return '"{}"'.format(s)
def last_error(self):
return self.query.lastError()
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
