Welcome! This PostgreSQL repository will help you to understand PostgreSQL quickly with examples and sample database (imdb). In this repository's database (imdb), you will be introduced to a PostgreSQL sample database that you can use for learning and practicing PostgreSQL.
We will use the imdb database for demonstrating the features of PostgreSQL. This database is built up personally for learning purpose. The main purpose of this task was to get familiar with PostgreSQL and python adaptor of PostgreSQL (psycopg2).
This relationship is generaed by omnidb
This tabular relationship is generated by SchemaSpy
For detail overview of the tables, please navigate to Schema folder and click the index.html file.
1- actors – stores actors data including name and actor_id.
2- contents – stores films/tv_shows data such as title, release_year,content_rating,user_voting, etc.
3- content_actors – stores the relationships between contents and actors.
4- content_types – stores content types.
5- content_ratings - stores content ratings. https://en.wikipedia.org/wiki/TV_parental_guidelines_(US)
6- content_directors - stores the relationships between contens and directors.
7- content_genres - stores the relationships between contents and genres.
8- directors - stores the information about directors including name and director_id.
9- genres - stores the information about imdb genres. http://www.imdb.com/feature/genre/?ref_=tt_ov_inf
10- episode_list - stores the inforamtion about episodes for each season of the tv shows.
11- languages - stores the information about the languages. (independent table)
You need to create a new database in the PostgreSQL database server before loading database schema and data into the database.
First, launch the psql tool.
Second, enter account’s information to login to the PostgreSQL database server.
Third, enter the following statement to create a new dvdrental database.
CREATE DATABASE imdb;
PostgreSQL will create a new database named dvdrental.
There are multiple ways of loading imdb database
Navigate to from_imdb directory and run the below command. You will need beautiful soup, urrlib, and psycopg2 for this operation.
You need to input username, password, host, and database name in the imdb_upload_data.py file.
python3 imdb_upload_data.py
Navigate to from_csv directory and run the below command. from_csv parse the csv files available under the csv_files and upload the data to the db. You will need psycopg2 for this operation.
You need to input username, password, host, and database name in the imdb_upload_data.py file.
python3 uploading_from_csv.py
Navigate to from_pg_restore directory and run the command
pg_restore -u user -n public -d databasename '/path/to/imdb.tar'
- Linux
- Windows
- OS X