Learn SQL in a browser with PostgreSQL and pgweb
PostgreSQL is a very versatile database. If you want to learn SQL, then a quick way to start is to 1) grab some data you want to analyze 2) insert into a PostgreSQL table and 3) use a SQL client such as pgweb and get started analyzing data. You can use any SQL client of your choice but pgweb is easy to use and is browser based which makes it a very convenient choice.
As with many of my posts, I'll use Docker to run PostgreSQL and pgweb.
START A POSTGRESQL CONTAINER
docker run -d -p 5432:5432 --name postgres_db -e POSTGRES_PASSWORD=postgres postgres
START A PGWEB CONTAINER
docker run -d -p 8081:8081 --link postgres_db:postgres_db -e DATABASE_URL=postgres://postgres:postgres@postgres_db:5432/postgres?sslmode=disable sosedoff/pgweb --readonly
--link flag here is being used to allow the pgweb container to access the PostgreSQL database running inside another container. Recall the
--name flag to set the name of the PostgreSQL container to
postgres_db. The name is now being used to point the pgweb container to where PostgreSQL is running. This is evident in the environment variable
DATABASE_URL that provides the connection details needed by pgweb to connect to PostgreSQL.
Go over to your browser and type
localhost:8081 to access pgweb. There isn't any data available yet which will be fixed below.
INSERT DATA INTO POSTGRESQL
In the snippet below, I use
pandas to grab the famous
iris dataset. I then define a connection using
sqlalchemy to the PostgreSQL container. Since I'm doing this on the host system, I can use
localhost to connect to the container. The containers by default are available on
localhost on the host system.
Finally I use
to_sql function to write the dataset to the database.
import pandas as pd from sqlalchemy import create_engine iris = pd.read_csv('https://raw.githubusercontent.com/mwaskom/seaborn-data/master/iris.csv') engine = create_engine('postgresql://postgres:postgres@localhost:5432/postgres') iris.to_sql("iris", engine, if_exists = 'replace', index=False, chunksize=1000)
Now when I go over to
localhost:8081 in my browser I see
iris in the list of tables.
PostgreSQL Persisting your PostgreSQL data inside your container requires you to set the appropriate
-v flags. Besides data persistence there are other factors you may have to deal with when running PostgreSQL inside a container. Please find more details here for some great suggestions on this topic.
pgweb You may want to limit access to the SQL client, run pgweb on a different port, allow read-only access to the database and so on. A large number of options can be found here. To enable these options you may need to create a custom Dockerfile for pgweb and enable the options of your choice. You can reuse the pgweb Dockerfile found here.