CODEWITHSUNDEEP
phppythonpostgresql
random2137
random2137

Reputation: 138

Get data from an API and saving into a Postgresql database

I'm starting in Python and I'm currently coding a script that gets the data from an API, processes it and save in a Postgresql database. I'm using Django and Postgresql, both of them are dockerized.

The code is getting the data from the API and showing for me, so that part is ok. The problem now is how I save it in the database.

I used to do that in PHP with Symfony and it was something along the lines of this:

PHP/Symfony

/*Movie*/
$movie_imdb_id = 'NULL';
$movie_status = $value->isPlaying;

$movie = new Movie();
$movie->setImdbId($movie_imdb_id);
$movie->setStatus($movie_status);

$entityManager->persist($movie);
$entityManager->flush();

I want to make the exact same thing, but in Python. Here it is my script so far.

Python/Django

#RestfulClient.py

import requests 
from requests.auth import HTTPDigestAuth
import json
import psycopg2

conn = psycopg2.connect(database="database", user="user", password="password", host="host", port="port")
url = "url_api"

myResponse = requests.get(url, verify = True)

if(myResponse.ok):
    jData = json.loads(myResponse.content)
    for f in jData:
      #print(json.dumps(jData, indent = 4, sort_keys = True))
      cur = conn.cursor()
      cur.execute("INSERT INTO table VALUES ('value', 'value')")
      conn.commit()
else:
    myResponse.raise_for_status()

My main problem is how to INSERT the data that I got in the Postgres tables, since they're object.

Thanks for any help!

EDIT 1

So, after reading the guide that @Chris post, I remade my code to something more readable

Python 2

import json
import urllib3
import psycopg2

conn = psycopg2.connect(database="pp-db", user="root", password="root", host="db", port="5432")
cur = conn.cursor()

http = urllib3.PoolManager()
url = "https://api-content.ingresso.com/v0/theaters"

try:
    response = http.request('GET', url)
    data = json.loads(response.data.decode('utf-8'))

    for i in data:
        cityName = None
        uf = None

        cityName = i['cityName']
        uf = i['uf']

        cur.execute("INSERT INTO city VALUES(%s, %s)", (cityName, uf))
        conn.commit()
    cur.close()
except IOError as io:
    print("ERROR!")

Ok, so far, so good, but when I execute the script, it returns me with a error:

psycopg2.DataError: invalid input syntax for integer: "Rio de Janeiro"

I'm thinking that this might be because I forgot to pass the id, since this table has (id, cityName, uf). I have to manually insert the id in the table?

I've created an index as an Id key. It's working just fine.

Upvotes: 3

Views: 11300

Answers (1)

random2137
random2137

Reputation: 138

So, after some coding I've finally succeed. Here is the code that I made. Though, I'm pretty sure there is a better way to do that, it is working, nevertheless.

PYTHON - WORKING

import json
import urllib3
import psycopg2

conn = psycopg2.connect(database="db", user="user", password="pass", host="host", port="port")
cur = conn.cursor()

http = urllib3.PoolManager()
url = "https://url"

try:
    response = http.request('GET', url)
    data = json.loads(response.data.decode('utf-8'))
    index = 0 #I'm using index as an id_key

    for i in data:
        var1 = None
        var2 = None

        var1 = i['var1']
        var2 = i['var2']

        cur.execute("""
            INSERT INTO table
            VALUES (%s, %s, %s); 
            """,
            (index, var1, var2))
        conn.commit()
        index += 1 
    cur.close()
except IOError as io:
    print("ERROR!")

Upvotes: 1

Related Questions