SQL get data as json or dict

Question

I have a database named products in sql and i wish to get all the rows as a dictionary or json. I've seen an example here but how do i pass username, password and host?

This is the example:

import json
import psycopg2


def db(database_name='products'):
    return psycopg2.connect(database=database_name)

def query_db(query, args=(), one=False):
    cur = db().cursor()
    cur.execute(query, args)
    r = [dict((cur.description[i][0], value) for i, value in enumerate(row)) for row in cur.fetchall()]
    cur.connection.close()
    return (r[0] if r else None) if one else r

my_query = query_db("SELECT * FROM main_prod WHERE id = 1")
print(my_query)

json_output = json.dumps(my_query)
print(json_output)

When i use it like this i'm getting this error:

File "/home/alex/Documents/Proiecte/Python/bapp/venv/lib/python3.5/site-packages/psycopg2/__init__.py", line 130, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: fe_sendauth: no password supplied

When i'm doing like this

import json
import psycopg2


def db(database_name='products', password='...', host='123.123.123.13', user='alex'):
    return psycopg2.connect(database=database_name, password=password, host=host, user=user)

def query_db(query, args=(), one=False):
    cur = db().cursor()
    cur.execute(query, args)
    r = [dict((cur.description[i][0], value) for i, value in enumerate(row)) for row in cur.fetchall()]
    cur.connection.close()
    return (r[0] if r else None) if one else r

my_query = query_db("SELECT * FROM main_prod WHERE id = 1")
print(my_query)

json_output = json.dumps(my_query)
print(json_output)

It won't print anything, it just remains like in sleep.

How can i do it?

This has absolutely nothing to do with JSON or getting results as dicts.... — bruno desthuilliers
– bruno desthuilliers, Commented Nov 8, 2017 at 9:03
Only with connecting to postgresql - which is not a SQL problem either. — bruno desthuilliers
– bruno desthuilliers, Commented Nov 8, 2017 at 9:05
And possibly not anything to do with connecting to the database as that problem seems corrected by supplying values for user and password. Can you connect to the database using psql or other client? — mhawke
– mhawke, Commented Nov 8, 2017 at 9:07

py_dude · Accepted Answer · 2017-11-08 09:23:34Z

Try this:

import psycopg2
import json

def main():
    conn_string = "database_name='products', password='...', host='123.123.123.13', user='alex'"
    # print the connection string we will use to connect
    print "Connecting to database\n ->%s" % (conn_string)

    # get a connection, if a connect cannot be made an exception will be raised here
    conn = psycopg2.connect(conn_string)

    # conn.cursor will return a cursor object, you can use this cursor to perform queries
    cursor = conn.cursor()

    # execute our Query
    cursor.execute("SELECT * FROM main_prod WHERE id = 1")

    # retrieve the records from the database
    records = cursor.fetchall()
    objects = [
        {
            'id': row.id,
        } for row in records
    ] # there you tell what data you want to return

    json_output = json.dumps(objects)
    print(json_output)

if __name__ == "__main__":
    main()

Collectives™ on Stack Overflow

SQL get data as json or dict

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related