search

python-gino / gino

GINO Is Not ORM - a Python asyncio ORM on SQLAlchemy core.
https://python-gino.org/
Other
2.68k stars 150 forks source link

mysql support #685

Closed wwwjfy closed 4 years ago

wwwjfy commented 4 years ago

A workable version based on master before baked queries.

It's a "painful" journey patching the existing system.

The main pain points:

The code is structured catering for PostgreSQL. It'll be easier if we have a generic implementation in 2.0 integrating with other databases like SQLite

Refs #381

fantix commented 4 years ago

wow!

wwwjfy commented 4 years ago

"fixed" codacy by excluding mysql_tests/**

wwwjfy commented 4 years ago

The warning seems related to https://github.com/PyCQA/pylint/issues/2315 It's defined in its ancestor class, and line 461 is a comment 🤷🏻‍♀️

xnuinside commented 4 years ago

wow, I just come with questions about plans to support different databases and see this :) very cool

woostundy commented 4 years ago

Cool ! Any fresh developments ?

wwwjfy commented 4 years ago

Sync'ed with latest master and added bakery.

Note: aiomysql doesn't support PREPARE at the moment.

oleeks commented 4 years ago

Hehe，Any fresh developments ?

wwwjfy commented 4 years ago

Hey sorry, I've been a bit busy at work lately. I'll probably revisit and polish it next week.

Meanwhile, I'm preparing a diff to support prepared statements. https://github.com/aio-libs/aiomysql/pull/508 that repo seems quiet for a while. I'm also considering alternatives.

oleeks commented 4 years ago

Hey sorry, I've been a bit busy at work lately. I'll probably revisit and polish it next week.

Meanwhile, I'm preparing a diff to support prepared statements. aio-libs/aiomysql#508 that repo seems quiet for a while. I'm also considering alternatives.

Consider adding support for non-auto-increment primary keys？

wwwjfy commented 4 years ago

Consider adding support for non-auto-increment primary keys？

@guifa2015 sorry I don't get your question. Isn't that already supported?

oleeks commented 4 years ago

maybe I am using the wrong way？

from fastapi import APIRouter

import shortuuid
from sqlalchemy import Column, String

router = APIRouter()

class Posts(db.Model):
    __tablename__ = "posts"
    id = Column(String(100), primary_key=True, default=shortuuid.uuid)
    username = Column(String(50))

@router.post('/add')
async def add_posts():
    p = await Posts.create(username='fantix')
  File "/home/lh/miniconda3/envs/mysql_dome/lib/python3.8/site-packages/gino/crud.py", line 445, in _create_without_instance
    return await cls(**values)._create(bind=bind, timeout=timeout)
  File "/home/lh/miniconda3/envs/mysql_dome/lib/python3.8/site-packages/gino/crud.py", line 434, in __init__
    self._update_request_cls(self).update(**values)
  File "/home/lh/miniconda3/envs/mysql_dome/lib/python3.8/site-packages/gino/crud.py", line 226, in update
    value = getattr(self._instance, value_from)[key]
KeyError: 'username'

@wwwjfy models use default

wwwjfy commented 4 years ago

@guifa2015 You're right. It's not supported yet. I'll add it soon.

wwwjfy commented 4 years ago

I attempted to fix it. Please also help to verify if this works.

oleeks commented 4 years ago

Set property Insert data Throw KeyError maybe gino bug, but i am use mysql-support give feedback here

from fastapi import APIRouter

import shortuuid
from sqlalchemy import Column, String

router = APIRouter()

class Posts(db.Model):
    __tablename__ = "posts"
    id = Column(String(100), primary_key=True, default=shortuuid.uuid)
    username = Column(String(50))
    _password = Column(String(128), name='password', nullable=False)
   @property
    def password(self):
        raise AttributeError('password is not readable')

    @password.setter
    def password(self, password):
        self._password = '123456'

    def check_password(self, password) -> bool:
        if not password:
            return False
        return True

@router.post('/add')
async def add_posts():
    p = await Posts.create(username='fantix', password='123456')

  File "/home/lh/miniconda3/envs/dome/lib/python3.8/site-packages/gino/crud.py", line 220, in update
    value = getattr(self._instance, value_from)[key]
KeyError: 'password'
wwwjfy commented 4 years ago

@oleeks it's not a bug. You'll need to use _password instead of password since the property of the class is _password, and password is the column name, so it should not be used to create the instance.

I get what you're trying to do, but this doesn't work as property on Model.create.

oleeks commented 4 years ago

create @oleeks it's not a bug. You'll need to use _password instead of password since the property of the class is _password, and password is the column name, so it should not be used to create the instance.

I get what you're trying to do, but this doesn't work as property on Model.create.

@wwwjfy thanks, but sqlalchemy can be used like this, so I thought it was compatible

DB_URI = 'mysql+pymysql://{username}:{password}@{hort}:{post}/{db}?charset-utf8'.format(
    username=USERNAME, password=PASSWORD, hort=HOST, post=POST, db=DATABASE)

engine = create_engine(DB_URI)
Base = declarative_base(engine)

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(20))
    age = Column(Integer)
    _password = Column(String(128), name='password', nullable=False)

    @property
    def password(self):
        raise AttributeError('password is not readable')

    @password.setter
    def password(self, password):
        self._password = password

# Base.metadata.create_all()

session = sessionmaker(engine)()

def init_data():
    user = User(name='fantix', age=18, password='123456')
    session.add(user)
    session.commit()

if __name__ == "__main__":
    init_data()
wwwjfy commented 4 years ago

It's not our target to replicate SQLAlchemy ORM behavior. That being said, you could create a feature request.

oleeks commented 4 years ago

Error again after adding two fields; After looking at the test cases and other questions, I found that it supports datetime.now, but I made a mistake.

from fastapi import APIRouter

import shortuuid
from sqlalchemy import Column, String

router = APIRouter()

class Posts(db.Model):
    __tablename__ = "posts"
    id = Column(String(100), primary_key=True, default=shortuuid.uuid)
    username = Column(String(50))
    created = Column(DATETIME, default=datetime.now)
    updated =Column(DATETIME, default=datetime.now)

@router.post('/add')
async def add_posts():
    p = await Posts.create(username='fantix')
2020-09-11 16:10:05,537 INFO gino.engine._SAEngine INSERT INTO posts (id, username, created, updated) VALUES (%s, %s, %s, %s)
2020-09-11 16:10:05,537 INFO gino.engine._SAEngine ('Bjq5NMnSaaJXLrd9s7pPB5', 'fantix', datetime.datetime(2020, 9, 11, 16, 10, 5, 537142), datetime.datetime(2020, 9, 11, 16, 10, 5, 537142))
2020-09-11 16:10:05,537 INFO gino.engine._SAEngine COMMIT
2020-09-11 16:10:05,540 INFO gino.engine._SAEngine SELECT posts.id, posts.username, posts.created, posts.updated 
FROM posts 
WHERE posts.id = %s AND posts.username = %s AND posts.created = %s AND posts.updated = %s
2020-09-11 16:10:05,540 INFO gino.engine._SAEngine ('Bjq5NMnSaaJXLrd9s7pPB5', 'fantix', datetime.datetime(2020, 9, 11, 16, 10, 5, 537142), datetime.datetime(2020, 9, 11, 16, 10, 5, 537142))
INFO:     127.0.0.1:30712 - "POST /add HTTP/1.1" 500 Internal Server Error
ERROR:    Exception in ASGI application
Traceback (most recent call last):
  File "C:\Users\Admin\.virtualenvs\untitled2-21uViUAW\lib\site-packages\uvicorn\protocols\http\h11_impl.py", line 388, in run_asgi
    result = await app(self.scope, self.receive, self.send)
  File "C:\Users\Admin\.virtualenvs\untitled2-21uViUAW\lib\site-packages\uvicorn\middleware\proxy_headers.py", line 45, in __call__
    return await self.app(scope, receive, send)
  File "C:\Users\Admin\.virtualenvs\untitled2-21uViUAW\lib\site-packages\fastapi\applications.py", line 179, in __call__
    await super().__call__(scope, receive, send)
  File "C:\Users\Admin\.virtualenvs\untitled2-21uViUAW\lib\site-packages\starlette\applications.py", line 111, in __call__
    await self.middleware_stack(scope, receive, send)
  File "C:\Users\Admin\.virtualenvs\untitled2-21uViUAW\lib\site-packages\starlette\middleware\errors.py", line 181, in __call__
    raise exc from None
  File "C:\Users\Admin\.virtualenvs\untitled2-21uViUAW\lib\site-packages\starlette\middleware\errors.py", line 159, in __call__
    await self.app(scope, receive, _send)
  File "C:\Users\Admin\.virtualenvs\untitled2-21uViUAW\lib\site-packages\gino_starlette.py", line 86, in __call__
    await self.app(scope, receive, send)
  File "C:\Users\Admin\.virtualenvs\untitled2-21uViUAW\lib\site-packages\starlette\exceptions.py", line 82, in __call__
    raise exc from None
  File "C:\Users\Admin\.virtualenvs\untitled2-21uViUAW\lib\site-packages\starlette\exceptions.py", line 71, in __call__
    await self.app(scope, receive, sender)
  File "C:\Users\Admin\.virtualenvs\untitled2-21uViUAW\lib\site-packages\starlette\routing.py", line 566, in __call__
    await route.handle(scope, receive, send)
  File "C:\Users\Admin\.virtualenvs\untitled2-21uViUAW\lib\site-packages\starlette\routing.py", line 227, in handle
    await self.app(scope, receive, send)
  File "C:\Users\Admin\.virtualenvs\untitled2-21uViUAW\lib\site-packages\starlette\routing.py", line 41, in app
    response = await func(request)
  File "C:\Users\Admin\.virtualenvs\untitled2-21uViUAW\lib\site-packages\fastapi\routing.py", line 183, in app
    dependant=dependant, values=values, is_coroutine=is_coroutine
  File "C:\Users\Admin\.virtualenvs\untitled2-21uViUAW\lib\site-packages\fastapi\routing.py", line 133, in run_endpoint_function
    return await dependant.call(**values)
  File "C:/Users/Admin/PycharmProjects/untitled2/post.py", line 24, in add_posts
    p = await Posts.create(username='fantix')
  File "C:\Users\Admin\.virtualenvs\untitled2-21uViUAW\lib\site-packages\gino\crud.py", line 445, in _create_without_instance
    return await cls(**values)._create(bind=bind, timeout=timeout)
  File "C:\Users\Admin\.virtualenvs\untitled2-21uViUAW\lib\site-packages\gino\crud.py", line 474, in _create
    await _query_and_update(bind, self, q, list(iter(cls)), opts)
  File "C:\Users\Admin\.virtualenvs\untitled2-21uViUAW\lib\site-packages\gino\crud.py", line 854, in _query_and_update
    raise NoSuchRowError()
gino.exceptions.NoSuchRowError
oleeks commented 4 years ago

Error again after adding two fields; After looking at the test cases and other questions, I found that it supports datetime.now, but I made a mistake.

from fastapi import APIRouter

import shortuuid
from sqlalchemy import Column, String

router = APIRouter()

class Posts(db.Model):
    __tablename__ = "posts"
    id = Column(String(100), primary_key=True, default=shortuuid.uuid)
    username = Column(String(50))
    created = Column(DATETIME, default=datetime.now)
    updated =Column(DATETIME, default=datetime.now)

@router.post('/add')
async def add_posts():
    p = await Posts.create(username='fantix')
2020-09-11 16:10:05,537 INFO gino.engine._SAEngine INSERT INTO posts (id, username, created, updated) VALUES (%s, %s, %s, %s)
2020-09-11 16:10:05,537 INFO gino.engine._SAEngine ('Bjq5NMnSaaJXLrd9s7pPB5', 'fantix', datetime.datetime(2020, 9, 11, 16, 10, 5, 537142), datetime.datetime(2020, 9, 11, 16, 10, 5, 537142))
2020-09-11 16:10:05,537 INFO gino.engine._SAEngine COMMIT
2020-09-11 16:10:05,540 INFO gino.engine._SAEngine SELECT posts.id, posts.username, posts.created, posts.updated 
FROM posts 
WHERE posts.id = %s AND posts.username = %s AND posts.created = %s AND posts.updated = %s
2020-09-11 16:10:05,540 INFO gino.engine._SAEngine ('Bjq5NMnSaaJXLrd9s7pPB5', 'fantix', datetime.datetime(2020, 9, 11, 16, 10, 5, 537142), datetime.datetime(2020, 9, 11, 16, 10, 5, 537142))
INFO:     127.0.0.1:30712 - "POST /add HTTP/1.1" 500 Internal Server Error
ERROR:    Exception in ASGI application
Traceback (most recent call last):
  File "C:\Users\Admin\.virtualenvs\untitled2-21uViUAW\lib\site-packages\uvicorn\protocols\http\h11_impl.py", line 388, in run_asgi
    result = await app(self.scope, self.receive, self.send)
  File "C:\Users\Admin\.virtualenvs\untitled2-21uViUAW\lib\site-packages\uvicorn\middleware\proxy_headers.py", line 45, in __call__
    return await self.app(scope, receive, send)
  File "C:\Users\Admin\.virtualenvs\untitled2-21uViUAW\lib\site-packages\fastapi\applications.py", line 179, in __call__
    await super().__call__(scope, receive, send)
  File "C:\Users\Admin\.virtualenvs\untitled2-21uViUAW\lib\site-packages\starlette\applications.py", line 111, in __call__
    await self.middleware_stack(scope, receive, send)
  File "C:\Users\Admin\.virtualenvs\untitled2-21uViUAW\lib\site-packages\starlette\middleware\errors.py", line 181, in __call__
    raise exc from None
  File "C:\Users\Admin\.virtualenvs\untitled2-21uViUAW\lib\site-packages\starlette\middleware\errors.py", line 159, in __call__
    await self.app(scope, receive, _send)
  File "C:\Users\Admin\.virtualenvs\untitled2-21uViUAW\lib\site-packages\gino_starlette.py", line 86, in __call__
    await self.app(scope, receive, send)
  File "C:\Users\Admin\.virtualenvs\untitled2-21uViUAW\lib\site-packages\starlette\exceptions.py", line 82, in __call__
    raise exc from None
  File "C:\Users\Admin\.virtualenvs\untitled2-21uViUAW\lib\site-packages\starlette\exceptions.py", line 71, in __call__
    await self.app(scope, receive, sender)
  File "C:\Users\Admin\.virtualenvs\untitled2-21uViUAW\lib\site-packages\starlette\routing.py", line 566, in __call__
    await route.handle(scope, receive, send)
  File "C:\Users\Admin\.virtualenvs\untitled2-21uViUAW\lib\site-packages\starlette\routing.py", line 227, in handle
    await self.app(scope, receive, send)
  File "C:\Users\Admin\.virtualenvs\untitled2-21uViUAW\lib\site-packages\starlette\routing.py", line 41, in app
    response = await func(request)
  File "C:\Users\Admin\.virtualenvs\untitled2-21uViUAW\lib\site-packages\fastapi\routing.py", line 183, in app
    dependant=dependant, values=values, is_coroutine=is_coroutine
  File "C:\Users\Admin\.virtualenvs\untitled2-21uViUAW\lib\site-packages\fastapi\routing.py", line 133, in run_endpoint_function
    return await dependant.call(**values)
  File "C:/Users/Admin/PycharmProjects/untitled2/post.py", line 24, in add_posts
    p = await Posts.create(username='fantix')
  File "C:\Users\Admin\.virtualenvs\untitled2-21uViUAW\lib\site-packages\gino\crud.py", line 445, in _create_without_instance
    return await cls(**values)._create(bind=bind, timeout=timeout)
  File "C:\Users\Admin\.virtualenvs\untitled2-21uViUAW\lib\site-packages\gino\crud.py", line 474, in _create
    await _query_and_update(bind, self, q, list(iter(cls)), opts)
  File "C:\Users\Admin\.virtualenvs\untitled2-21uViUAW\lib\site-packages\gino\crud.py", line 854, in _query_and_update
    raise NoSuchRowError()
gino.exceptions.NoSuchRowError

Just convert datetime.now into a string 。 like

def time2str(sft='%Y-%m-%d %H:%M:%S'):
    return  datetime.now()strftime(sft)

class Posts(db.Model):
    __tablename__ = "posts"
    id = Column(String(100), primary_key=True, default=shortuuid.uuid)
    username = Column(String(50))
    created = Column(DATETIME, default=time2str)
    updated =Column(DATETIME, default=time2str)
wwwjfy commented 4 years ago

Thanks for review. I plan to do cleanup, check autocommit and the errors raised above this weekend.

wwwjfy commented 4 years ago

@oleeks Thanks for the report. It's fixed now. The reason is that MySQL doesn't keep the fractional part of a date by default, i.e. the millisecond part, causing the condition can't be satisfied. The fix is to use primary key only to fetch the inserted row. It'll still fail if there is no primary key of the table, or datetime is the primary key. I don't have a good solution. I see SQLAlchemy has the same problem. I'm fine to have it as a known issue, because it's not a practical db design anyway.

oleeks commented 4 years ago

it's me again .....

When updating the same data concurrently, "NoSuchRowError" appears. Is there any better suggestion?

ERROR:    Exception in ASGI application
Traceback (most recent call last):
  File "/home/lh/miniconda3/envs/admin/lib/python3.8/site-packages/uvicorn/protocols/http/httptools_impl.py", line 390, in run_asgi
    result = await app(self.scope, self.receive, self.send)
  File "/home/lh/miniconda3/envs/admin/lib/python3.8/site-packages/uvicorn/middleware/proxy_headers.py", line 45, in __call__
    return await self.app(scope, receive, send)
  File "/home/lh/miniconda3/envs/admin/lib/python3.8/site-packages/fastapi/applications.py", line 179, in __call__
    await super().__call__(scope, receive, send)
  File "/home/lh/miniconda3/envs/admin/lib/python3.8/site-packages/starlette/applications.py", line 111, in __call__
    await self.middleware_stack(scope, receive, send)
  File "/home/lh/miniconda3/envs/admin/lib/python3.8/site-packages/starlette/middleware/errors.py", line 181, in __call__
    raise exc from None
  File "/home/lh/miniconda3/envs/admin/lib/python3.8/site-packages/starlette/middleware/errors.py", line 159, in __call__
    await self.app(scope, receive, _send)
  File "/home/lh/miniconda3/envs/admin/lib/python3.8/site-packages/starlette/middleware/cors.py", line 78, in __call__
    await self.app(scope, receive, send)
  File "/home/lh/miniconda3/envs/admin/lib/python3.8/site-packages/gino_starlette.py", line 86, in __call__
    await self.app(scope, receive, send)
  File "/home/lh/miniconda3/envs/admin/lib/python3.8/site-packages/starlette/exceptions.py", line 82, in __call__
    raise exc from None
  File "/home/lh/miniconda3/envs/admin/lib/python3.8/site-packages/starlette/exceptions.py", line 71, in __call__
    await self.app(scope, receive, sender)
  File "/home/lh/miniconda3/envs/admin/lib/python3.8/site-packages/starlette/routing.py", line 566, in __call__
    await route.handle(scope, receive, send)
  File "/home/lh/miniconda3/envs/admin/lib/python3.8/site-packages/starlette/routing.py", line 227, in handle
    await self.app(scope, receive, send)
  File "/home/lh/miniconda3/envs/admin/lib/python3.8/site-packages/starlette/routing.py", line 41, in app
    response = await func(request)
  File "/home/lh/miniconda3/envs/admin/lib/python3.8/site-packages/fastapi/routing.py", line 182, in app
    raw_response = await run_endpoint_function(
  File "/home/lh/miniconda3/envs/admin/lib/python3.8/site-packages/fastapi/routing.py", line 133, in run_endpoint_function
    return await dependant.call(**values)
  File "/home/lh/works/admins/admin/src/routes/frontend/order.py", line 83, in create_order
    await account.update(balance=new_balance).apply()
  File "/home/lh/miniconda3/envs/admin/lib/python3.8/site-packages/gino/crud.py", line 173, in apply
    await _query_and_update(
  File "/home/lh/miniconda3/envs/admin/lib/python3.8/site-packages/gino/crud.py", line 858, in _query_and_update
    row = await _execute_and_fetch(conn, query)
  File "/home/lh/miniconda3/envs/admin/lib/python3.8/site-packages/gino/crud.py", line 828, in _execute_and_fetch
    raise NoSuchRowError()
gino.exceptions.NoSuchRowError
wwwjfy commented 4 years ago

@oleeks is it related to concurrent updates? could you share the model and code you're using? I can't tell if this can be caused by concurrent requests.

oleeks commented 4 years ago

@oleeks is it related to concurrent updates? could you share the model and code you're using? I can't tell if this can be caused by concurrent requests.

okey.I am operating on business code，Initiating two operations at the same time, logically speaking, there should be no problems; probably like this;

class Account(db.Model):
    __tablename__ = "account"
    id = db.Column(db.String(22),
                   default=shortuuid.uuid,
                   primary_key=True,
                   unique=True)
    uid = Column(String(22), nullable=False)
    balance = Column(DECIMAL(5, 2), nullable=False, default=0)

from fastapi import APIRouter
router = APIRouter()

@router.post('/create-order')
async def create_order():
    account = await Account.query.where(Account.uid == uid
                                        ).gino.first()
    # omit ....
   new_balance = ( account.balance- Decimal('0.01')).quantize(Decimal('0.00'))
   await account.update(balance=new_balance).apply()
import httpx
import asyncio

async def http(client):
    url = "http://127.0.0.1:8000/v1/create-order"
    headers = {
        "Accept": "application/json, text/plain, */*",
    }
    result = await client.post(url=url,headers=headers, timeout=30)
    print(result.text)

async def main():
    async with httpx.AsyncClient() as client:
        task_list = []
        for _ in range(2):
            req = http(client)
            task = asyncio.create_task(req)
            task_list.append(task)

        await asyncio.gather(*task_list)

if __name__ == "__main__":
    asyncio.run(main())
oleeks commented 4 years ago

@oleeks is it related to concurrent updates? could you share the model and code you're using? I can't tell if this can be caused by concurrent requests.

Is the transaction enabled by default, or does it need to be added? What to do with additional addition

wwwjfy commented 4 years ago

It's fixed. The reason is I misunderstood the meaning of affected_rows in the result of MySQL, which means how many rows actually get updated. So in your second request, the value is the same, causing the affected_rows to be 0.

For the transaction question, in MySQL, autocommit is set to true by default. If you need transactions, you'll need to explicitly wrap the block in a transaction.

@oleeks Thanks for your experiments for this PR and bug reports! Ideally I should apply it to my own project but sadly I don't have one now, so I miss a lot of cases. Really appreciate your help!

oleeks commented 4 years ago

@ Not very familiar with the use of transactions, Is used like this？

@router.post('/create-order')
async def create_order():
    async with db.transaction():
        account = await Account.query.where(Account.uid ==
uid).gino.first()
        new_balance = ( account.balance-
Decimal('0.01')).quantize(Decimal('0.00'))
        await account.update(balance=new_balance).apply()

