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, Nov 08 '17 at 09:03
Only with connecting to postgresql - which is not a SQL problem either. — bruno desthuilliers, Nov 08 '17 at 09: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, Nov 08 '17 at 09:07
What about tracing your code in the step debugger or adding prints in `query_db` to find out where your program get stuck ? — bruno desthuilliers, Nov 08 '17 at 09:08
yes i did, i managed to connect to it via mysql.connector but i couldn't return a dict — alex, Nov 08 '17 at 09:11
is it other way to connect to my database and return a dict with the rows? — alex, Nov 08 '17 at 09:13
`mysql.connector` is for MySQL. `psycopg2` is for Postgres. Which database are you using? If Postgres, the default port is 5432 so make sure that your firewall has that port open. Did you try using the `psql` client? — mhawke, Nov 08 '17 at 10:58

score 1 · Answer 1 · answered Nov 08 '17 at 09:23

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()

SQL get data as json or dict

1 Answers1