search

thinkingmachines / geomancer

Automated feature engineering for geospatial data
MIT License
214 stars 16 forks source link

[WIP] Add initial support for postgresql #79

Open binkymilk opened 4 years ago

ljvmiranda921 commented 4 years ago 
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)
ljvmiranda921 commented 4 years ago

@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