I want to fetch output values from database against my input values PostgresSQL

Question

I have setup a table in my database in which output value is set against its input and value tuple, and .for example my input is scoring with string "Calculation_score", and in database , there is value set against to this string which is "10".

**Table :**

**input**            **value**            **output value**

scoring                 Calculation_score         10
scoring                 Credit_score              20
scoring                 Monthly_score             30
scoring                 weekly_score              5

My code:

import psycopg2

value = "Calculation_score"
input = "scoring"
con = psycopg2.connect(database="myDB", user="postgres", password="admin", host="localhost", port="5432")
cur = con.cursor()


result = cur.execute("""SELECT input, input_type, value, output FROM value_rules WHERE input = :input
AND value = :value""", {"input": input, "value": value}).fetchone()

print(result)
return result['output']

I am querying database from python to get my output value, i dont understand whats wrong with this query, it gives me error( which is below ) ,even when i use "==" in place of ":" .

Traceback (most recent call last):
    """, {"input": input, "value": value}).fetchone()
psycopg2.ProgrammingError: syntax error at or near ":"
LINE 4: WHERE input = :input
                      ^

Gideon Maina · Accepted Answer · 2019-11-30 17:33:07Z

1

You can be more specific on the setting the query statement and add the expected format character s using parameterized query as follows

>>> # example
>>> q = 'SELECT id, name FROM customer WHERE id = %(cid)s;'
>>> cursor.execute(q, {'cid': 1})
>>> cursor.query
    b'SELECT id, name FROM customer WHERE id = 1;'

>>> # so in your case you can do 
>>> q = 'SELECT input, input_type, value, output FROM value_rules WHERE input = %(input)s AND value = %(value)s' 
>>> cursor.execute(q, {'input': 'Calculation_score', 'value': 'scoring' })

Check more on the docs here

edited Nov 30, 2019 at 17:33

answered Nov 30, 2019 at 17:15

Gideon Maina

87011 silver badges26 bronze badges

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

7 Comments

xpertdev Over a year ago

``` rows = cur.fetchone() print(rows) ``` i tried your snippet, but in output it only returns None

Gideon Maina Over a year ago

does the cursor.query return the SQL statement you expect? Post a sample print(cursor.query)

Gideon Maina Over a year ago

@xpertdev I have edited to put your expected values for the params.

xpertdev Over a year ago

i tried your snippet. ```cur.execute(q, {'input': input, 'value': value }) rows = cur.fetchone() print(rows)`` its output is none .

xpertdev Over a year ago

Checkign hold on

|

zip · Accepted Answer · 2019-11-30 17:28:14Z

1

Try this

import psycopg2

value = "Calculation_score"
input = "monthly_inquiries"
con = psycopg2.connect(database="myDB", user="postgres", password="admin", host="localhost", port="5432")
cur = con.cursor()

 cur.execute("SELECT input, input_type, value, output FROM value_rules WHERE input = %s
AND value = %s;", (input, value)) 

result = cur.fetchone()

    print(result)

Indeed, execute doesnt fetch the rows, We have to do the fetch with the cursor (cur)

edited Nov 30, 2019 at 17:28

answered Nov 30, 2019 at 17:05

zip

4,0612 gold badges13 silver badges22 bronze badges

1 Comment

xpertdev Over a year ago

thankyou for your response, it throws AttributeError: 'NoneType' object has no attribute 'fetchone' error

Collectives™ on Stack Overflow

I want to fetch output values from database against my input values PostgresSQL

2 Answers 2

7 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related