Connecting to PostgreSQL from psycopg2 erros and what now?

Q&A
,
#1

Hello everyone. This post has two sides and its kinda long:

Part I:
First, about some errors and overall understanding of the connection to PostgreSQL with psycopg:

My installation process was this one (on ubuntu 20.04):

  1. pip3 install psycopg2 (resulted in some errors, which were corrected by step 2)

2.sudo apt-get install libpq-dev and then pip3 install psycopg2

3.psql command gave the following error: psql: error: could not connect to server: FATAL: role “samyr” does not exist. (samyr is the current computer user)

  1. sudo -u createuser samyr Now psql gives another error psql: error: could not connect to server: FATAL: database “samyr” does not exist which makes sense because by default (if I’m not mistaken psql uses the same string for the user and db, so I only solved one of the problems)

5.I try the following code:

import psycopg2
conn = psycopg2.connect(dbname="postgres", user="postgres")
conn.autocommit = True
cursor = conn.cursor()
cursor.execute("CREATE TABLE notes(id integer PRIMARY KEY, body text, title text)")
conn.close()

Which gives the following error: FATAL: Peer authentication failed for user "postgres"
So I change the user argument to “samyr” which works. But for some reason that I cant understand, when I tried to create another users such as “testuser” using the same method (sudo -u postgres createuser testuser) it gives error: FATAL: Peer authentication failed for user “testuser”.

  1. I tried the following code:
import psycopg2
conn = psycopg2.connect(dbname="postgres", user="samyr")
conn.autocommit = True
cursor = conn.cursor()
cursor.execute("CREATE TABLE testtable(id integer PRIMARY KEY, body text, title text)")
cursor.execute("INSERT INTO testtable values (1,'Hello','Dataquest');")
x=cursor.execute("SELECT * FROM testtable;")
print(x)
conn.close()

Which returns “None” .

All of this was to give context for my questions:
Step 4. This isn’t how we learned how to create users, when should we use this method?

Step 5. The ‘‘postgress’’ db is a db that anyone in the whole world can try to access? Or is it only on my machine? Why did the peer authentication only worked for the user ‘‘samyr’’ and not for ‘‘testuser’’?

Step 6. Why is the code returning None?

Part II:

The next mission is regarding indexes using SQLite. So I’m left with the feeling of no closure in the matter. When should I use psql through the command line and when should I use it within python? And more importantly: What can I do with PostgreSQL on my own? Or will I only use it in a large company that uses it? Are there any databases that I can access on my own?

Thank you for reading so far, and I hope this doesn’t sound overly picky.

#2

hey @samyrhelou

First off, merits to very detailed and formatted question.

Coming to your queries, I am a student like you too, so my inputs are solely based on my level of understanding (I am no expert). Also, I don’t have experience with Ubuntu.

  • I googled this part error: FATAL: Peer authentication failed for user “postgres” and found this StackOverflow post. This may help you somewhat.

  • You will have to update this query to, as below: (one of the missions has some content for this I guess)

cursor.execute("SELECT * FROM testtable;")
x = cursor.fetchall() #this fetches all the rows and stores in list object x
print(x)
  • This postgres db is in your machine, so only you have the access to it right now unless you authorize other users to your database. This is more of an admin knowledge ie. DB administration, so if you are not that well versed with DB concepts, you can skip it for now, have a grasp of basics at an end-user level, and then take a deep dive for more technical and super-user level concepts. You have a lot of material available on the internet to start with.
  • I didn’t quite understand this.

Coming to part II:
Not sure what path at DQ you have taken or are exploring. So this might be a generic answer.

When it comes to accessing data, the source can be anything, a file (.txt, .csv, .xlsx, etc.), a database, or something else.

The idea behind introducing Sqlite3 and Postgres in the course path by DQ is to give us familiarity with two open-source and easily available databases so that we have hands-on practice of working with Python and SQL queries.

coming to using psql and importing module in python - these are like the two ways to access the database. you can directly use psql method when you are only trying to focus on SQL and DB part basically shapening your SQL knowledge or just want to have feel of using postgres console.
When it comes to python with postgres, that like using a front end to access a backend. Combining them together to make a project workflow, or learning them in parallel.

There are a lot of databases, that large corporations use such as Oracle, IBM DB2, SQL Server, and many others. All you need to understand is SQL querying. Then given any db which utilizes SQL, you are good to go!

Hope these inputs were somewhat helpful to you.

#3

Hey @Rucha, thanks for taking your time to answer my questions.

I completely forgot about the fetchall method, that one is totally on me.
About the “creating users” questions. I was just confused by the new need of creating a user via
sudo -u createuser samyr.

At last, what I really would like to know, is how to get a database to practice more SQL, because from what I’ve understood, it would be needed permissions to access some databases (which makes sense). So I suppose the only way is finding a database that’s been shared and work locally.

But overall your answer definitely helped. Thanks again =))

#4

hey @samyrhelou

you may refer to this article for a start and then google to further your understanding about creating the user

You can also google about external practice databases. there might be some. But I would like to know, why not use a clean slate and learn from scratch?
You have an empty database that you created during installation. why not use that to learn

  • how to create a table
  • how to insert data into it first manually then maybe via .csv or .txt or .xlsx (at this point it may be an advanced topic so you may keep it for later!)
  • how to query the table
  • how to create another table
  • how to join the two tables
  • how to create a view using this join (importantly understand what is a view!)
    and so on…
  • how to create another database perhaps.

I am not able to comprehend why do you need an external database to practice when you can do all of the above on your own system and from scratch. The external database will already have multiple tables with advanced concepts implemented, so how would a pre-prepared frozen pizza teach you how to make one! :thinking: :thought_balloon:

1 Like
#5

That’s a very good closing phrase. Thx for all the attention you’ve given so far, and I’m sorry for the delayed response. I will be following your recommended steps. Thanks again =))

#6

hey @samyrhelou

no problem about the delay. just glad to help a fellow student and in return take something in my own learning! :slight_smile: