A cikin duniyar nazarin bayanai, sarrafa bayanan jerin lokaci abu ne mai mahimmanci. Ɗaya daga cikin ɗakunan karatu da aka fi amfani da su don wannan dalili shine ** pandas ** a cikin harshen shirye-shiryen Python. Ayyukan gama gari lokacin aiki tare da bayanan jerin lokaci shine canza bambance-bambancen lokaci tsakanin al'amura daban-daban zuwa daidaitaccen tsari. Wannan shine inda pandas Timedelta ya zo da amfani. Koyaya, lokacin aiki tare da bayanan bayanai kamar PostgreSQL, adana waɗannan timedeltas na iya zama ɗan wahala. A cikin wannan labarin, za mu tattauna yadda ake canza pandas Timedelta zuwa tsarin da za a iya adana shi a PostgreSQL, da kuma dawo da shi yayin da yake riƙe da ainihin wakilcinsa.
Maganin Matsala
Maganin wannan matsala ya haɗa da amfani da pandas da ɗakunan karatu na psycopg2, waɗanda aka fi amfani da su don sarrafa bayanai da sarrafa bayanan PostgreSQL, bi da bi. Laburaren psycopg2 yana da tallafi don sarrafa timedeltas, ta amfani da nau'in bayanan 'tazara' a cikin PostgreSQL. Za mu yi amfani da wannan fasalin don adana pandas Timedelta a cikin PostgreSQL kuma mu dawo da shi cikin tsarin da ya dace.
Da farko, bari mu shigo da dakunan karatu da suka dace kuma mu kafa haɗin kai zuwa bayananmu na 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 da Tazarar PostgreSQL
pandas Timedelta kayan aiki ne mai ƙarfi don bayyana bambance-bambancen lokaci a daidaitaccen hanya kuma ana iya karantawa. Yana da sauƙi don ƙirƙira da sarrafa abubuwan timedelta a cikin pandas, amma idan ana batun adana su a cikin bayanan PostgreSQL, muna buƙatar canza su zuwa tsarin da ya dace.
PostgreSQL yana ba da nau'in bayanan 'tazara' don adana tazarar lokaci. Wannan nau'in bayanan na iya wakiltar tazarar lokaci a cikin ƙima daban-daban, kamar kwanaki, sa'o'i, mintuna, da sakan. Domin adana pandas Timedelta a cikin bayanan PostgreSQL, muna buƙatar canza shi zuwa tazarar PostgreSQL.
Bari mu ƙirƙiri samfurin pandas DataFrame tare da ginshiƙin 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)
Yanzu bari mu ƙirƙiri aiki don saka wannan bayanan a cikin bayananmu na PostgreSQL kuma mu canza bayanan Timedelta zuwa tazara mai dacewa da 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()
Amfani da wannan aikin, zamu iya saka bayanan Pandas DataFrame a cikin bayanan PostgreSQL:
for _, row in df.iterrows():
event_name, time, difference = row['event_name'], row['time'], row['difference']
insert_data(event_name, time, difference, conn)
Ana dawo da Timedeltas daga PostgreSQL
Da zarar an adana bayanan Pandas Timedelta a cikin PostgreSQL a matsayin tazara, za mu iya dawo da su cikin sauƙi mu mayar da su pandas Timedeltas yayin karanta bayanan.
Bari mu ƙirƙiri aiki don ɗauko bayanai daga teburin mu na 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
Tare da wannan aikin, za mu iya debo da buga bayanan daga bayananmu na PostgreSQL:
result = fetch_data(conn) print(result)
Bayanan da aka samo daga PostgreSQL yanzu yana da Timedeltas ɗin sa yadda ya kamata a matsayin pandas Timedeltas.
A ƙarshe, canza pandas Timedelta zuwa tsarin da ya dace da ajiya na PostgreSQL da dawo da shi a cikin ainihin tsari tsari ne mai sauƙi. Ta hanyar yin amfani da pandas masu ƙarfi da ɗakunan karatu na psycopg2, za mu iya sauƙin sarrafa bayanan jerin lokaci kuma mu kula da wakilcin da ya dace, duka a cikin nazarin bayanan mu da kuma a cikin ma'ajin bayanan mu.
