Yakagadziriswa: tafura pandas kune postgresql

Munyika yekuongorora data uye kunyengera, imwe yeanonyanya kufarirwa Python maraibhurari ndeye pandas. Inopa akasiyana maturusi ane simba ekushanda nedata rakarongeka, zvichiita kuti zvive nyore kushandura, kuona uye kuongorora. Rimwe remabasa mazhinji angasangana nemuongorori wedata kupinza data kubva kune a CSV faira mu a PostgreSQL database. Muchikamu chino, tichakurukura maitiro ekuita basa iri nemazvo uye nemazvo tichishandisa ese ari maviri pandas uye psychopg2 library. Tichaongororawo mabasa akasiyana uye maraibhurari anobatanidzwa mukuita uku, tichipa kunzwisisa kwakadzama kwemhinduro.

Nhanganyaya kuPandas uye PostgreSQL

Pandas iraibhurari ine simba yePython inopa nyore-kushandisa-data zvimiro uye data manipulation mabasa ekuongorora data. Zvinonyanya kubatsira kana uchibata nemahombe data seti kana iwe paunoda kuita yakaoma data shanduko. PostgreSQL, kune rumwe rutivi, ndeye yemahara uye yakavhurika-sosi chinhu-relational dhatabhesi manejimendi system (ORDBMS) inosimbisa kuwedzera uye kutevedzera SQL. Inoshandiswa zvakanyanya kune yakakura-yakakura, yakaoma data manejimendi mabasa.

Zvino, ngatiti tine CSV faira rine hombe dataset, uye tinoda kuipinza muPostgreSQL dhatabhesi. Nzira yakajairika yekuzadzisa basa iri kushandisa Pandas mukubatana ne psycopg2 raibhurari, iyo inopa adapta yePostgreSQL dhatabhesi inotibvumira kutaurirana nayo tichishandisa Python.

Pandas: Kuverenga CSV mafaera

Nhanho yekutanga mukuita kwedu kuverenga zvirimo mufaira redu reCSV tichishandisa Pandas.

import pandas as pd

filename = "example.csv"
df = pd.read_csv(filename)

Iyi kodhi inoshandisa iyo pd.read_csv() function, iyo inoverenga CSV faira uye inodzorera chinhu cheDataFrame. Nechinhu cheDataFrame, tinogona kushandura uye kuongorora data.

Kubatanidza kune iyo PostgreSQL database

Nhanho inotevera ndeyekubatanidza kune yedu PostgreSQL dhatabhesi uchishandisa iyo psycopg2 raibhurari. Kuti tiite izvi, isu tinofanirwa kuisa iyo psycopg2 raibhurari, iyo inogona kuitwa uchishandisa pip:

pip install psycopg2

Kana raibhurari yaiswa, tinoda kubatana kune yedu PostgreSQL dhatabhesi:

import psycopg2

connection = psycopg2.connect(
    dbname="your_database_name",
    user="your_username",
    password="your_password",
    host="your_hostname",
    port="your_port",
)

The psycopg2.connect() basa rinogadzira chinongedzo nesevha yedatabase uchishandisa izvo zvakapihwa. Kana iyo yekubatanidza ikabudirira, basa rinodzorera chinhu chekubatanidza chatichashandisa kupindirana nedhatabhesi.

Kugadzira tafura muPostgreSQL

Zvino zvatine data redu muDataFrame chinhu uye chinongedzo kuPostgreSQL dhatabhesi, tinogona kugadzira tafura mudhatabhesi kuchengetedza data redu.

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

Mune ino kodhi snippet, isu tinotanga kugadzira chinhu checursor tichishandisa iyo connection.cursor() nzira. Iyo cursor inoshandiswa kuita dhatabhesi mashandiro sekugadzira matafura uye kuisa data. Tevere, tinotsanangura mubvunzo weSQL wekugadzira tafura, uye woita uchishandisa iyo cursor.execute() nzira. Pakupedzisira, tinopa shanduko kune database ne connection.commit().

Kuisa data muPostgreSQL dhatabhesi

Zvino zvatine tafura, tinogona kuisa iyo data kubva kune yedu DataFrame muPostgreSQL dhatabhesi tichishandisa iyo ku_sql() nzira inopiwa nePandas.

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)

Mune iyi kodhi snippet, isu tinotanga kugadzira injini yedatabase tichishandisa iyo gadzira_injini() basa reraibhurari yeSQLAlchemy, inoda tambo yekubatanidza ine dhatabhesi yedu. Zvadaro, tinoshandisa ku_sql() nzira yekuisa iyo data kubva kune yedu DataFrame mu "example_table" tafura muPostgreSQL dhatabhesi.

Mukupedzisa, chinyorwa ichi chinopa gwara rakazara rekuti ungaunza sei data kubva kuCSV faira muPostgreSQL dhatabhesi uchishandisa Pandas uye psycopg2. Nekubatanidza kureruka kwekushandisa data muPandas nesimba uye scalability yePostgreSQL, tinogona kuwana mhinduro isina musono uye inoshanda kune iyo yakajairika basa rekupinza CSV data mudhatabhesi.