api data directly insert into PostgreSQL database with python

Question

main.py

import json, urllib.request, requests
import psycopg2
from psycopg2.extras import execute_values


# Retrieve Json Data from Within API

url = "https://datahead.herokuapp.com/api/employeers/"
response = urllib.request.urlopen(url)
data = json.loads(response.read())


# ***** connect to the db *******
try:
    conn = psycopg2.connect("dbname='datahead' user='postgres' host='localhost' password='datahead'")
except:
    print("I am unable to connect to the database")

# cursor
cur = conn.cursor()

fields = [
    'id', #integer
    'name', #varchar
    'log_date', #date
    'log_time', #timestamp
    'login', #timestamp
    'logout' #timestamp
]

for item in data:
    my_data = [item[field] for field in fields]
    insert_query = "INSERT INTO employee VALUES (%s, %s, %s, %s, %s, %s)"
    cur.execute(insert_query, tuple(my_data))
    conn.commit()

# close the cursor
cur.close()

# close the connection
conn.close()

please visit my api data format https://datahead.herokuapp.com/api/employeers/

Error Through

Will it change here timestamp type, if yes so which data type i've to use? Line 56 what happened?

Laurenz Albe · Accepted Answer · 2019-03-04 08:15:09Z

1

I think login_time should be of type time without time zone in PostgresSQL.

But you would probably be better off using timestamp with time zone to represent date and time together.

answered Mar 4, 2019 at 8:15

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Prosenjit Over a year ago

in my login, logout and log_time i used "timestamp without time zone" and in this case only i want to represent my time that's why without timezone used and doesn't work.

Prosenjit Over a year ago

i.sstatic.net/KUC7z.png please visit this image and here if i use with timezone then i can't calculation may be.

Laurenz Albe Over a year ago

There is too little English in your last comment for me to understand. Also, text is better than images.

Prosenjit Over a year ago

Yeah i've solved that. have to use there time instead of timestamp. Thanks @Laurenz Albe

Collectives™ on Stack Overflow

api data directly insert into PostgreSQL database with python

main.py

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

main.py

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related