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()