At the same time, due to concurrency, the value of account.balance from asynchronous query is the same, and the balance referenced by new_balance is the same every time, which causes an error in the result.

Tried using lock, But the impact is too big, use synchronization in this scenario? If so, can you provide a sample code for reference? Or have better suggestions in this scenario

lock = asyncio.Lock()

@router.post('/create-order')
async def create_order():
    async with lock:
    # async with db.acquire():
        account = await Account.query.where(Account.uid == uid).gino.first()
        new_balance = (account.balance -
total_cost).quantize(Decimal('0.00'))
        await account.update(balance=new_balance).apply()

On Tue, Sep 15, 2020 at 12:51 AM Tony Wang notifications@github.com wrote:

It's fixed. The reason is I misunderstood the meaning of affected_rows in the result of MySQL, which means how many rows actually get updated. So in your second request, the value is the same, causing the affected_rows to be 0.

For the transaction question, in MySQL, autocommit is set to true by default. If you need transactions, you'll need to explicitly wrap the block in a transaction.

@oleeks https://github.com/oleeks Thanks for your experiments for this PR and bug reports! Ideally I should apply it to my own project but sadly I don't have one now, so I miss a lot of cases. Really appreciate your help!

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/python-gino/gino/pull/685#issuecomment-692181272, or unsubscribe https://github.com/notifications/unsubscribe-auth/ADKI42ZGO6O7WQMWRVEERWDSFZCZ5ANCNFSM4NK5VINQ .

wwwjfy commented 4 years ago

@oleeks right, lock here is probably not a good idea. You could take a look at transaction isolation level, or you can try to use optimistic lock with a version, where the affected rows can be useful.

oleeks commented 4 years ago

@oleeks right, lock here is probably not a good idea. You could take a look at transaction isolation level, or you can try to use optimistic lock with a version, where the affected rows can be useful. I searched, optimistic lock is used like this in django

goods = GoodsInfo.objects.filter(id=goods_id).first()
result = GoodsInfo.objects.filter(id=goods.id, stock=goods.origin_stock).update(stock=goods.origin_stock - count)

I don’t see any relevant examples in the gino documentation. Is this how to use it? Doesn't feel right

from sqlalchemy import and_
account = await Account.query.where(Account.uid == uid).gino.first()
await Account.update.values(balance=new_balance).where(and_(Account.id == account.id,
Account.balance == balance)).gino.status()
wwwjfy commented 4 years ago

isolation level is easier, but it needs to be set for each session if this is not applicable for other transactions.

For optimistic lock, I'd suggest to use a version instead of balance. To use it we'd need some improvements to show affected rows. For example.

class Account:
  version = Column(Integer, nullable=False)

account = await Account.query.where(Account.uid == uid).gino.first()
await Account.update.values(balance=new_balance, version=account.version+1).where(and_(Account.id == account.id,
        Account.version == account.version)).gino.status()

The second update can't match Account.version == account.version because of the increment.

oleeks commented 4 years ago

isolation level is easier, but it needs to be set for each session if this is not applicable for other transactions.

For optimistic lock, I'd suggest to use a version instead of balance. To use it we'd need some improvements to show affected rows. For example.

class Account:
  version = Column(Integer, nullable=False)

account = await Account.query.where(Account.uid == uid).gino.first()
await Account.update.values(balance=new_balance, version=account.version+1).where(and_(Account.id == account.id,
        Account.version == account.version)).gino.status()

The second update can't match Account.version == account.version because of the increment.

@wwwjfy thaks

wwwjfy commented 4 years ago

I'll merge this first. Feel free to raise issues 🙏

xnuinside commented 3 years ago

Does MySQL works ok with Gino? Or exists any reason why info about it not in README.md in repo?:) and not in docs