La xaliyay: pandas Timedelta ilaa postgres

Dunida falanqaynta xogta, maaraynta xogta taxanaha wakhtiga waa arrin muhiim ah. Mid ka mid ah maktabadaha inta badan loo isticmaalo ujeedadan waa ** pandas ** luqadda barnaamijka Python. Hawsha caadiga ah marka lagu shaqeynayo xogta taxanaha waqti waa in loo beddelo kala duwanaanshaha waqtiga ee u dhexeeya dhacdooyinka kala duwan qaab caadi ah. Tani waa meesha pandas Timedelta uu ku anfacayo. Si kastaba ha noqotee, markaad la shaqeyneyso xog-ururinta sida PostgreSQL, kaydinta timedeltas-kan waxay noqon kartaa xoogaa dhib badan. Maqaalkan, waxaan kaga hadli doonaa sida loogu beddelo pandas Timedelta qaab lagu kaydin karo PostgreSQL, oo ​​aan soo saarno iyada oo la ilaalinayo matalaaddeeda saxda ah.

Xalka Dhibaatada

Xalka dhibaatadan waxay ku lug leedahay isticmaalka pandas iyo psycopg2 maktabadaha, kuwaas oo si weyn loogu isticmaalo wax ka qabashada xogta iyo maareynta xogta PostgreSQL, siday u kala horreeyaan. Maktabada psycopg2 waxay taageero u haysaa wax ka qabashada timedeltas, iyadoo la adeegsanayo nooca xogta 'interval' ee PostgreSQL. Waxaan ka faa'iidaysan doonaa sifadan si aan u kaydinno pandas Timedelta PostgreSQL oo aan dib ugu soo ceshano qaabkeeda saxda ah.

Marka hore, aynu soo dejinno maktabadaha lagama maarmaanka ah oo aan samayno isku xidhka xogtayada PostgreSQL.

import pandas as pd
import psycopg2

conn = psycopg2.connect(database="your_database",
                        user="your_user",
                        password="your_password",
                        host="your_host",
                        port="your_port")

pandas Timedelta iyo PostgreSQL dhexda

pandas Timedelta waa qalab awood leh oo lagu muujiyo kala duwanaanshaha waqtiga si joogto ah oo la akhriyi karo. Way fududahay in la abuuro oo lagu maamulo walxaha timedelta pandas, laakiin marka ay timaado in lagu kaydiyo xogta PostgreSQL, waxaan u baahanahay inaan u bedelno qaabka ku haboon.

PostgreSQL waxay bixisaa nooca xogta 'interval' si loo kaydiyo inta u dhaxaysa. Nooca xogtani waxa ay mateli kartaa wakhti taako kala duwan, sida maalmo, saacadood, daqiiqado, iyo ilbidhiqsiyo. Si loo kaydiyo pandas Timedelta ee xogta PostgreSQL, waxaan u baahanahay inaan u bedelno dhexda PostgreSQL.

Aan abuurno muunad pandas DataFrame leh tiir Timedelta:

data = {'event_name': ['start', 'end'],
        'time': [pd.Timestamp('2021-01-01'), pd.Timestamp('2021-01-03')]}
df = pd.DataFrame(data)
df['difference'] = df['time'].diff()
print(df)

Hadda aan abuurno shaqo si aan xogtan u galno xogtayada PostgreSQL oo aan u beddelno xogta Timedelta mid ku habboon PostgreSQL.

def insert_data(event_name, time, difference, conn):
    query = """
    INSERT INTO timedeltas (event_name, time, difference)
    VALUES (%s, %s, %s)
    """
    with conn.cursor() as cur:
        cur.execute(query, (event_name, time, difference))
    conn.commit()

Isticmaalka shaqadan, waxaan gelin karnaa pandas DataFrame xogta xogta PostgreSQL:

for _, row in df.iterrows():
    event_name, time, difference = row['event_name'], row['time'], row['difference']
    insert_data(event_name, time, difference, conn)

Ka soo celinta Timedeltas PostgreSQL

Marka xogta pandas Timedelta lagu kaydiyo PostgreSQL inta u dhaxaysa, si fudud ayaan u soo ceshan karnaa oo aan dib ugu celin karnaa pandas Timedeltas inta aan akhrineyno xogta.

Aan abuurno shaqo si aan xogta uga soo saarno miiska PostgreSQL:

def fetch_data(conn):
    query = "SELECT event_name, time, difference FROM timedeltas"
    data = pd.read_sql(query, conn)
    data['difference'] = pd.to_timedelta(data['difference'])
    return data

Shaqadan, waxaan ka soo qaadan karnaa oo ka daabacan karnaa xogta xogtayada PostgreSQL:

result = fetch_data(conn)
print(result)

Xogta laga soo qaatay PostgreSQL hadda waxay leedahay Timedeltas si sax ah loogu matalay pandas Timedeltas.

Gebagebadii, u beddelashada pandas Timedelta qaab ku habboon kaydinta PostgreSQL iyo soo celinta qaabkeedii asalka ahaa waa hab toos ah. Anagoo ka faa'iidaysanayna pandas-yada xoogga badan iyo maktabadaha psycopg2, waxaan si sahlan u maarayn karnaa xogta taxanaha waqtiga oo aan ilaalin karnaa matalaaddeeda saxda ah, labadaba falanqaynta xogta iyo kaydinta xogtayada.