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