class MariaDB(object): """ MariaDB is a facade pattern class based on Orator ORM """ def __init__(self, host, port, user, password, database, trace=0): self._client = 'MariaDB' self._host = host self._port = port self._user = user self._password = password self._database = database self._api = None self._last_query = None # Debug messages self._trace = trace # This is to avoid problems that mysql has with localhost if host == 'localhost': self._host = '127.0.0.1' # Create connection with pony orm python API config = { 'mysql': { 'driver': 'mysql', 'host': host, 'database': database, 'user': user, 'password': password, 'port': port, 'prefix': '' } } self._api = DatabaseManager(config) result = self._api.select('SHOW TABLES') if not list(result): raise DBConnectionFailed( f'Connection to MariaDB failed. Check connection parameters') @property def last_query(self): return self._last_query @property def last_query_stats(self): return None def sql(self, q): return self._api.select(q) def cmd(self): pass
from orator import DatabaseManager config = { 'mysql': { 'driver': 'mysql', 'host': 'localhost', 'database': 'db_wo', 'user': '******', 'password': '******', 'prefix': '' } } db = DatabaseManager(config) results = db.select('SELECT * from tb_pesan_gedung where (now() > DATE_ADD(waktu_pesan, INTERVAL 2 HOUR)) and status = "pending" and status_pembayaran = "Belum Melakukan Pembayaran"') # print results for r in results: # print r db.table('tb_pesan_gedung').where('id_pesan', r['id_pesan']).update(status='expired') # query # SELECT # waktu_pesan, # now() as sekarang, # DATE_ADD(waktu_pesan, INTERVAL 2 HOUR) as expired, # if(now() > (DATE_ADD(waktu_pesan, INTERVAL 2 HOUR)), 'lebih','kurang') as jam_setelah # from tb_pesan_gedung # where # (DATE_ADD(NOW(), INTERVAL 2 HOUR) > waktu_pesan)
from orator import DatabaseManager config = { 'mysql': { 'read': [{ 'host': 'localhost', 'database': 'test1', 'user': '******', 'password': '******' }], 'write': [{ 'host': 'localhost', 'database': 'test', 'user': '******', 'password': '******', }], 'driver': 'mysql', 'prefix': '' } } db = DatabaseManager(config) results = db.select('select * from user') print results
class DBController(): def __init__(self, parent=None): self.parent = parent #Configure DB self.config = { 'mysql': { 'driver': 'mysql', 'host': 'localhost', 'database': 'mydb', 'user': '******', 'password': '******', 'prefix': '' } } self.db = DatabaseManager(self.config) Model.set_connection_resolver(self.db) def makeUser(self, name, birthday, gender, nationality): return User(Name=name, Birthday=birthday, Gender=gender, Nationality=nationality) def makeRun(self, type, user): newRun = Run() currentTime = datetime.now(timezone.utc).strftime("%Y-%m-%d %H:%M:%S") newRun.Starttime = currentTime newRun.type=type newRun.User().associate(user) return newRun def makeEncoding(self, code ,fileName): """Creates and returns a new encoding object to be saved, or returns a ref to an existing such object if an encoding with such attributes already exists. @code (int): Encoding of the file. @fileName (string): Name of the file.s @returns (Encoding object): object to be saved to database with .save(). """ newEncoding = Encoding.first_or_create(code=code, filename=fileName) return newEncoding def makeSample(self, sampleNumber, channelVals, timestamp, run, encoding): """Creates and returns a new Sample object to be saved. @sampleNumber (int): Should correspond to the id sent with every sample from the board. @channelVals (list): Array with the values for each sample. Value at index 0 should be the value for channel 1 and so on. @timestamp (datestring on format "%Y-%m-%d %H:%M:%S"): time at which sample was taken, UTC time. @run (Run object): As returned by makeRun for example, the run that owns this sample. @encoding(Encoding object): as returned by makeEncoding, the encoding showing which img was shown during this sample. @returns (Sample object): a new sample to be saved to DB. """ newSample = Sample() newSample.Timestamp = timestamp for i in range(1, 17): eval("newSample.Channel" + i + "_val = " + str(channelVals[i])) newSample.SampleNumber = sampleNumber newSample.Run().associate(run) newSample.Encoding().associate(encoding) return newSample def getUser(self, id): """Returns an actual User object, not simple dictionary with info""" return User.first_or_create(User_ID = id) def getAllUsers(self): """Returns a list of dicts with info of all users in DB""" userQuery = "select * from user" return self.db.select(userQuery) def getAllUserSamples(self, userID): """Returns a list of dictionaries filled with information about the EEG values from all runs made by a certain user.""" query = ("select Run_ID, SampleNumber, filename, Channel1_val, Channel2_val,"+ "Channel3_val, Channel4_val, Channel5_val Channel6_val,"+ "Channel7_val, Channel8_val, Channel9_val, Channel10_val, Channel11_val,"+ "Channel12_val, Channel13_val, Channel14_val, Channel15_val, Channel16_val "+ "from user join run "+ "on run.idUser = user.User_ID join "+ "sample on sample.idRun = run.Run_ID join "+ "encoding on encoding.Encoding_ID = sample.idEncoding "+ "where user.User_ID = %d;" % userID) return self.db.select(query) def deleteUser(self, userID): """Deletes user from database based on user ID. @userID (str): ID of user to be deleted. """ deletedRows = User.where('user_id', '=', userID).delete()