fuyukai / asyncqlio Goto Github PK

Rows that haven't been loaded by Katagawa don't appear to work correctly with Session.merge. Also I don't completely understand what's going on or how to ORM.

import asyncio
import os

from asyncqlio.db import DatabaseInterface

from asyncqlio.orm.schema.column import Column
from asyncqlio.orm.schema.table import table_base
from asyncqlio.orm.schema.types import Integer, String

class Test(table_base()):
    id = Column(Integer(), primary_key=True)
    name = Column(String())

async def main():
    db = DatabaseInterface(os.environ['ASQL_DSN'])
    await db.connect()
    async with db.get_ddl_session() as sess:
        await sess.create_table(Test.__tablename__, *Test.iter_columns())
    async with db.get_session() as sess:
        await sess.select(Test).first()


loop = asyncio.get_event_loop()
row = loop.run_until_complete(main())

The preceding minimal reproduction results in asyncpg.exceptions.UndefinedColumnError: column test.name does not exist. Upon checking in psql, the table test was indeed not created.

When attempting to add a new row to a able with an auto-incrementing column, if you do not specify the value of the auto-incrementing column, the lib throws an error.

import asyncio

from asyncqlio.db import DatabaseInterface
from asyncqlio.orm.schema.column import Column
from asyncqlio.orm.schema.table import table_base
from asyncqlio.orm.schema.types import Integer, String

class Test(table_base()):
    id = Column(Integer(), primary_key=True, autoincrement=True)
    name = Column(String())

async def main():
    db = DatabaseInterface('postgresql://postgres:postgres@localhost/')
    await db.connect()
    async with db.get_ddl_session() as sess:
        await sess.create_table(Test.__tablename__, *Test.iter_columns())
    db.bind_tables(Test)
    async with db.get_session() as sess:
        await sess.add(Test(name='test'))


loop = asyncio.get_event_loop()
loop.run_until_complete(main())

The above code raises an asyncpg.exceptions.NotNullViolationError

Table.set(Table.column).where(...) should be a thing. So, uh, let's make it a thing? Why am I making this issue

DDL should be implemented:

• Table DDL

  • Table creation
  • Table deletion
  • Renaming tables
  • Temporary tables

• Column DDL

  • Add column to existing table
  • Change type of column
  • Add foreign key to column
  • Change attributes of columns (NULLABLE, DEFAULT)

• Indexes

• Sequences (Postgres only?)

• Constraints (UNIQUE etc)

Often times I'll rely on integrity checks at the database engine level (primarily for key already exists). Unfortunately these are bubbled up with warnings module and not diverted to logging where I think they more appropriately fit.

If this makes sense for the project I'd be happy to swap these out.

await Table.truncate()

(drops every row)

Trying to figure out how to best model an assoc (many-to-many) relationship in asyncqlio but haven't found any documentation on it.

When attempting to do an insert on a new record where an auto-incrementing primary key field is omitted, run_insert_query fails trying to test Column.autoincrement property.

Traceback (most recent call last):
  File "app/meet/meeting.py", line 39, in get_or_create_code
    await sess.add(m)
  File ".venv/lib/python3.6/site-packages/asyncqlio/orm/session.py", line 456, in add
    return await self.insert_now(row)
  File ".venv/lib/python3.6/site-packages/asyncqlio/orm/session.py", line 273, in insert_now
    result = await self.run_insert_query(q)
  File ".venv/lib/python3.6/site-packages/asyncqlio/orm/session.py", line 363, in run_insert_query
    if sum(1 for x in row.table.iter_columns() if x.autoincrement) == 1:
  File ".venv/lib/python3.6/site-packages/asyncqlio/orm/session.py", line 363, in <genexpr>
    if sum(1 for x in row.table.iter_columns() if x.autoincrement) == 1:
  File ".venv/lib/python3.6/site-packages/asyncqlio/orm/schema/column.py", line 188, in __getattr__
    item)) from None
AttributeError: Column object 'id' has no attribute 'autoincrement'

It should work with datetime.datetime.

On PostgreSQL, TIMESTAMP is both a date and time record. In MySQL, TIMESTAMP is simply the time and not date. For equivalent/compatibility, MySQL driver should translate TIMESTAMP to DATETIME, or a DATETIME column type added.

This seems possible, but unfortunately it doesn't seem easy to do DB-agnostically.

msgs = await sess.select.from_(Message)\
    .where(Message.author_id == who.id and Message.guild_id == ctx.guild.id)\
    .order_by(Message.created_at, sort_order='desc')\
    .limit(limit)\
    .all()

Only produces the following SQL query:

[05/24/2017 11:38:15 PM DEBUG] katagawa.backends.postgresql.asyncpg: Executing query SELECT "messages"."id" AS "t_messages_id", "messages"."guild_id" AS "t_messages_guild_id", "messages"."channel_id" AS "t_messages_channel_id", "messages"."author_id" AS "t_messages_author_id", "messages"."created_at" AS "t_messages_created_at", "messages"."content" AS "t_messages_content" FROM "messages"  WHERE "messages"."guild_id" = $1 ORDER BY "t_messages_created_at" DESC LIMIT 10 with params (295341979800436736,)

Everything else is fine, but you as you can see there is only a guild_id comparison, not an author_id comparison.

`from asyncqlio import DatabaseInterface
import asyncio

async def hello():
db = DatabaseInterface("mysql://root:123456@localhost:3306/user")
await db.connect()
async with db.get_transaction() as t:
cur = await t.cursor("SELECT 1;")
row = await cur.fetch_row()
print(row)

if name=='main':
loop = asyncio.get_event_loop()
loop.run_until_complete(hello())`

UPSERT is a pretty useful feature; if it isn't available directly in the database driver it can be implemented on top instead.

I'm not sure if I'm using the wrong classmethod, but I have a database schema with some legacy fields names. As a result I've used past ORMs to map what the field name should be to it's actual name in the schema.

The following script produces inconsistent results.

CREATE TABLE `camps` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `lat` decimal(12,8) NOT NULL DEFAULT '0.00000000',
  `lon` decimal(12,8) NOT NULL DEFAULT '0.00000000',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

INSERT INTO `camps` (`lat`, `lon`) VALUES (11.0012, 11.0012);

import asyncio

from asyncqlio import (
    table_base,
    Column,
    Integer,
    Numeric,
    DatabaseInterface,
)


Table = table_base()
db = DatabaseInterface('mysql+aiomysql://root:[email protected]/asqltest')


class Camp(Table, table_name='camps'):
    id = Column(Integer, primary_key=True, unique=True)
    tal = Column.with_name('lat', Numeric(12, 8), nullable=False)
    lon = Column(Numeric(12, 8), nullable=False)


async def test():
    await db.connect()

    print(Camp.tal.name)
    print(Camp.tal.get_ddl_sql())

    Camp.tal.name = 'lat'
    print(Camp.tal.name)
    print(Camp.tal.get_ddl_sql())

    async with db.get_session() as sess:
        u = await sess.select(Camp).where(Camp.id == 1).first()

    print(u)
    print(u.lon)
    print(u.tal)

loop = asyncio.get_event_loop()
loop.run_until_complete(test())

This results in the following output before a traceback at the select statement.

tal
tal NUMERIC(12,8) NOT NULL
lat
lat NUMERIC(12,8) NOT NULL
Traceback (most recent call last):
  File "test.marco.py", line 42, in <module>
    loop.run_until_complete(test())
  File "/usr/lib/python3.6/asyncio/base_events.py", line 467, in run_until_complete
    return future.result()
  File "test.marco.py", line 34, in test
    u = await sess.select(Camp).where(Camp.id == 1).first()
  File "/home/marco/Projects/asyncqlio/asyncqlio/orm/query.py", line 310, in first
    row = await gen.next()
  File "/home/marco/Projects/asyncqlio/asyncqlio/orm/query.py", line 122, in next
    return await self.__anext__()
  File "/home/marco/Projects/asyncqlio/asyncqlio/orm/query.py", line 116, in __anext__
    return self.query.map_columns(rows[0])
  File "/home/marco/Projects/asyncqlio/asyncqlio/orm/query.py", line 348, in map_columns
    row = self.table._internal_from_row(row_expando, existed=True)
  File "/home/marco/Projects/asyncqlio/asyncqlio/orm/schema/table.py", line 404, in _internal_from_row
    obb._init_row(**values)
  File "/home/marco/Projects/asyncqlio/asyncqlio/orm/schema/table.py", line 639, in _init_row
    raise TypeError("Unexpected row parameter: '{}'".format(name))
TypeError: Unexpected row parameter: 'lat'

Currently there's no way to get a mapping of all columns in a TableRow to their values, as far as I can tell. Having that would be neat. 👍

If an error happens, in an async with block the transaction is broken, and closing it can cause a reset query to be issued which is broken.

• Release instead of closing when there's no error in a session
• Close the connection if there's an error.

This can be reverted with better transaction logic, but this is the easier solution for now.

Before opening a pull request I wanted to check on if this is something in the maintainers vision for the library. Today the only precision value type support by MySQL backend is float which maps to orm.type.Real. Our database contains quite a few columns that are decimal(12,8). This size is outside what floats can normally sign - and given the choice between double and decimal we settled on the later.

I believe there is a case to be made for at a minimum mapping decimal and double in MySQL backend to orm.type.Real. However, given there is also a decimal type in PostgreSQL (numeric) which is slightly different than a type.Real I believe a numeric type might be worth inclusion with the following backend mappings:

Interested in opinions on the topic before I attempt any code updates.

The following

import asyncio

from asyncqlio.db import DatabaseInterface
from asyncqlio.orm.schema.column import Column
from asyncqlio.orm.schema.table import table_base
from asyncqlio.orm.schema.types import BigSerial, String

Table = table_base()


class Tag(Table):
    id = Column(BigSerial, primary_key=True)
    key = Column(String(2000))


db = DatabaseInterface("postgresql://postgres:[email protected]:5432/postgres")

db.bind_tables(Tag)


async def test():
    await db.connect()
    await Tag.create()

    async with db.get_session() as sess:
        q = sess.insert
        q.add_row(Tag(key="test"))
        await q.run()

    async with db.get_session() as sess:
        q = sess.select(Tag)
        q.add_condition(Tag.key.ilike("test"))
        r = await q.first()
    print(r)

loop = asyncio.get_event_loop()
loop.run_until_complete(test())

Results in:

Traceback (most recent call last):
  File "asyncqlio_ilike.py", line 37, in <module>
    loop.run_until_complete(test())
  File "/usr/lib/python3.6/asyncio/base_events.py", line 467, in run_until_complete
    return future.result()
  File "asyncqlio_ilike.py", line 32, in test
    q.add_condition(Tag.key.ilike("test"))
  File "/home/ben/.local/lib/python3.6/site-packages/asyncqlio/orm/schema/types.py", line 217, in ilike
    if self.column.table.bind.dialect.has_ilike:
  File "/home/ben/.local/lib/python3.6/site-packages/asyncqlio/orm/schema/table.py", line 353, in __getattr__
    raise AttributeError("'{}' object has no attribute {}".format(self.__name__, item))
AttributeError: 'Tag' object has no attribute bind

This is the module metadata

λ pip show asyncqlio                                                                                                                                      258ms
Name: asyncqlio
Version: 0.1.1.dev109
Summary: An asyncio ORM for Python 3.5+
Home-page: https://github.com/SunDwarf/asyncqlio
Author: Laura Dickinson
Author-email: [email protected]
License: MIT
Location: /home/ben/.local/lib/python3.6/site-packages
Requires: cached-property, asyncio-extras, click, tqdm

The History API needs to be implemented which will allow things such as arrays and the likes a lot easier.

Right now a lot of exceptions are bubbled up. They should be changed into asyncqlio exceptions across all drivers.