CODEWITHSUNDEEP
pythonpostgresqlpsycopg2
user3521180
user3521180

Reputation: 1130

why the select query through python throwing syntax error?

I am trying to connect to a Postgres DB and execute a simple select query. But I am getting errors. Below is the sample program

import psycopg2 as ps

param_dic = ps.connect(
    host="localhost",
    database="Test_DB",
    user="username",
    password="password"
)

cur = param_dic.cursor()
cur.execute("select * from  <schema_name>.'employee_tbl'") # I am 
rows = cur.fetchall()

for r in rows:
    print(r)

I get below error:

psycopg2.errors.SyntaxError: syntax error at or near "'tbl_name'"
LINE 1: select * from  <schema_name>.'tbl_name'

If I use

cur.execute("select * from <schema_name>.'Employee_TBL'")

Still, I get the same error.

Note: when I check my pgAdmin, I see below as the table name

<schema_name>.Employee_TBL

So, what is the right way to execute SQL queries on a Postgres table?

Upvotes: 0

Views: 983

Answers (4)

Adrian Klaver
Adrian Klaver

Reputation: 19724

The overriding issue is that you created a table name with quoted mixed case. This means that from then on you need to quote the name to be able to use it:

create table "Mixed_Case" (id int, fld_1 varchar);
select * from mixed_case;
ERROR:  relation "mixed_case" does not exist
LINE 1: select * from mixed_case;

select * from "Mixed_Case";
 id | fld_1 
----+-------
(0 rows)

--NOTE the double quotes.

For more information see 4.1.1. Identifiers and Key Words

For the psycopg2 case:

import psycopg2
con = psycopg2.connect("dbname='test' host='localhost' user='aklaver'")
cur = con.cursor()
# Single quoted
cur.execute("select * from public.'Mixed_Case'")
SyntaxError: syntax error at or near "'Mixed_Case'"
LINE 1: select * from public.'Mixed_Case'
# Identifiers need double quotes

# Changing to double quotes
cur.execute('select * from public."Mixed_Case"')
# No error.
# You can also use Postgres functions
cur.execute("select * from quote_ident('public.Mixed_Case')")

For more on Postgres functions and methods see string functions

Also in psycopg2 there is the sql module that allows you to work with identifiers:

from psycopg2 import sql
sql_str = sql.SQL("select * from public.{}").format(sql.Identifier('Mixed_Case'))
print(sql_str.as_string(con))                                                                                                                                             
select * from public."Mixed_Case"
cur.execute(sql_str)

Upvotes: 0

Maurice Meyer
Maurice Meyer

Reputation: 18136

Maybe it's just a typo, use double quotes for the table if you need to.
Single quotes indicate a string literal.

import psycopg2
conn = psycopg2.connect(...)
cur = conn.cursor()
    
cur.execute('SELECT * FROM public.servers')
print(len(cur.fetchall()))

cur.execute('SELECT * FROM PUBLIC."servers"')
print(len(cur.fetchall()))

Out:

6
6

Upvotes: 1

GProst
GProst

Reputation: 10237

Try removing quotes around ‘emloyee_tbl’

Upvotes: 0

nimajv
nimajv

Reputation: 433

replace <schema_name>.'Employee_TBL'" with true value : dbo.Employee_TBL or another schema name in your database

Upvotes: 1

Related Questions