why the select query through python throwing syntax error?

Question

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?

I think <schema_name> should be replaced with real table/schema name. Or you replaced here real name with it to hide secure info? If your table name is Employee_TBL then just do select * from Employee_TBL; — Arty
– Arty, Commented Sep 20, 2020 at 8:42

nimajv · Accepted Answer · 2020-09-20 08:43:59Z

1

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

answered Sep 20, 2020 at 8:43

nimajv

4533 silver badges12 bronze badges

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

3 Comments

user3521180 Over a year ago

I hid the schema for the security purpose. If I just put table_name without the schema name, I get the same error. cur.execute("select * from 'Employee_TBL'"). I tried with and without a single quote.

user3521180 Over a year ago

When I only add the tabel name, I get "psycopg2.errors.UndefinedTable: relation "employee_tbl" does not exist LINE 1: select * from Employee_TBL"

GProst Over a year ago

So what’s your schema name if it’s not public?

Maurice Meyer · Accepted Answer · 2020-09-20 13:31:27Z

1

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

answered Sep 20, 2020 at 13:31

Maurice Meyer

18.2k4 gold badges35 silver badges54 bronze badges

Comments

GProst · Accepted Answer · 2020-09-20 11:22:46Z

0

Try removing quotes around ‘emloyee_tbl’

answered Sep 20, 2020 at 11:22

GProst

10.3k3 gold badges28 silver badges51 bronze badges

Comments

Adrian Klaver · Accepted Answer · 2020-09-20 16:30:19Z

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)

Collectives™ on Stack Overflow

why the select query through python throwing syntax error?

4 Answers 4

3 Comments

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Related