Ezweni lokuhlaziywa kwedatha kanye nokuphathwa kwayo, enye yemitapo yolwazi yePython ethandwa kakhulu yiPandas . Ihlinzeka ngamathuluzi ahlukahlukene anamandla okusebenza ngedatha ehlelekile, okwenza kube lula ukuyisebenzisa, ukuyibuka ngeso lengqondo kanye nokuhlaziya. Omunye wemisebenzi eminingi umhlaziyi wedatha angase ahlangane nayo ukungenisa idatha kusuka kufayela le-CSV kudathabheyisi ye-PostgreSQL . Kulesi sihloko, sizoxoxa ngendlela yokwenza lo msebenzi ngempumelelo nangendlela ephumelelayo sisebenzisa iPandas kanye nomtapo wolwazi we -psycopg2 . Sizohlola nemisebenzi ehlukene kanye nemitapo yolwazi ehilelekile kule nqubo, sinikeze ukuqonda okuphelele kwesisombululo.
Isingeniso kuPandas kanye ne-PostgreSQL
I-Pandas iwumtapo wezincwadi wePython onamandla ohlinzeka ngezakhiwo zedatha okulula ukuzisebenzisa nemisebenzi yokukhohlisa idatha ukuze kuhlaziywe idatha. Kuwusizo ikakhulukazi lapho usebenza namasethi amakhulu edatha noma uma udinga ukwenza ukuguqulwa kwedatha okuyinkimbinkimbi. I-PostgreSQL, ngakolunye uhlangothi, iwuhlelo lwamahhala nomthombo ovulekile lokuphathwa kwedatha yedatha (i-ORDBMS) egcizelela ukwandiswa nokuthobela i-SQL. Isetshenziswa kabanzi emisebenzini emikhulu, eyinkimbinkimbi yokuphatha idatha.
Manje, ake sithi sinefayela le-CSV eliqukethe idathasethi enkulu, futhi sifuna ukulingenisa kusizindalwazi se-PostgreSQL. Indlela evamile yokufeza lo msebenzi ukusebenzisa i-Pandas ngokuhambisana nomtapo wezincwadi we-psycopg2, ohlinzeka nge-adaptha yolwazi lwe-PostgreSQL esivumela ukuthi sixhumane nayo sisebenzisa i-Python.
AmaPanda: Ukufunda amafayela e-CSV
Isinyathelo sokuqala senqubo yethu ukufunda okuqukethwe kwefayela lethu le-CSV sisebenzisa i-Pandas.
import pandas as pd filename = "example.csv" df = pd.read_csv(filename)
Le khodi isebenzisa umsebenzi we -pd.read_csv() , ofunda ifayela le-CSV bese ubuyisela into ye-DataFrame. Ngento ye-DataFrame, singayilawula futhi siyihlaziye kalula idatha.
Ixhuma kusizindalwazi se-PostgreSQL
Isinyathelo esilandelayo ukuxhuma kusizindalwazi sethu se-PostgreSQL usebenzisa umtapo wezincwadi we-psycopg2. Ukwenza lokhu, sidinga ukufaka umtapo wezincwadi we-psycopg2, ongenziwa kusetshenziswa i-pip:
pip install psycopg2
Uma umtapo wezincwadi usufakiwe, sidinga ukuxhuma kusizindalwazi sethu se-PostgreSQL:
import psycopg2
connection = psycopg2.connect(
dbname="your_database_name",
user="your_username",
password="your_password",
host="your_hostname",
port="your_port",
)
Umsebenzi we- psycopg2.connect() usungula uxhumano neseva yedatha usebenzisa iziqinisekiso ezinikeziwe. Uma uxhumano luphumelela, umsebenzi ubuyisela into yokuxhumana esizoyisebenzisa ukusebenzisana nedatha.
Ukudala ithebula ku-PostgreSQL
Manje njengoba sesinedatha yethu entweni ye-DataFrame kanye nokuxhumana kusizindalwazi se-PostgreSQL, singakwazi ukwakha ithebula kusizindalwazi ukuze sigcine idatha yethu.
cursor = connection.cursor()
create_table_query = '''
CREATE TABLE IF NOT EXISTS example_table (
column1 data_type,
column2 data_type,
...
)
'''
cursor.execute(create_table_query)
connection.commit()
Kulesi siqeshana sekhodi, siqala ngokudala into yesikhombisi sisebenzisa indlela ye-connection.cursor() . Isikhombisi sisetshenziselwa ukwenza imisebenzi yesizindalwazi njengokudala amathebula nokufaka idatha. Okulandelayo, sichaza umbuzo we-SQL wokudala ithebula, bese siwusebenzisa sisebenzisa indlela ye -cursor.execute() . Ekugcineni, senza izinguquko kusizindalwazi nge -connection.commit().
Ukufaka idatha kusizindalwazi se-PostgreSQL
Manje njengoba sesinethebula, singafaka idatha evela ku-DataFrame yethu ku-database ye-PostgreSQL sisebenzisa indlela ye-to_sql() enikezwe yi-Pandas.
from sqlalchemy import create_engine
engine = create_engine("postgresql://your_username:your_password@your_hostname:your_port/your_database_name")
df.to_sql("example_table", engine, if_exists="append", index=False)
Kulesi siqeshana sekhodi, siqala ngokudala injini yedatha sisebenzisa umsebenzi we- create_engine() welabhulali ye-SQLAlchemy, edinga umucu wokuxhumana oqukethe iziqinisekiso zethu zedatha. Bese, sisebenzisa indlela ye -to_sql() ukufaka idatha evela ku-DataFrame yethu kuthebula elithi “isibonelo_sethebula” kudathabheyisi ye-PostgreSQL.
Sengiphetha, le ndatshana ihlinzeka ngomhlahlandlela ophelele wokuthi ungangenisa kanjani idatha kusuka kufayela le-CSV uye kusizindalwazi se-PostgreSQL usebenzisa i-Pandas ne-psycopg2. Ngokuhlanganisa ukusebenziseka kalula kwedatha ku-Pandas namandla nokukaleka kwe-PostgreSQL, singakwazi ukuzuza isisombululo esingenazihibe nesisebenza kahle emsebenzini ovamile wokungenisa idatha ye-CSV kusizindalwazi.
