def main():
setup_IPC()
config()
connect()
create_tables()
def main():
"""Extracts or transforms and loads the data at the users request."""
while True:
cmd = get_command()
if cmd == "C":
print("\nDrop then re-create all tables (~1 minute)")
cur, conn = connect()
create_tables(cur, conn)
elif cmd == "L":
# load data
print("\nLoading data (~20 minutes)")
cur, conn = connect()
load_staging_tables(cur, conn)
elif cmd == "I":
# insert data
print("\nInserting data (~2 minutes)")
cur, conn = connect()
insert_tables(cur, conn)
elif cmd == "Q":
break
conn.close()
def generate(conn, gtfs_map):
create_tables(conn)
print "Generating Hubway stops..."
index = Hubway().generate(conn, 0)
print "Generating commuter rail stops..."
index = MbtaCommuterRail().generate(conn, index, gtfs_map)
print "Generating heavy rail stops..."
index = MbtaHeavyRail().generate(conn, index, gtfs_map)
print "Generating NextBus stops..."
index = NextBus("mbta").generate(conn, index)
print index
def main():
schema_exists = False
quit = False
#read password
infile = open('password.txt', 'r')
password = infile.read()
#remove newline
password = password[:-1]
infile.close()
print('\nConnecting to database...\n')
#establish connection to db
mydb = connect_server(password)
#print(mydb)
#set cursor
mycursor = mydb.cursor()
#Make or remake curriculum database, then use it
#mycursor.execute('DROP DATABASE IF EXISTS ' + SCHEMA_NAME)
#mycursor.execute('CREATE DATABASE IF NOT EXISTS ' + SCHEMA_NAME)
#Connect to schema (db) within server
mydb = connect_db(password, SCHEMA_NAME)
print('Connected to database:', SCHEMA_NAME, '\n')
#reset mycursor
mycursor = mydb.cursor()
#reset mycursor
mycursor = mydb.cursor()
#create tables
print("Creating tables...\n")
create.create_tables(mycursor)
mycursor.execute("SHOW TABLES")
print("Here are the tables in the " + SCHEMA_NAME, "schema/database:")
for table in mycursor:
print(table[0])
print('\nOpening GUI...\n')
gui = g.MyGUI(mycursor, mydb)
def main():
"""
Initializes DB server connection and creates database tables.
Calls process_data for both data directories: song_data and log_data.
"""
conn = psycopg2.connect(
'host=127.0.0.1 dbname=sparkifydb user=student password=student')
cur = conn.cursor()
drop_tables(cur, conn)
create_tables(cur, conn)
process_data(cur, conn, filepath='data/song_data', func=process_song_file)
process_data(cur, conn, filepath='data/log_data', func=process_log_file)
conn.close()
def main():
"""
main function is to drive the whole pipe line of the project. it build the connection the database and then apply process_data twice
the first process_data is to process songfiles.
the secound is to process log files.
and then it colse the connection.
input: None
output None
"""
cur, conn = create_database()
create_tables(cur, conn)
process_data(cur, conn, filepath='data/song_data', func=process_song_file)
process_data(cur, conn, filepath='data/log_data', func=process_log_file)
conn.close()
def main():
""""
This first create target database and tables, then transfer data from source to target
Args:
- None
Returns:
None
"""
# get jdbc config
config_object = config.resolve()
# create owh db databases and tables
create_tables(config_object, "DWH_DB", dim_fact_create_table_queries,
dim_fact_drop_table_queries)
# transfer from oltp to dwh db
transfer_data(config_object)
def main():
start_time = time.time()
print('Starting time: ', start_time)
# setup aws cluster
config, ec2, s3, iam, redshift, cluster_props, conn, cur = create_cluster()
drop_tables(cur, conn, drop_table_queries)
aws_end_time = time.time()
print('AWS setup elapsed time: ', aws_end_time - start_time)
create_tables(cur, conn, create_table_queries)
# set configs with correct ARN
staging_events_copy, staging_songs_copy = set_staging_copy_params(config)
# load events staging table
load_staging_tables(cur, conn, staging_events_copy)
event_load_time = time.time()
print('Event load elapsed time: ', event_load_time - aws_end_time)
# load songs staging table
load_staging_tables(cur, conn, staging_songs_copy)
songs_load_time = time.time()
print('Song load elapsed time: ', songs_load_time - event_load_time)
# insert data from staging to main tables
execute_list_of_queries(cur, conn, insert_table_queries)
table_insert_time = time.time()
print('Table insert elapsed time: ', table_insert_time - songs_load_time)
# select counts form main tables
execute_select_queries(cur, select_table_queries)
# drop tables and delete the cluster
drop_tables(cur, conn, drop_table_queries)
drop_tables(cur, conn, drop_staging_table_queries)
conn.close()
delete_cluster(redshift,
config.get("DWH", "DWH_CLUSTER_IDENTIFIER"),
iam, config.get("DWH", "DWH_IAM_ROLE_NAME"),
config.get('IAM_ROLE', 'POLICY'))
print('Total elapsed time: ', time.time() - start_time)
def main(argv):
i_dl, i_loc, o_loc, o_db, i_file, o_file, o_db_name = parse_paramaters(
argv)
if i_dl:
data = download_data()
else:
file = open(i_file, "r")
data = load_data(file)
if o_db:
db, cursor = connect2host()
createdb(cursor, o_db_name)
create_tables(cursor)
export_to_database(data, cursor)
db.commit()
disconnect(db, cursor)
if o_loc and not i_loc:
file = open(o_file, "w")
export_to_file(data, file)
def create_testing_database(self):
engine = create_engine(settings.DATABASE_URI, pool_pre_ping=True)
SessionLocal = sessionmaker(autocommit=False,
autoflush=False,
bind=engine)
session = SessionLocal()
if "_test" not in str(engine.url):
print("Tests are running over production or development database")
print(f"Database url: {engine.url}")
raise (ValueError)
print()
print("*" * 80)
print("TEST DB OPERATIONS")
print("*" * 80)
create_tables(engine, session)
# Add User and Review
crud_user = CRUDUserFactory(session_db=session)
crud_book = CRUDBookFactory(session_db=session)
crud_review = CRUDReviewFactory(session_db=session)
new_user = user.UserCreate(
username=DBTestingData.TEST_USER,
password=DBTestingData.TEST_PSW,
)
user1 = crud_user.create(obj_in=new_user)
book1 = crud_book.get_by_column("title",
DBTestingData.BOOK_TITLE_W_REVIEW)[0]
new_review = review.ReviewCreate(
review_value="1",
review_comment="test comment",
user_id=user1.id,
book_id=book1.id,
)
review1 = crud_review.create(obj_in=new_review)
session.close() # noqa
engine.dispose()
print("-" * 80)
def create_mode(args):
"""
Initially drop then create all the required tables in both staging
and final
"""
try:
print("Dropping any existing tables...")
conn, cur = connect()
drop_tables(cur, conn)
print("Creating tables...")
create_tables(cur, conn)
conn.close()
except (Exception, psycopg2.Error) as error:
print("Error while creating tables: ", error)
return False
return True
def create_testing_database(self):
from src.repositories import RepositoryContainer
engine = RepositoryContainer.engine()
session = RepositoryContainer.DEFAULT_SESSIONFACTORY()
if "_test" not in str(engine.url):
print("Tests are running over production or development database")
print(f"Database url: {engine.url}")
raise (ValueError)
print()
print("*" * 80)
print("TEST DB OPERATIONS")
print("*" * 80)
create_tables(engine, session)
uow = RepositoryContainer.uow()
# Add User
with uow:
new_user = dict(username=DBTestingData.TEST_USER,
password=DBTestingData.TEST_PSW)
uow.repository.add_user(new_user)
uow.commit()
# Add Review for User
user = uow.repository.get_username(DBTestingData.TEST_USER)
book = uow.repository.get_book_by_like(
"title", DBTestingData.BOOK_TITLE_W_REVIEW)[0]
new_review = dict(
review_value="1",
review_comment="test comment",
user_id=user.id,
book_id=book.id,
)
uow.repository.add_review(new_review)
uow.commit()
print("-" * 80)
import MySQLdb
import sys
import json
from linkedin_data import get_profile_json_data
from create_tables import create_tables
from add_data_to_db import insert_data
static_json = {"schools": ["University of Illinois at Urbana-Champaign", "Illinois Mathematics and Science Academy"], "companies": ["SurveyMonkey", "Dev Bootcamp", "Zazzle", "Fileboard", "Ace Metrix", "AOL"], "name": "Henry Wang"}
generated_json = get_profile_json_data("https://www.linkedin.com/in/henrytwang")
generated_json_to_dict = json.loads(generated_json)
db1 = MySQLdb.connect(host="localhost",user="******",passwd="",db="testdb")
cursor = db1.cursor()
create_tables(cursor)
insert_data(cursor, converted_json_data_2)
# # open a database connection
# # be sure to change the host IP address, username, password and database name to match your own
# connection = MySQLdb.connect(
# host = 'localhost',
# user = '******',
# passwd = '',
# port = 3000)
# # prepare a cursor object using cursor() method
# cursor = connection.cursor ()
# # execute the SQL query using execute() method.
# cursor.execute ("SELECT VERSION()")
# # fetch a single row using fetchone() method.
# row = cursor.fetchone ()
def setup_db():
"""Run the create tables command"""
logger.info("Creating Database Tables")
create_tables(get_db_engine())
def get_json_files(path_to_directory):
concat_paths = lambda x: path_to_directory + '/' + x
file_filter = lambda x: '~lock' not in x and '.json' in x
files = os.listdir(path_to_directory)
files = filter(file_filter, files)
files = map(concat_paths, files)
return files
#get dictionary for looking up team objects by team code
def get_team_dict():
team_dict = {}
for team in Team.select():
team_dict[team.code] = team
return team_dict
def sanitize_string(string):
string = string.strip()
return string
if __name__ == '__main__':
drop_tables()
create_tables()
db.connect()
create_team_records()
create_athlete_records()
team_dict = get_team_dict()
import_games(PATH_TO_GAME_LIST, team_dict)
db.close()
def create_tables_script(init_tables: Param("Init tables", bool_arg) = False):
if init_tables is True:
create_tables()
for s in share_data:
cur.execute(sharesale_insert, s)
if i % 10000 == 0:
print(i)
conn.commit()
break
# print(' Inserting:', s)
i += 1
conn.commit()
print(' -- Finished!')
def show_first_rows(cur, conn, table, n):
cur.execute('SELECT * FROM {} LIMIT {}'.format(table, n))
conn.commit()
for row in cur.fetchall():
print(row, row[1].timestamp())
if __name__ == '__main__':
try:
input('This will drop all tables, press Enter to continue.')
cur, conn = connect.connect(autocommit=False)
drop_tables(cur, conn)
create_tables(cur, conn)
process_data(cur, conn, './data/ticks.csv')
show_first_rows(cur, conn, 'sharesales', 12)
finally:
conn.close()
from flask import Flask
from flask_restful import reqparse, abort, Api, Resource
from flask_jwt import JWT, jwt_required, current_identity
from security import authenticate, identity
from items import Item, ItemList
from users import UserRegister
from create_tables import create_tables
from test import insert_test_values
app = Flask(__name__)
api = Api(app)
app.config['SECRET_KEY'] = 'super-secret'
create_tables()
# insert_test_values()
jwt = JWT(app, authenticate, identity)
# парсер запроса
# Эндпоинты
api.add_resource(ItemList, '/items')
api.add_resource(Item, '/items/<name>')
api.add_resource(UserRegister, '/register')
if __name__ == '__main__':
app.run(debug=True)
if len(game['teams']['BLUE']['players'][0]['uniqueIdentifiers']
['leaguepedia']['runes']) > 0:
game_player_runes = sqlize_runes(game)
if len(game['teams']['BLUE']['players'][0]['uniqueIdentifiers']
['leaguepedia']['items']) > 0:
game_player_items = sqlize_items(game)
if len(game['teams']['BLUE']['players'][0]['uniqueIdentifiers']
['leaguepedia']['ss']) > 0:
game_player_summoner_spells = sqlize_summoner_spells(game)
def get_postgres_conn():
try:
conn = psycopg2.connect(host="db",
database="pro_league",
user="******",
password="******",
connect_timeout=1)
conn.close()
return conn
except:
return True
if __name__ == '__main__':
conn = ""
while True:
conn = get_postgres_conn()
create_tables(conn)
# collect(conn)
def main():
create_tables()
print('Hello!')
user_name = input('Provide user name: ')
login(username=user_name)
def setUpClass(cls):
create_tables()
""" Main Module"""
from config_module import config
from create_tables import main as create_tables
from etl import main as etl
from load_csv_files import main as load_csv_file
from question import main as question
from sql_queries.oltp import create_table_queries, drop_table_queries
"""
3 thins done in this project:
1- Creating original database and tables
2- Loading csv files data to original table
3- Creating new database and tables which is a fact and multiple dimension tables through sql
queries for transformation in etl process. Queries can be check in sql_queries.oltp module
"""
if __name__ == '__main__':
obj = config.resolve()
create_tables(obj, "OLTP_DB", create_table_queries, drop_table_queries)
load_csv_file()
etl()
question()
