-
Notifications
You must be signed in to change notification settings - Fork 0
/
dboperations.py
75 lines (63 loc) · 2.39 KB
/
dboperations.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
import psycopg2
def conection():
conn = psycopg2.connect(host="134.209.45.101", database="p2p", user="p2p", password="p42fUEoUpfAp2b7QsibgIT4PCWrp4wCW")
return conn
# Store procedure para actualizar datos
def act_prices(pcrypto, pfiat, pamount):
conn = conection()
try:
# create a cursor object for execution
cursor = conn.cursor()
cursor.execute("BEGIN")
cursor.callproc('act_prices', (pcrypto, pfiat, pamount, )) # Llamado de procedimiento almacenado en postgres
cursor.execute("COMMIT")
cursor.close # Cerrar cursor
# Llamado de store procedure para actualizar tabla t_historical
except (psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close() # Cerrando conección
return
# Store procedure para actualizar datos
def act_dolar(pamount):
conn = conection()
try:
# create a cursor object for execution
cursor = conn.cursor()
cursor.execute("BEGIN")
cursor.callproc('act_dolar', (pamount, )) # Llamado de procedimiento almacenado en postgres
cursor.execute("COMMIT")
cursor.close # Cerrar cursor
# Llamado de store procedure para actualizar tabla t_historical
except (psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close() # Cerrando conección
return
import psycopg2
from psycopg2 import sql
def act_prices_batch(data):
# Connect to your postgres DB
conn = psycopg2.connect(host="134.209.45.101", database="p2p", user="p2p", password="p42fUEoUpfAp2b7QsibgIT4PCWrp4wCW")
# Open a cursor to perform database operations
cur = conn.cursor()
# SQL query for deleting old records
delete_query = sql.SQL("DELETE FROM p2p_prices WHERE crypto = %s AND fiat = %s")
# SQL query for inserting new records
insert_query = sql.SQL("INSERT INTO p2p_prices (crypto, fiat, value, date) VALUES (%s, %s, %s, CURRENT_DATE)")
try:
for item in data:
# Delete old record
cur.execute(delete_query, (item[0], item[1]))
# Insert new record
cur.execute(insert_query, item)
# Commit the transaction
conn.commit()
except Exception as e:
print(f"Failed to insert data into database. {e}")
finally:
# Close the cursor and connection
cur.close()
conn.close()