Accessing PostGIS geometry with psycopg2

Question

I've a PostGIS database loaded with several tables filled with shapefiles. I'm using psycopg2 to access everything via python. I'm able to access the table names with the below query:

"""SELECT table_name 
              FROM information_schema.tables 
              WHERE table_schema = 'public'"""

which returns

('Sea Areas(PolyGrid)',)
('geography_columns',)
('geometry_columns',)
('spatial_ref_sys',)
('12NM(LineGrid)',)
('TimeZone(LineGrid)',)
('12NM_PolyGrid',)

But when i try and run a simple query like:

"""SELECT geom FROM 12NM_PolyGrid"""

it throws:

Error while connecting to PostgreSQL syntax error at or near "12"

I'm new to SQL so it could be simple error. Ultimately I would like to do a bounding box query like in this link. The results would then be placed in a geodataframe.

Answer 1

PostGreSQL (and thus PostGIS) don't like table (or column) names to start with digits or to have mixed case. So you need to add " around any name that does this. So, in your case the select statement is:

SELECT geom FROM "12NM_PolyGrid" 

which in a python string should probably be:

'SELECT geom FROM "12NM_PolyGrid"'