Python: Linking your Database to your code with PostgreSQL

When using a PostgreSQL database with our Python code, there are a few things we need to do that are different from how we use SQLite.

Firstly, downloading Psycopg2 is what we need to do as it will allow us to establish a connection wit our database.
To Download we use: pip install psycopg2

Once this is done, we need to go into the code we are using and import the psycopg2 package like so:

Once done we now need to connect to our database. We do this in the following way:

Now how did we get this info? From our SQLshell. When we first open it, it will be whatever you set up with. For me, I used the defaults to do so as you can see below.

That’s it! Thats how you connect in but lets go a few steps further.

Once we have that, we need to create our cursor which allows us to go through our data line by line.

Then we need to execute a query which is what we need for things such as creating a table, etc in our database. Personally, I add something to be printed after that to see where in the code I am. It’s optional but I find it’s helpful.

From there, we need to both save what we have done and close our cursor, which would be done with conn.commit() and conn.close()

Now, say we need to create a table for our database, it would look like this:

While the flow of this code can vary depending on what you need to do, it follows this basic layout when connecting to your database and manipulating the data there.