DBOPERATIONS-module

dbOperations.py on moduuli, joka tarjoaa perustyökalut tietokannan objektien käyttämiseen. Moduuli perustuu Psycopg2-kirjastoon. Se sisältää seuraavaat luokat:

Luokka	Käyttötarkoitus
DbConnection	Muodostaa yhteyden tietokantaan ja mahdollistaa datan CRUD (Create, Read, Update, Delete)

DbConnection-luokka

Luokan avulla luodaan yhteysolio, jonka metodien avulla voidaan lisätä, lukea, muokata ja poistaa tietokannan tauluista.

Muodostin

Konstruktorimetodi: dbconnection = DbConnection(settingsDictionary), jossa settingsDictionary on tietokannan yhteysmerkkijonoon tarvittavat tiedot avain-arvo-pareina.

Esimerkki:

settingsDictionary = {'server': 'localhost',
                      'port': '5433',
                      'database': 'testaus',
                      'userName': 'postgres',
                      'password': 'Q2werty'}
dbconnection = DbConnection(settingsDictionary)

Palvelimen nimenä server voi käyttää palvelimen nimeä tai IP-osoitetta.

Ominaisuudet

Ominasuus (kenttä)	Sisältö
self.server	Palvelimen nimi tai IP-osoite
self.port	Palvelimen käyttämä porttinumero
self.databaseName	Palvelimen käyttämä nimi
self.userName	Käyttäjätunnus
self.password	Tunnukseen liitetty salasana
self.connectionString	Edellisistä tiedoista muodostetty yhteysmerkkijono

Metodit

Luokasta muodostetuilla oliolla on seuraavat metodit

Metodit:

• addToTable(self, table: str, data: dict) -> None : Lisää tauluun table tiedot sanakirjasta dict

• deleteRowsFromTable(self, table, criteriaColumn, criteriaValue) -> None : Poistaa taulusta table rivit, joissa sarakkeen criteriaColumn arvo on_criteriaValue_

• filterColumsFromTable(self, table: str, columns: list, filter:str) -> list | None : Lukee taulusta table suodatusehdot filter täyttävät rivit ja näyttää määritellyt sarakkeet columns

• filterDistinctColumsFromTable(self, table: str, columns: list, filter:str) -> list | None : Lukee taulusta tai näkymästä ainutkertaiset tietueet eli jättää kaksoisarvot pois

• getColumnNames(self, table: str) -> list : Hakee taulun tai näkymän sarakkeiden nimet

• getPgTimestamp(self) -> str : Hakee tietokantapalvelimelta aikaleiman ja muuntaa sen Python-muotoon

• modifyTableData(self, table: str, column: str, newValue, criteriaColumn: str, criteriaValue) -> None : Muokataan taulun table sarakkeen column arvoksi newValue, kun ehtosarakkeen criteriaColumn arvona on criteriaValue

• readAllColumnsFromTable(self, table: str) -> list None : Lukee taulun table kaikkien sarakkeiden tiedot

• readColumsFromTable(self, table: str, columns: list) -> list : Lukee taulusta table listana columns annettujen sarakkeiden tiedot

• updateBinaryField(self, table: str, column: str, criteriaColumn: str, criteriaValue, data) -> None : Päivitetään taulun table binäärisaraketta clolumn, kun ehtosarakkeen criteriaColumn arvona oon criteriaValue, binääritieto on data -argumenttina.

addToTable

addToTable(self, table: str, data: dict) -> None-metodilla lisätään sanakirjan tiedot taulun tietueeksi. Sanakirjan avaiment ovat samat kuin taulun sarakkeiden nimet. Seuraavassa esimerkissä lisätään tauluun asiakas sarakkeiden etunimi ja sukunimi.

Esimerkki:

settingsDictionary = {'server': 'localhost',
                      'port': '5432',
                      'database': 'testaus',
                      'userName': 'postgres',
                      'password': 'Q2werty7'}

