def test_query_string_must_be_provided(self):
task = PostgresExecute(
db_name="test", user="******", password="******", host="test"
)
with pytest.raises(ValueError) as exc:
task.run()
assert "A query string must be provided" == str(exc.value)
def test_query_string_must_be_provided(self):
task = PostgresExecute(db_name="test",
user="******",
password="******",
host="test")
with pytest.raises(ValueError,
match="A query string must be provided"):
task.run()
def insert_stations(list_of_tuples: list): #, password: str):
insert = 0
unique_key_violation = 0
#print(len(list_of_tuples))
insert = 0
unique_key_violation = 0
for row in list_of_tuples[1:2]:
print(row)
station = row[0]
latitude = row[2] if row[2] != '' else None
longitude = row[3] if row[3] != '' else None
elevation = row[4] if row[4] != '' else None
name = row[5]
try:
PostgresExecute(
db_name=local_config.DB_NAME, #'climatedb',
user=local_config.DB_USER, #'postgres',
host=local_config.DB_HOST, #'192.168.86.32',
port=local_config.DB_PORT, #5432,
query="""
insert into climate.noaa_global_temp_sites
(station, latitude, longitude, elevation, name)
values (%s, %s, %s, %s, %s)
""",
data=(station, latitude, longitude, elevation, name),
commit=True,
).run(password=PrefectSecret('NOAA_LOCAL_DB').run())
insert += 1
except UniqueViolation:
unique_key_violation += 1
except InvalidTextRepresentation as e:
print(e)
print(
f'STATION INSERT RESULT: inserted {insert} records | {unique_key_violation} duplicates'
)
def insert_records(filename):#list_of_tuples: list):#, waiting_for):
with open(filename) as read_obj:
csv_reader = reader(read_obj)
# Get all rows of csv from csv_reader object as list of tuples
list_of_tuples = list(map(tuple, csv_reader))
#insert = 0
if not list_of_tuples:
return
unique_key_violation = 0
new_list = []
for row in list_of_tuples[1:]:
# print(row)
date=row[1]
station=row[0]
latitude=row[2]# if row[2] != '' else None
longitude=row[3]# if row[3] != '' else None
elevation=row[4]# if row[4] != '' else None
temp=row[6]
temp_attributes=row[7]
dewp=row[8]
dewp_attributes=row[9]
slp=row[10]
slp_attributes=row[11]
stp=row[12]
stp_attributes=row[13]
visib=row[14]
visib_attributes=row[15]
wdsp=row[16]
wdsp_attributes=row[17]
mxspd=row[18]
gust=row[19]
max_v=row[20]
max_attributes=row[21]
min_v=row[22]
min_attributes=row[23]
prcp=row[24]
prcp_attributes=row[25]
sndp=row[26]
frshtt=row[27]
name=row[5]
new_tuple = (date, station, latitude, longitude, elevation, temp, temp_attributes, dewp,
dewp_attributes, slp, slp_attributes, stp, stp_attributes, visib, visib_attributes,
wdsp, wdsp_attributes, mxspd, gust, max_v, max_attributes, min_v, min_attributes,
prcp, prcp_attributes, sndp, frshtt, name)
new_list.append(new_tuple)
insert = 0
try:
PostgresExecuteMany(
db_name=local_config.DB_NAME, #'climatedb',
user=local_config.DB_USER, #'postgres',
host=local_config.DB_HOST, #'192.168.86.32',
port=local_config.DB_PORT, #5432,
query="""
insert into climate.noaa_global_daily_temps
(date, station, latitude, longitude, elevation, temp, temp_attributes, dewp, dewp_attributes, slp, slp_attributes,
stp, stp_attributes, visib, visib_attributes, wdsp, wdsp_attributes, mxspd, gust,
max, max_attributes, min, min_attributes, prcp, prcp_attributes, sndp, frshtt, name)
values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
""",
data=new_list,
#(date, station, temp, temp_attributes, dewp, dewp_attributes, slp, slp_attributes,
# stp, stp_attributes, visib, visib_attributes, wdsp, wdsp_attributes, mxspd, gust,
# max_v, max_attributes, min_v, min_attributes, prcp, prcp_attributes, sndp, frshtt),
commit=True,
).run(password=PrefectSecret('NOAA_LOCAL_DB').run())
insert = len(new_list)
# insert += 1
except UniqueViolation:
unique_key_violation += 1
try:
csv_filename = station + '.csv'
PostgresExecute(
db_name=local_config.DB_NAME, #'climatedb',
user=local_config.DB_USER, #'postgres',
host=local_config.DB_HOST, #'192.168.86.32',
port=local_config.DB_PORT, #5432,
query="""
insert into climate.csv_checker
(station, date_create, date_update, year)
values (%s, CURRENT_DATE, CURRENT_DATE, %s)
""",
data=(csv_filename, date[0:4]),
commit=True,
).run(password=PrefectSecret('NOAA_LOCAL_DB').run())
except UniqueViolation:
pass
print(f'RECORD INSERT RESULT: inserted {insert} records | {unique_key_violation} duplicates')
POSTGRES_PORT,
)
insert_user = PostgresExecute(
name="insert-user",
db_name=POSTGRES_DBNAME,
user=POSTGRES_USERNAME,
password=POSTGRES_PASSWORD,
host=POSTGRES_HOST,
port=POSTGRES_PORT,
query="""
INSERT INTO fantasy_analytics.users (
username,
created_on,
is_premium_member,
espn_team_id,
espn_team_name,
espn_team_abbrev,
league_id
) VALUES (
%s,
NOW(),
false,
%s,
%s,
%s,
%s
) ON CONFLICT DO NOTHING
""",
)
def insert_records(list_of_tuples: list, waiting_for):
#insert = 0
unique_key_violation = 0
new_list = []
for row in list_of_tuples[1:]:
date = row[1]
station = row[0]
temp = row[6]
temp_attributes = row[7]
dewp = row[8]
dewp_attributes = row[9]
slp = row[10]
slp_attributes = row[11]
stp = row[12]
stp_attributes = row[13]
visib = row[14]
visib_attributes = row[15]
wdsp = row[16]
wdsp_attributes = row[17]
mxspd = row[18]
gust = row[19]
max_v = row[20]
max_attributes = row[21]
min_v = row[22]
min_attributes = row[23]
prcp = row[24]
prcp_attributes = row[25]
sndp = row[26]
frshtt = row[27]
new_tuple = (date, station, temp, temp_attributes, dewp,
dewp_attributes, slp, slp_attributes, stp, stp_attributes,
visib, visib_attributes, wdsp, wdsp_attributes, mxspd,
gust, max_v, max_attributes, min_v, min_attributes, prcp,
prcp_attributes, sndp, frshtt)
new_list.append(new_tuple)
insert = len(new_list)
try:
PostgresExecuteMany(
db_name=local_config.DB_NAME, #'climatedb',
user=local_config.DB_USER, #'postgres',
host=local_config.DB_HOST, #'192.168.86.32',
port=local_config.DB_PORT, #5432,
query="""
insert into climate.noaa_global_daily_temps
(date, station, temp, temp_attributes, dewp, dewp_attributes, slp, slp_attributes,
stp, stp_attributes, visib, visib_attributes, wdsp, wdsp_attributes, mxspd, gust,
max, max_attributes, min, min_attributes, prcp, prcp_attributes, sndp, frshtt)
values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
""",
data=new_list,
#(date, station, temp, temp_attributes, dewp, dewp_attributes, slp, slp_attributes,
# stp, stp_attributes, visib, visib_attributes, wdsp, wdsp_attributes, mxspd, gust,
# max_v, max_attributes, min_v, min_attributes, prcp, prcp_attributes, sndp, frshtt),
commit=True,
).run(password=PrefectSecret('NOAA_LOCAL_DB').run())
# insert += 1
except UniqueViolation:
unique_key_violation += 1
try:
csv_filename = station + '.csv'
PostgresExecute(
db_name=local_config.DB_NAME, #'climatedb',
user=local_config.DB_USER, #'postgres',
host=local_config.DB_HOST, #'192.168.86.32',
port=local_config.DB_PORT, #5432,
query="""
insert into climate.csv_checker
(station, date_create, date_update, year)
values (%s, CURRENT_DATE, CURRENT_DATE, %s)
""",
data=(csv_filename, date[0:4]),
commit=True,
).run(password=PrefectSecret('NOAA_LOCAL_DB').run())
except UniqueViolation:
pass
print(
f'RECORD INSERT RESULT: inserted {insert} records | {unique_key_violation} duplicates'
)
from prefect.tasks.postgres import PostgresExecute
from settings import (
POSTGRES_DBNAME,
POSTGRES_USERNAME,
POSTGRES_PASSWORD,
POSTGRES_HOST,
POSTGRES_PORT,
)
insert_league = PostgresExecute(
name="insert-league",
db_name=POSTGRES_DBNAME,
user=POSTGRES_USERNAME,
password=POSTGRES_PASSWORD,
host=POSTGRES_HOST,
port=POSTGRES_PORT,
query="""
INSERT INTO fantasy_analytics.leagues (
id,
name,
year
) VALUES (
%s,
%s,
%s
) ON CONFLICT DO NOTHING
""",
)
user=POSTGRES_USERNAME,
password=POSTGRES_PASSWORD,
host=POSTGRES_HOST,
port=POSTGRES_PORT,
fetch="all",
query="""
SELECT * FROM information_schema.schemata;
""",
)
create_schema = PostgresExecute(
name="create-fantasy-schema",
db_name=POSTGRES_DBNAME,
user=POSTGRES_USERNAME,
password=POSTGRES_PASSWORD,
host=POSTGRES_HOST,
port=POSTGRES_PORT,
query="""
CREATE SCHEMA IF NOT EXISTS fantasy_analytics;
""",
)
"""
Example:
(1, 'League Example', 2020)
"""
create_leagues_table = PostgresExecute(
name="create-leagues-table",
db_name=POSTGRES_DBNAME,
user=POSTGRES_USERNAME,
password=POSTGRES_PASSWORD,
host=POSTGRES_HOST,
def test_construction(self):
task = PostgresExecute(db_name="test", user="******", host="test")
assert task.commit is False