[
Preparing Database¶
Let us setup the database along with tables to see how the batch loads are typically done.
- We have scripts and data set available in our GitHub repository. If you are using our environment the repository is already cloned under /data/retail_db.
- It have scripts to create tables with primary keys. Those scripts are generated from MySQL tables and refactored for Postgres. We will create the tables and load the data from the files.
- Script to create tables: create_db_tables_pg.sql
- Here are the commands to launch
psqland run scripts to create tables as well as load data into tables.
psql -U retail_user \
-h localhost \
-p 5432 \
-d retail_db \
-W
\i /data/retail_db/create_db_tables_pg.sql
In [1]:
%run 02_function_get_database_connection.ipynb
In [2]:
retail_connection
Out[2]:
<connection object at 0x7f3e143c47c0; dsn: 'user=itversity_sms_user password=xxx dbname=itversity_sms_db host=pg.itversity.com port=5432', closed: 0>
In [3]:
%load_ext sql
In [4]:
%env DATABASE_URL=postgresql://retail_user:itversity@pg.itversity.com:5432/retail_db
env: DATABASE_URL=postgresql://retail_user:itversity@pg.itversity.com:5432/retail_db
In [5]:
%%sql
SELECT * FROM orders LIMIT 10
(psycopg2.OperationalError) FATAL: password authentication failed for user "retail_user"
(Background on this error at: https://sqlalche.me/e/14/e3q8)
Connection info needed in SQLAlchemy format, example:
postgresql://username:password@hostname/dbname
or an existing connection: dict_keys([])
In [6]:
%%sql
SELECT * FROM order_items LIMIT 10
(psycopg2.OperationalError) FATAL: password authentication failed for user "retail_user"
(Background on this error at: https://sqlalche.me/e/14/e3q8)
Connection info needed in SQLAlchemy format, example:
postgresql://username:password@hostname/dbname
or an existing connection: dict_keys([])
]