dbConnection = dbOperations.DbConnection(settingsDictionary)
dbConnection.addToTable('asiakas', {'etunimi': 'Erkki', 'sukunimi': 'Esimerkki'})

filterColumsFromTable

filterColumsFromTable(self, table: str, columns: list, filter:str) -> list | None -metodi palauttaa annetun ehdon täyttävän tulosjoukon, jonka sarakkeet on määritelty metodin argumentteina. filter-argumentti määritellään FString-komennolla, jotta merkkijonoihin saadaan SQL:n vaatimat puolilainausmerkit '

Esimerkki:

settingsDictionary = {'server': 'localhost',
                      'port': '5432',
                      'database': 'testaus',
                      'userName': 'postgres',
                      'password': 'Q2werty7'}

dbConnection3 = dbOperations.DbConnection(settingsDictionary)
filterText = f"etunimi = 'Jakke'"
results = dbConnection3.filterColumsFromTable('asiakas', ['etunimi', 'sukunimi'], filterText)
print(results)

modifyTableData

modifyTableData(self, table: str, column: str, newValue, criteriaColumn: str, criteriaValue)-metodin avulla voidaan päivittää taulun table ehdot täyttävien tietueiden sarakkeen column uudeksi arvoksi newValue. Ehto määritellään sarakkeen criteriaColumn arvon criteriaValue perusteella. criteriaValue ja newValue argumentit määritellään FString-muodossa, jotta merkkijonoihin saadaan tarvittaessa SQL:n edellyttämät puolilainausmerkit '.

Esimerkki:

settingsDictionary = {'server': 'localhost',
                      'port': '5432',
                      'database': 'testaus',
                      'userName': 'postgres',
                      'password': 'Q2werty7'}

dbConnection4 = dbOperations.DbConnection(settingsDictionary)

criteriaValue = f"'Essi'"
newValue = f"'Eräjärvi'"
dbConnection4.modifyTableData('asiakas', 'sukunimi', newValue, 'etunimi', criteriaValue)

readAllColumnsFromTable

Metodi paluttaa listan (list), jonka jäseninä on monikoita (tuple)

dbConnection2 = dbOperations.DbConnection(settingsDictionary)
asiakasLista = dbConnection2.readAllColumnsFromTable('asiakas')
print(asiakasLista)

Esimekin koodi tulostaa seuraavat tiedot: [('Erkki', 'Esimerkki'), ('Essi', 'Esimerkki')]

updateBinaryField

updateBinaryField(self, table: str, column: str, criteriaColumn: str, criteriaValue, data)-metodin avulla päivitetään binääritietoja sisältävää saraketta. Binääritietona (data) voi olla esim. kuvatiedosto tai äänitiedosto. Psycopg2-kirjasto tallentaa tiedot aina monikkoina. Metodia käyttäessä ei tarvitse käyttää monikoita. Sisäisesti metodi käyttää monikkoaseuraavalla tavalla:

# Määritellään f-stringin avulla lopullinen SQL-lause, paikkamerkki %s korvautuu binääritiedolla data
sqlClause = f'UPDATE {table} SET  {column} = %s WHERE {criteriaColumn} = {criteriaValue}'

# Suoritetaan SQL-lause ja lisätään data-muuttuja monikkona käyttäen sulkuja ja pilkkua
cursor.execute(sqlClause, (data,))

Seuraavassa esimerkissä päivitetään auton kuva, jonka rekisterinumero on XYZ-123. Auton kuva on luettu muuttujaan pictureDara

numberPlate = 'XYZ-123'
dbConnection3 = dbOperations.DbConnection(settingsDictionary
dbConnection3.updateBinaryField('auto', 'kuva', 'rekisterinumero', f"'{numberPlate}'", pictureData

Huomaa, että esimerkissä rekisterinumero pitää olla SQL-merkkijono, jossa on puolilainausmerkit.