geomancer
geomancer copied to clipboard
Published
20 hours ago •
thinkingmachines
thinkingmachines
[WIP] Add initial support for postgresql
2019-07-31 15:11:21.918 | ERROR | __main__:<module>:10 - An error has been caught in function '<module>', process 'MainProcess' (27318), thread 'MainThread' (140735861891904):
Traceback (most recent call last):
File "/Users/bianca/tm/repos/geomancer/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context
cursor, statement, parameters, context
│ │ │ └ <sqlalchemy.dialects.postgresql.psycopg2.PGExecutionContext_psycopg2 object at 0x1121721d0>
│ │ └ {'fclass_1': 'supermarket', 'ST_Distance_1': 10000, 'row_number_1': 1}
│ └ 'WITH pois AS \n(SELECT gis_osm_pois_free_1.osm_id AS osm_id, gis_osm_pois_free_1."WKT" AS "WKT" \nFROM gis_osm_pois_free_1 \nWH...
└ <cursor object at 0x111d90dd8; closed: -1>
File "/Users/bianca/tm/repos/geomancer/venv/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute
cursor.execute(statement, parameters)
│ │ │ └ {'fclass_1': 'supermarket', 'ST_Distance_1': 10000, 'row_number_1': 1}
│ │ └ 'WITH pois AS \n(SELECT gis_osm_pois_free_1.osm_id AS osm_id, gis_osm_pois_free_1."WKT" AS "WKT" \nFROM gis_osm_pois_free_1 \nWH...
│ └ <built-in method execute of psycopg2.extensions.cursor object at 0x111d90dd8>
└ <cursor object at 0x111d90dd8; closed: -1>
psycopg2.errors.SyntaxError: subquery in FROM must have an alias
LINE 11: FROM (SELECT pairs.__index_level_0__ AS __index_level_0__, p...
^
HINT: For example, FROM (SELECT ...) [AS] foo.
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
> File "postgres_sample.py", line 10, in <module>
).cast(df, dburl="postgresql://bianca:postgres@localhost/geomancer")
└ WKT code
0 POINT (121.0042183 14.6749145) 2082
1 POINT (121.0052375 14.6767411) 2110
2 PO...
File "/Users/bianca/tm/repos/geomancer/geomancer/spells/base.py", line 204, in cast
results = engine.execute(query)
│ │ └ <sqlalchemy.sql.selectable.Select at 0x112172278; Select object>
│ └ <bound method Engine.execute of Engine(postgresql://bianca:***@localhost/geomancer)>
└ Engine(postgresql://bianca:***@localhost/geomancer)
File "/Users/bianca/tm/repos/geomancer/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 2166, in execute
return connection.execute(statement, *multiparams, **params)
│ │ │ │ └ {}
│ │ │ └ ()
│ │ └ <sqlalchemy.sql.selectable.Select at 0x112172278; Select object>
│ └ <bound method Connection.execute of <sqlalchemy.engine.base.Connection object at 0x112172080>>
└ <sqlalchemy.engine.base.Connection object at 0x112172080>
File "/Users/bianca/tm/repos/geomancer/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 988, in execute
return meth(self, multiparams, params)
│ │ │ └ {}
│ │ └ ()
│ └ <sqlalchemy.engine.base.Connection object at 0x112172080>
└ <bound method ClauseElement._execute_on_connection of <sqlalchemy.sql.selectable.Select at 0x112172278; Select object>>
File "/Users/bianca/tm/repos/geomancer/venv/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 287, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
│ │ │ │ └ {}
│ │ │ └ ()
│ │ └ <sqlalchemy.sql.selectable.Select at 0x112172278; Select object>
│ └ <bound method Connection._execute_clauseelement of <sqlalchemy.engine.base.Connection object at 0x112172080>>
└ <sqlalchemy.engine.base.Connection object at 0x112172080>
File "/Users/bianca/tm/repos/geomancer/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1107, in _execute_clauseelement
distilled_params,
└ []
File "/Users/bianca/tm/repos/geomancer/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context
e, statement, parameters, cursor, context
│ │ │ └ <sqlalchemy.dialects.postgresql.psycopg2.PGExecutionContext_psycopg2 object at 0x1121721d0>
│ │ └ <cursor object at 0x111d90dd8; closed: -1>
│ └ {'fclass_1': 'supermarket', 'ST_Distance_1': 10000, 'row_number_1': 1}
└ 'WITH pois AS \n(SELECT gis_osm_pois_free_1.osm_id AS osm_id, gis_osm_pois_free_1."WKT" AS "WKT" \nFROM gis_osm_pois_free_1 \nWH...
File "/Users/bianca/tm/repos/geomancer/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1466, in _handle_dbapi_exception
util.raise_from_cause(sqlalchemy_exception, exc_info)
│ │ │ └ (<class 'psycopg2.errors.SyntaxError'>, SyntaxError('subquery in FROM must have an alias\nLINE 11: FROM (SELECT pairs.__index_le...
│ │ └ ProgrammingError('(psycopg2.errors.SyntaxError) subquery in FROM must have an alias\nLINE 11: FROM (SELECT pairs.__index_level_0...
│ └ <function raise_from_cause at 0x110d7a158>
└ <module 'sqlalchemy.util' from '/Users/bianca/tm/repos/geomancer/venv/lib/python3.7/site-packages/sqlalchemy/util/__init__.py'>
File "/Users/bianca/tm/repos/geomancer/venv/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 383, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
│ │ │ │ │ │ └ SyntaxError('subquery in FROM must have an alias\nLINE 11: FROM (SELECT pairs.__index_level_0__ AS __index_level_0__, p...\n ...
│ │ │ │ │ └ SyntaxError('subquery in FROM must have an alias\nLINE 11: FROM (SELECT pairs.__index_level_0__ AS __index_level_0__, p...\n ...
│ │ │ │ └ <traceback object at 0x11217dc88>
│ │ │ └ ProgrammingError('(psycopg2.errors.SyntaxError) subquery in FROM must have an alias\nLINE 11: FROM (SELECT pairs.__index_level_0...
│ │ └ ProgrammingError('(psycopg2.errors.SyntaxError) subquery in FROM must have an alias\nLINE 11: FROM (SELECT pairs.__index_level_0...
│ └ <class 'type'>
└ <function reraise at 0x110d81b70>
File "/Users/bianca/tm/repos/geomancer/venv/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 128, in reraise
raise value.with_traceback(tb)
│ │ └ <traceback object at 0x11217dc88>
│ └ <built-in method with_traceback of ProgrammingError object at 0x1120bae88>
└ ProgrammingError('(psycopg2.errors.SyntaxError) subquery in FROM must have an alias\nLINE 11: FROM (SELECT pairs.__index_level_0...
File "/Users/bianca/tm/repos/geomancer/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context
cursor, statement, parameters, context
│ │ │ └ <sqlalchemy.dialects.postgresql.psycopg2.PGExecutionContext_psycopg2 object at 0x1121721d0>
│ │ └ {'fclass_1': 'supermarket', 'ST_Distance_1': 10000, 'row_number_1': 1}
│ └ 'WITH pois AS \n(SELECT gis_osm_pois_free_1.osm_id AS osm_id, gis_osm_pois_free_1."WKT" AS "WKT" \nFROM gis_osm_pois_free_1 \nWH...
└ <cursor object at 0x111d90dd8; closed: -1>
File "/Users/bianca/tm/repos/geomancer/venv/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute
cursor.execute(statement, parameters)
│ │ │ └ {'fclass_1': 'supermarket', 'ST_Distance_1': 10000, 'row_number_1': 1}
│ │ └ 'WITH pois AS \n(SELECT gis_osm_pois_free_1.osm_id AS osm_id, gis_osm_pois_free_1."WKT" AS "WKT" \nFROM gis_osm_pois_free_1 \nWH...
│ └ <built-in method execute of psycopg2.extensions.cursor object at 0x111d90dd8>
└ <cursor object at 0x111d90dd8; closed: -1>
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) subquery in FROM must have an alias
LINE 11: FROM (SELECT pairs.__index_level_0__ AS __index_level_0__, p...
^
HINT: For example, FROM (SELECT ...) [AS] foo.
[SQL: WITH pois AS
(SELECT gis_osm_pois_free_1.osm_id AS osm_id, gis_osm_pois_free_1."WKT" AS "WKT"
FROM gis_osm_pois_free_1
WHERE gis_osm_pois_free_1.fclass = %(fclass_1)s),
pairs AS
(SELECT "457ab2b2b2114eb797a20fc5420a6d64".__index_level_0__ AS __index_level_0__, "457ab2b2b2114eb797a20fc5420a6d64"."WKT" AS "WKT", "457ab2b2b2114eb797a20fc5420a6d64".code AS code, ST_Distance(ST_GeogFromText("457ab2b2b2114eb797a20fc5420a6d64"."WKT"), ST_GeogFromText(pois."WKT")) AS dist_supermarket
FROM "457ab2b2b2114eb797a20fc5420a6d64", pois
WHERE ST_Distance(ST_GeogFromText("457ab2b2b2114eb797a20fc5420a6d64"."WKT"), ST_GeogFromText(pois."WKT")) < %(ST_Distance_1)s)
SELECT "WKT", code, dist_supermarket
FROM (SELECT __index_level_0__, "WKT", code, dist_supermarket
FROM (SELECT pairs.__index_level_0__ AS __index_level_0__, pairs."WKT" AS "WKT", pairs.code AS code, pairs.dist_supermarket AS dist_supermarket, row_number() OVER (PARTITION BY pairs.__index_level_0__ ORDER BY pairs.dist_supermarket ASC) AS row_number
FROM pairs)
WHERE row_number = %(row_number_1)s)]
[parameters: {'fclass_1': 'supermarket', 'ST_Distance_1': 10000, 'row_number_1': 1}]
(Background on this error at: http://sqlalche.me/e/f405)
Traceback (most recent call last):
File "/Users/bianca/tm/repos/geomancer/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context
cursor, statement, parameters, context
File "/Users/bianca/tm/repos/geomancer/venv/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute
cursor.execute(statement, parameters)
psycopg2.errors.SyntaxError: subquery in FROM must have an alias
LINE 11: FROM (SELECT pairs.__index_level_0__ AS __index_level_0__, p...
^
HINT: For example, FROM (SELECT ...) [AS] foo.
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "postgres_sample.py", line 10, in <module>
).cast(df, dburl="postgresql://bianca:postgres@localhost/geomancer")
File "/Users/bianca/tm/repos/geomancer/venv/lib/python3.7/site-packages/loguru/_logger.py", line 931, in catch_wrapper
return function(*args, **kwargs)
File "/Users/bianca/tm/repos/geomancer/geomancer/spells/base.py", line 204, in cast
results = engine.execute(query)
File "/Users/bianca/tm/repos/geomancer/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 2166, in execute
return connection.execute(statement, *multiparams, **params)
File "/Users/bianca/tm/repos/geomancer/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 988, in execute
return meth(self, multiparams, params)
File "/Users/bianca/tm/repos/geomancer/venv/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 287, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "/Users/bianca/tm/repos/geomancer/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1107, in _execute_clauseelement
distilled_params,
File "/Users/bianca/tm/repos/geomancer/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context
e, statement, parameters, cursor, context
File "/Users/bianca/tm/repos/geomancer/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1466, in _handle_dbapi_exception
util.raise_from_cause(sqlalchemy_exception, exc_info)
File "/Users/bianca/tm/repos/geomancer/venv/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 383, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File "/Users/bianca/tm/repos/geomancer/venv/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 128, in reraise
raise value.with_traceback(tb)
File "/Users/bianca/tm/repos/geomancer/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1244, in _execute_context
cursor, statement, parameters, context
File "/Users/bianca/tm/repos/geomancer/venv/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) subquery in FROM must have an alias
LINE 11: FROM (SELECT pairs.__index_level_0__ AS __index_level_0__, p...
^
HINT: For example, FROM (SELECT ...) [AS] foo.
[SQL: WITH pois AS
(SELECT gis_osm_pois_free_1.osm_id AS osm_id, gis_osm_pois_free_1."WKT" AS "WKT"
FROM gis_osm_pois_free_1
WHERE gis_osm_pois_free_1.fclass = %(fclass_1)s),
pairs AS
(SELECT "457ab2b2b2114eb797a20fc5420a6d64".__index_level_0__ AS __index_level_0__, "457ab2b2b2114eb797a20fc5420a6d64"."WKT" AS "WKT", "457ab2b2b2114eb797a20fc5420a6d64".code AS code, ST_Distance(ST_GeogFromText("457ab2b2b2114eb797a20fc5420a6d64"."WKT"), ST_GeogFromText(pois."WKT")) AS dist_supermarket
FROM "457ab2b2b2114eb797a20fc5420a6d64", pois
WHERE ST_Distance(ST_GeogFromText("457ab2b2b2114eb797a20fc5420a6d64"."WKT"), ST_GeogFromText(pois."WKT")) < %(ST_Distance_1)s)
SELECT "WKT", code, dist_supermarket
FROM (SELECT __index_level_0__, "WKT", code, dist_supermarket
FROM (SELECT pairs.__index_level_0__ AS __index_level_0__, pairs."WKT" AS "WKT", pairs.code AS code, pairs.dist_supermarket AS dist_supermarket, row_number() OVER (PARTITION BY pairs.__index_level_0__ ORDER BY pairs.dist_supermarket ASC) AS row_number
FROM pairs)
WHERE row_number = %(row_number_1)s)]
[parameters: {'fclass_1': 'supermarket', 'ST_Distance_1': 10000, 'row_number_1': 1}]
(Background on this error at: http://sqlalche.me/e/f405)
@binkymilk Now I think the solution for this can be found here: https://stackoverflow.com/questions/14767209/subquery-in-from-must-have-an-alias
At the end of the day, this seems like an unlucky syntax. Hopefully we can resolve this the soonest