CODEWITHSUNDEEP
pythonsqldatabasepostgresql
xpertdev
xpertdev

Reputation: 1433

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

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
                      ^

Upvotes: 0

Views: 257

Answers (2)

Gideon Maina
Gideon Maina

Reputation: 870

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

Upvotes: 1

zip
zip

Reputation: 4061

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)

Upvotes: 1

Related Questions