Ixazululiwe: i-pandas yetafula kuya ku-postgresql

Emhlabeni wokuhlaziywa kwedatha kanye nokukhohlisa, omunye wemitapo yolwazi yePython edume kakhulu AmaPandas. Ihlinzeka ngamathuluzi anamandla ahlukahlukene okusebenza ngedatha ehlelekile, okwenza kube lula ukuyilawula, ukuyibona ngeso lengqondo nokuhlaziya. Omunye wemisebenzi eminingi umhlaziyi wedatha angase ahlangabezane nayo ukungenisa idatha kusuka ku- CSV ifayela ku-a I-PostgreSQL database. Kulesi sihloko, sizoxoxa ngendlela yokwenza lo msebenzi ngempumelelo nangempumelelo usebenzisa kokubili AmaPandas futhi ngc2 umtapo wolwazi. Sizophinde sihlole imisebenzi ehlukene kanye nemitapo yolwazi ehilelekile kule nqubo, sinikeze ukuqonda okuphelele kwesixazululo.

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 i- pd.read_csv() function, efunda ifayela le-CSV bese ibuyisela into ye-DataFrame. Ngento ye-DataFrame, singakwazi ukukhohlisa futhi sihlaziye 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",
)

The psycopg2.connect() umsebenzi usungula uxhumano neseva yesizindalwazi usebenzisa imininingwane enikeziwe. Uma ukuxhumeka kuphumelele, umsebenzi ubuyisela into yokuxhuma esizoyisebenzisa ukuze sihlanganyele nesizindalwazi.

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()

Kule khodi amazwibela, siqala sakha into yekhesa sisebenzisa i- uxhumano.ikhesa() indlela. Ikhesa isetshenziselwa ukwenza imisebenzi yesizindalwazi njengokwenza amathebula nokufaka idatha. Okulandelayo, sichaza umbuzo we-SQL wokwenza ithebula, bese siwenza usebenzisa ifayela le- i-cursor.execute() indlela. Ekugcineni, sibophezela izinguquko kusizindalwazi nge uxhumano.commit().

Ukufaka idatha kusizindalwazi se-PostgreSQL

Manje njengoba sesinetafula, singakwazi ukufaka idatha esuka ku-DataFrame yethu kusizindalwazi se-PostgreSQL sisebenzisa i- kuya_sql() indlela ehlinzekwe yiPandas.

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)

Kule khodi amazwibela, siqale sakha injini yesizindalwazi sisebenzisa i dala_injini() umsebenzi womtapo wezincwadi we-SQLAlchemy, odinga iyunithi yezinhlamvu yokuxhuma equkethe imininingwane yethu yesizindalwazi. Khona-ke, sisebenzisa i- kuya_sql() indlela yokufaka idatha esuka ku-DataFrame yethu kuthebula elithi "example_table" kusizindalwazi se-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.

Okuthunyelwe okuhlobene:

Shiya amazwana