Skip to content

Instantly share code, notes, and snippets.

@vaintrub

vaintrub/data_loader.py

Created December 21, 2021 00:21
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save vaintrub/2be95e6672e4ff029d64f741c4462384 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save vaintrub/2be95e6672e4ff029d64f741c4462384 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
data_loader.py
#!/usr/bin/env python3
from re import L
import psycopg2
import random
from faker import Faker
fake = Faker()
conn = psycopg2.connect(
database="postgres",
user="postgres",
password="secret",
host="127.0.0.1",
port="5432"
)
cursor = conn.cursor()
def insert_data(cursor, table_name, data):
INSERT_QUERY = "INSERT INTO {table} ({fields}) VALUES ({values})"
fields = list(data.keys())
values = []
for field in fields:
if type(data[field]) is str:
data[field] = f"'{data[field]}'"
elif type(data[field]) is list: # only for integers
data[field] = [str(val) for val in data[field]]
data[field] = f"'{{{','.join(data[field])}}}'"
values.append(str(data[field]))
sql = INSERT_QUERY.format(
table = table_name,
fields = ','.join(fields),
values = ','.join(values)
)
print(sql)
cursor.execute(sql)
N = 1000 # Count added rows in each space
table_name = 'depository'
print(f"Start load data to '{table_name}' table...")
cursor.execute('''CREATE TABLE IF NOT EXISTS depository (
id BIGSERIAL NOT NULL PRIMARY KEY,
purchase_date TIMESTAMP WITH TIME ZONE NOT NULL,
purchase_price DECIMAL NOT NULL,
sale_price DECIMAL NOT NULL,
invest_unit_id BIGINT NOT NULL,
unit_count BIGINT NOT NULL,
sale_date TIMESTAMP WITH TIME ZONE NOT NULL,
owner_id BIGINT NOT NULL,
is_owner_fond boolean NOT NULL,
currency_id INTEGER NOT NULL
);''')
for i in range(N):
data = {
'purchase_date': str(fake.date_time_this_century()),
'purchase_price': random.randrange(1550, 31323819)/100,
'sale_price': random.randrange(1550, 31323819)/1000,
'invest_unit_id': random.randrange(1, N),
'unit_count': random.randrange(1, 31323),
'sale_date': str(fake.date_time_this_century()),
'owner_id': random.randrange(1, N),
'is_owner_fond': random.choice([False, True]),
'currency_id': random.randrange(1, N),
}
insert_data(cursor, table_name, data)
conn.commit()
print(f"Data loaded into '{table_name}' table successfully\n")
table_name = 'owners'
print(f"Start load data to '{table_name}' table...")
cursor.execute('''CREATE TABLE IF NOT EXISTS owners (
id BIGSERIAL NOT NULL PRIMARY KEY,
broker_id INTEGER NOT NULL,
license CHARACTER VARYING(128) NOT NULL,
is_individual BOOLEAN NOT NULL
);''')
for i in range(N):
data = {
'broker_id': random.randrange(1, N),
'license': fake.bban(),
'is_individual': random.choice([False, True])
}
insert_data(cursor, table_name, data)
conn.commit()
print(f"Data loaded into '{table_name}' table successfully\n")
table_name = 'invest_unit_prices'
print(f"Start load data to '{table_name}' table...")
cursor.execute('''CREATE TABLE IF NOT EXISTS invest_unit_prices (
golden_id INTEGER NOT NULL,
price DECIMAL NOT NULL,
currency_id INTEGER NOT NULL
);''')
for i in range(N):
data = {
'golden_id': random.randrange(1, N),
'price': random.randrange(1550, 31323819)/1000,
'currency_id': random.randrange(1, N)
}
insert_data(cursor, table_name, data)
conn.commit()
print(f"Data loaded into '{table_name}' table successfully\n")
table_name = 'issuers'
print(f"Start load data to '{table_name}' table...")
cursor.execute('''CREATE TABLE IF NOT EXISTS issuers (
id BIGSERIAL NOT NULL PRIMARY KEY,
code INTEGER NOT NULL,
country CHARACTER VARYING(45) NOT NULL,
company_name CHARACTER VARYING(45) NOT NULL,
license CHARACTER VARYING(45) NOT NULL,
issuer_type CHARACTER VARYING(45) NOT NULL,
foundation CHARACTER VARYING(128) NOT NULL,
info CHARACTER VARYING(256) NOT NULL
);''')
for i in range(N):
data = {
'code': random.randrange(1, N),
'country': fake.country_code(),
'company_name': fake.company(),
'license': fake.bban(),
'issuer_type': random.choice(['fond', 'company']),
'foundation': str(fake.date_time_this_century()),
'info': ""
}
insert_data(cursor, table_name, data)
conn.commit()
print(f"Data loaded into '{table_name}' table successfully\n")
table_name = 'dividends'
print(f"Start load data to '{table_name}' table...")
cursor.execute('''CREATE TABLE IF NOT EXISTS dividends (
id BIGSERIAL NOT NULL PRIMARY KEY,
invest_unit_id INTEGER NOT NULL,
amount DECIMAL NOT NULL,
currency_id INTEGER NOT NULL
);''')
for i in range(N):
data = {
'invest_unit_id': random.randrange(1, N),
'amount': random.randrange(1550, 31323819)/1000,
'currency_id': random.randrange(1, N)
}
insert_data(cursor, table_name, data)
conn.commit()
print(f"Data loaded into '{table_name}' table successfully\n")
table_name = 'shares'
print(f"Start load data to '{table_name}' table...")
cursor.execute('''CREATE TABLE IF NOT EXISTS shares (
id BIGSERIAL NOT NULL PRIMARY KEY,
share_desc CHARACTER VARYING(45) NOT NULL,
total_count INTEGER NOT NULL
);''')
for i in range(N):
data = {
'share_desc': "Some description",
'total_count': random.randrange(1000000, 1000000000)
}
insert_data(cursor, table_name, data)
conn.commit()
print(f"Data loaded into '{table_name}' table successfully\n")
table_name = 'forecasts'
print(f"Start load data to '{table_name}' table...")
cursor.execute('''CREATE TABLE IF NOT EXISTS forecasts (
id BIGSERIAL NOT NULL PRIMARY KEY,
created_at TIMESTAMP WITH TIME ZONE NOT NULL,
invest_unit_id INTEGER NOT NULL,
amount DECIMAL NOT NULL,
currency_id INTEGER NOT NULL,
issuer_id INTEGER NOT NULL
);''')
for i in range(N):
data = {
'currency_id': random.randrange(1, N),
'issuer_id': random.randrange(1, N),
'amount': random.randrange(1550, 31323819)/1000,
'invest_unit_id': random.randrange(1, N),
'created_at': str(fake.date_time_this_century())
}
insert_data(cursor, table_name, data)
conn.commit()
print(f"Data loaded into '{table_name}' table successfully\n")
table_name = 'indicators'
print(f"Start load data to '{table_name}' table...")
cursor.execute('''CREATE TABLE IF NOT EXISTS indicators (
id BIGSERIAL NOT NULL PRIMARY KEY,
invest_unit_id INTEGER NOT NULL,
eps REAL NOT NULL,
roe REAL NOT NULL,
roa REAL NOT NULL,
ebitda REAL NOT NULL
);''')
for i in range(N):
data = {
'invest_unit_id': random.randrange(1, N),
'eps': random.randrange(1550, 31323819)/1000,
'roe': random.randrange(1550, 31323819)/1000,
'roa': random.randrange(1550, 31323819)/1000,
'ebitda': random.randrange(1550, 31323819)/1000
}
insert_data(cursor, table_name, data)
conn.commit()
print(f"Data loaded into '{table_name}' table successfully\n")
table_name = 'invest_units'
print(f"Start load data to '{table_name}' table...")
cursor.execute('''CREATE TABLE IF NOT EXISTS invest_units (
id BIGSERIAL NOT NULL PRIMARY KEY,
unit_type INTEGER NOT NULL,
category_id INTEGER NOT NULL,
issuer_id INTEGER NOT NULL,
golden_id INTEGER NOT NULL,
ticker CHARACTER VARYING(8) NOT NULL
);
''')
for i in range(N):
data = {
'unit_type': random.randrange(1, N),
'category_id': random.randrange(1, N),
'issuer_id': random.randrange(1, N),
'golden_id': random.randrange(1, N),
'ticker': fake.swift(length=8)
}
insert_data(cursor, table_name, data)
conn.commit()
print(f"Data loaded into '{table_name}' table successfully\n")
table_name = 'exchange_rate'
print(f"Start load data to '{table_name}' table...")
cursor.execute('''CREATE TABLE IF NOT EXISTS exchange_rate (
id BIGSERIAL NOT NULL PRIMARY KEY,
cur_id2 INTEGER NOT NULL,
cur_id1 INTEGER NOT NULL
);''')
for i in range(N):
data = {
'cur_id2': random.randrange(1, N),
'cur_id1': random.randrange(1, N),
}
insert_data(cursor, table_name, data)
conn.commit()
print(f"Data loaded into '{table_name}' table successfully\n")
table_name = 'currency'
print(f"Start load data to '{table_name}' table...")
cursor.execute('''CREATE TABLE IF NOT EXISTS currency (
id BIGSERIAL NOT NULL PRIMARY KEY,
country CHARACTER VARYING(45) NOT NULL,
cur_name CHARACTER VARYING(45) NOT NULL,
code INTEGER NOT NULL
);''')
for i in range(N):
cur = fake.currency()
data = {
'country': cur[1],
'cur_name': cur[0],
'code': random.randrange(23, 999)
}
insert_data(cursor, table_name, data)
conn.commit()
print(f"Data loaded into '{table_name}' table successfully\n")
table_name = 'profile_type'
print(f"Start load data to '{table_name}' table...")
cursor.execute('''
CREATE TABLE IF NOT EXISTS profile_type (
id BIGSERIAL NOT NULL PRIMARY KEY,
prof_name CHARACTER VARYING(45) NOT NULL,
grants INTEGER[] NOT NULL
);''')
for i in range(N):
data = {
'prof_name': fake.simple_profile()['username'],
'grants': random.choice([[1, 2], [0], [1, 3]]),
}
insert_data(cursor, table_name, data)
conn.commit()
print(f"Data loaded into '{table_name}' table successfully\n")
table_name = 'category'
print(f"Start load data to '{table_name}' table...")
cursor.execute('''CREATE TABLE IF NOT EXISTS category (
id BIGSERIAL NOT NULL PRIMARY KEY,
category_name CHARACTER VARYING(45) NOT NULL
);''')
for i in range(5):
data = {
'category_name': random.choice(['manager', 'admin', 'user'])
}
insert_data(cursor, table_name, data)
conn.commit()
print(f"Data loaded into '{table_name}' table successfully\n")
table_name = 'profiles'
print(f"Start load data to '{table_name}' table...")
cursor.execute('''CREATE TABLE IF NOT EXISTS profiles (
id BIGSERIAL NOT NULL PRIMARY KEY,
username CHARACTER VARYING(45) NOT NULL,
passport_id INTEGER NOT NULL,
updated_at TIMESTAMP WITH TIME ZONE NOT NULL,
bank_profile_id INTEGER NOT NULL,
created_at TIMESTAMP WITH TIME ZONE NOT NULL
);''')
for i in range(N):
data = {
'username': fake.simple_profile()['username'],
'passport_id': random.randrange(1, N),
'updated_at': str(fake.date_time_this_century()),
'bank_profile_id': random.randrange(1, N),
'created_at': str(fake.date_time_this_century())
}
insert_data(cursor, table_name, data)
conn.commit()
print(f"Data loaded into '{table_name}' table successfully\n")
table_name = 'contacts'
print(f"Start load data to '{table_name}' table...")
cursor.execute('''CREATE TABLE IF NOT EXISTS contacts (
id BIGSERIAL NOT NULL PRIMARY KEY,
profile_id INTEGER NOT NULL,
contact_type CHARACTER VARYING(64) NOT NULL,
contact_value CHARACTER VARYING(256) NOT NULL
);''')
for i in range(N):
cont_type = random.choice(['phone', 'email'])
if cont_type == 'phone':
cont_value = fake.phone_number()
else:
cont_value = fake.email()
data = {
'contact_value': cont_value,
'profile_id': random.randrange(1, N),
'contact_type': cont_type
}
insert_data(cursor, table_name, data)
conn.commit()
print(f"Data loaded into '{table_name}' table successfully\n")
table_name = 'passport'
print(f"Start load data to '{table_name}' table...")
cursor.execute('''CREATE TABLE IF NOT EXISTS passport (
id BIGSERIAL NOT NULL PRIMARY KEY,
doc_type CHARACTER VARYING(64) NOT NULL,
first_name CHARACTER VARYING(64) NOT NULL,
last_name CHARACTER VARYING(64) NOT NULL,
patronymic CHARACTER VARYING(64) NOT NULL,
serial_num CHARACTER VARYING(16) NOT NULL,
doc_number CHARACTER VARYING(16) NOT NULL
);''')
for i in range(N):
data = {
'doc_type': 'passport',
'first_name': fake.first_name(),
'last_name': fake.last_name(),
'patronymic': '',
'serial_num': random.randrange(10, 99),
'doc_number': random.randrange(234234, 935921)
}
insert_data(cursor, table_name, data)
conn.commit()
print(f"Data loaded into '{table_name}' table successfully\n")
table_name = 'profile_settings'
print(f"Start load data to '{table_name}' table...")
cursor.execute('''CREATE TABLE IF NOT EXISTS profile_settings (
id BIGSERIAL NOT NULL PRIMARY KEY,
tarrif_id INTEGER NOT NULL,
sms_on BOOLEAN NOT NULL,
stock_exch_id INTEGER NOT NULL,
margin_trading_on BOOLEAN NOT NULL,
overnight_on BOOLEAN NOT NULL,
updated_at TIMESTAMP WITH TIME ZONE NOT NULL
);
''')
for i in range(N):
data = {
'tarrif_id': random.randrange(1, N),
'sms_on': random.choice([False, True]),
'stock_exch_id': random.randrange(1, N),
'margin_trading_on': random.choice([False, True]),
'overnight_on': random.choice([False, True]),
'updated_at': str(fake.date_time_this_century())
}
insert_data(cursor, table_name, data)
conn.commit()
print(f"Data loaded into '{table_name}' table successfully\n")
table_name = 'tarrif_plans'
print(f"Start load data to '{table_name}' table...")
cursor.execute('''CREATE TABLE IF NOT EXISTS tarrif_plans (
id BIGSERIAL NOT NULL PRIMARY KEY,
tarrid_name CHARACTER VARYING(64) NOT NULL,
comission_amount DECIMAL NOT NULL,
currency_id INTEGER NOT NULL,
availale_categories INTEGER[] NOT NULL
);''')
for i in range(N):
data = {
'tarrid_name': random.randrange(1, N),
'comission_amount': random.randrange(112, 5000)/1000,
'currency_id': random.randrange(1, N),
'availale_categories': random.choice([[1, 4, 3], [5, 3], [12, 4]]),
}
insert_data(cursor, table_name, data)
conn.commit()
print(f"Data loaded into '{table_name}' table successfully\n")
table_name = 'requests'
print(f"Start load data to '{table_name}' table...")
cursor.execute('''CREATE TABLE IF NOT EXISTS requests (
id BIGSERIAL NOT NULL PRIMARY KEY,
is_closed BOOLEAN NOT NULL,
invest_unit_id INTEGER NOT NULL,
amount DECIMAL NOT NULL,
count_units INTEGER NOT NULL,
is_temp BOOLEAN NOT NULL,
bury_date TIMESTAMP WITH TIME ZONE NOT NULL
);''')
for i in range(N):
data = {
'is_closed': random.choice([False, True]),
'amount': random.randrange(1550, 31323819)/1000,
'invest_unit_id': random.randrange(1, N),
'count_units': random.randrange(124, 24523454),
'is_temp': random.choice([False, True]),
'bury_date': str(fake.date_time_this_century())
}
insert_data(cursor, table_name, data)
conn.commit()
print(f"Data loaded into '{table_name}' table successfully\n")
table_name = 'accounts'
print(f"Start load data to '{table_name}' table...")
cursor.execute('''CREATE TABLE IF NOT EXISTS accounts (
id BIGSERIAL NOT NULL PRIMARY KEY,
acc_name CHARACTER VARYING(64) NOT NULL,
acc_type CHARACTER VARYING(64) NOT NULL,
pan CHARACTER VARYING(64) NOT NULL,
created_at TIMESTAMP WITH TIME ZONE NOT NULL,
updated_at TIMESTAMP WITH TIME ZONE NOT NULL,
profile_id INTEGER NOT NULL
);''')
for i in range(N):
data = {
'acc_name': 'Credit card',
'acc_type': fake.credit_card_provider(),
'pan': fake.credit_card_number(),
'created_at': str(fake.date_time_this_century()),
'updated_at': str(fake.date_time_this_century()),
'profile_id': random.randrange(1, N)
}
insert_data(cursor, table_name, data)
conn.commit()
print(f"Data loaded into '{table_name}' table successfully\n")
table_name = 'operations'
print(f"Start load data to '{table_name}' table...")
cursor.execute('''CREATE TABLE IF NOT EXISTS operations (
id BIGSERIAL NOT NULL PRIMARY KEY,
oper_type character varying(45) NOT NULL,
profile_id INTEGER NOT NULL,
account_id INTEGER NOT NULL,
is_comission BOOLEAN NOT NULL,
depository_id INTEGER NOT NULL,
amount DECIMAL NOT NULL,
currency_id INTEGER NOT NULL
);''')
for i in range(N):
data = {
'oper_type': random.choice(['devidends', 'buy', 'sale', 'comission']),
'profile_id': random.randrange(1, N),
'is_comission': random.choice([True, False]),
'account_id': random.randrange(1, N),
'amount': random.randrange(1550, 31323819)/1000,
'depository_id': random.randrange(1, N),
'currency_id': random.randrange(1, N),
}
insert_data(cursor, table_name, data)
conn.commit()
print(f"Data loaded into '{table_name}' table successfully\n")
conn.close()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment