payload: {"action":"created","issue":{"url":"https://api.github.com/repos/encode/databases/issues/128","repository_url":"https://api.github.com/repos/encode/databases","labels_url":"https://api.github.com/repos/encode/databases/issues/128/labels{/name}","comments_url":"https://api.github.com/repos/encode/databases/issues/128/comments","events_url":"https://api.github.com/repos/encode/databases/issues/128/events","html_url":"https://github.com/encode/databases/issues/128","id":470323207,"node_id":"MDU6SXNzdWU0NzAzMjMyMDc=","number":128,"title":"Simple query that works with sqlalchemy fails with databases","user":{"login":"trvrmcs","id":45498500,"node_id":"MDQ6VXNlcjQ1NDk4NTAw","avatar_url":"https://avatars2.githubusercontent.com/u/45498500?v=4","gravatar_id":"","url":"https://api.github.com/users/trvrmcs","html_url":"https://github.com/trvrmcs","followers_url":"https://api.github.com/users/trvrmcs/followers","following_url":"https://api.github.com/users/trvrmcs/following{/other_user}","gists_url":"https://api.github.com/users/trvrmcs/gists{/gist_id}","starred_url":"https://api.github.com/users/trvrmcs/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/trvrmcs/subscriptions","organizations_url":"https://api.github.com/users/trvrmcs/orgs","repos_url":"https://api.github.com/users/trvrmcs/repos","events_url":"https://api.github.com/users/trvrmcs/events{/privacy}","received_events_url":"https://api.github.com/users/trvrmcs/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":1,"created_at":"2019-07-19T13:25:33Z","updated_at":"2019-07-25T14:34:40Z","closed_at":null,"author_association":"NONE","body":"A simple `COUNT(*)` works using `sqlalchemy.Engine.execute()`, but fails using `databases.Database.fetch_all()`, raising an `IndeterminateDatatypeError` exception.\r\n\r\n```python\r\nimport asyncio\r\nimport databases\r\nimport sqlalchemy\r\nfrom sqlalchemy import Column, MetaData, Text, Table, select, func\r\n\r\n\r\nurl = \"postgresql://test:password@localhost/example\"\r\nengine = sqlalchemy.create_engine(url)\r\n\r\n\r\nmetadata = MetaData()\r\nPeople = Table(\"people\", metadata, Column(\"name\", Text()))\r\nQUERY = select([func.count(\"*\")]).select_from(People)\r\n\r\n\r\ndef setup():\r\n    engine.execute(\"CREATE TABLE IF NOT EXISTS people(name text)\")\r\n\r\n\r\ndef test_sync():\r\n    \"this works\"\r\n    engine.execute(QUERY).fetchall()\r\n\r\n\r\nasync def test_async():\r\n    \"this fails with 'asyncpg.exceptions.IndeterminateDatatypeError: could not determine data type of parameter $1'\"\r\n    database = databases.Database(url)\r\n    await database.connect()\r\n    await database.fetch_all(QUERY)\r\n    await database.disconnect()\r\n\r\n\r\nif __name__ == \"__main__\":\r\n    setup()\r\n    test_sync()\r\n\r\n    asyncio.get_event_loop().run_until_complete(test_async())\r\n\r\n```\r\n\r\n"},"comment":{"url":"https://api.github.com/repos/encode/databases/issues/comments/515069047","html_url":"https://github.com/encode/databases/issues/128#issuecomment-515069047","issue_url":"https://api.github.com/repos/encode/databases/issues/128","id":515069047,"node_id":"MDEyOklzc3VlQ29tbWVudDUxNTA2OTA0Nw==","user":{"login":"gvbgduh","id":8695596,"node_id":"MDQ6VXNlcjg2OTU1OTY=","avatar_url":"https://avatars1.githubusercontent.com/u/8695596?v=4","gravatar_id":"","url":"https://api.github.com/users/gvbgduh","html_url":"https://github.com/gvbgduh","followers_url":"https://api.github.com/users/gvbgduh/followers","following_url":"https://api.github.com/users/gvbgduh/following{/other_user}","gists_url":"https://api.github.com/users/gvbgduh/gists{/gist_id}","starred_url":"https://api.github.com/users/gvbgduh/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/gvbgduh/subscriptions","organizations_url":"https://api.github.com/users/gvbgduh/orgs","repos_url":"https://api.github.com/users/gvbgduh/repos","events_url":"https://api.github.com/users/gvbgduh/events{/privacy}","received_events_url":"https://api.github.com/users/gvbgduh/received_events","type":"User","site_admin":false},"created_at":"2019-07-25T14:34:40Z","updated_at":"2019-07-25T14:34:40Z","author_association":"CONTRIBUTOR","body":"It looks like it's due to a subtle difference in the underlying driver. \r\n`await database.fetch_all(QUERY)` will render the query and perform the command as\r\n```raw_connection.fetch('select count($1) from notes;', '*')``` that will raise an exception at the `asyncpg` level \r\n```\r\nasyncpg.exceptions.IndeterminateDatatypeError: could not determine data type of parameter $1\r\n```\r\nand to resolve the confusion asyncpg expects it as\r\n```\r\nraw_connection.fetch('select count($1::text) from notes;', '*')\r\n```.\r\n\r\nAnother way to resolve it would be to define the query as\r\n`str(select([func.count()]).select_from(notes))`\r\nthat'll be rendered as\r\n```\r\n'SELECT count(*) AS count_1 \\nFROM notes'\r\n```\r\nthat doesn't confuse asyncpg.\r\n\r\nFor `engine.execute(QUERY).fetchall()` the underlying driver is `psycopg2`, that receives the query as\r\n```\r\nengine.execute('select count(%s) from notes;', '*').fetchall()\r\n``` that it can execute."}}
other: {"actor":{"display_login":"gvbgduh"}}