Postgres CRUD operations with Python Part 1: [Create Table]
PostgreSQL [the most advanced open source db] is one of the most popular databases out there and almost everyone I know uses it for something. Be it data warehousing or just creating simple web apps, this right here is one freaking awesome dB.
In most cases, one would normally access and use PostgreSQL with the aid of PgAdmin which is Postgres’ administration and development platform. However, since we are all cool and awesome :-) we’ll try to play around with PostgreSQL by using Python. This is a very simple way to learn and train yourself on both PostgreSQL and Python.
It is also a medically proven way of making yourself feel like a genius :-).
Assumptions
Seeing as I am a very lazy person and I hate doing a lot of work, I am just gonna assume that you have PostgreSQL and Python installed and know at least your way around both e.g. creating a database, running a python program etc.
Can I really do that? [as in just assume you know stuff?] Hell yeah, I can! Being an adult is awesome, you can actually choose not to do stuff and nobody will force you :-)
As a way to make myself feel a little bit better about my laziness, here are a couple of links to get you started if you have no idea what we’re doing.
Download Postgres for Windows.
Installation tutorial.
Installation for Linux users.
Okay, Now that we are done with assumptions,
Getting to the real shit!
Time to create a database. You can do it in pgAdmin or in psql.
Give it an awesome name like peaches or whatever makes your heart tickle.
Open your favorite editor and install the psycopg2 python library if you don’t have it yet. On Windows, I use pip:
The message above tells I already have psycopg2 installed. If you don’t have it installed, running that command will install it.
Now, launch your favorite editor(I am using Sublime Text 3).
Create a new file and name it. Am naming my file pg_create.py
The first thing you do is import the newly downloaded library. Then, like a good human being, put your connection and the rest of your code inside a try.
The section below defines your connection parameters as you have set them.
As a personal way of doing things, I absolutely hate creating tables on the public schema so I will create a new schema and call it stage1. You don’t really have to do this.
Define your create table query in SQL. The triple quotes are used for any query that is more than one line long. You don’t have to use the same name as mine, call it whatever makes you happy e.g. sandwiches, cakes.
Here’s how the whole code looks like:
Go to pgAdmin and check, if everything worked out well, you have just created a table using python!
NB: All the code can be found in the Github repo.
