python-gino / gino Goto Github PK
View Code? Open in Web Editor NEWGINO Is Not ORM - a Python asyncio ORM on SQLAlchemy core.
Home Page: https://python-gino.org/
License: Other
GINO Is Not ORM - a Python asyncio ORM on SQLAlchemy core.
Home Page: https://python-gino.org/
License: Other
Hi!
As a tornado user I think get_or_404
should throw a customizable error class.
I'm not sure where to put this setting though.
README should have at least:
Error to create record in Sanic after upgraded to version 0.5.0
@bp.post('/users')
async def add_new_user(request):
new_obj = request.json #dict
u = await User.create(**new_obj)
return ajax_maint_ok(u.id)
Call it like this:
DATA='{"nickname":"n1"}'
curl \
http://dserver:9901/demo/users \
-X POST \
-H "Content-Type: application/json" \
-H "Accept: text/html,application/json" \
-d ${DATA}
So that user could get model objects from raw SQL. For example:
users = await db.text('SELECT * FROM users WHERE id > :num').gino.model(User).return_model(True).all(num=28, bind=db.bind)
Error when create table record.
--model:
class Worker(db.Model):
__tablename__ = 'workers'
id = db.Column(db.BigInteger(), primary_key=True)
nickname = db.Column(db.Unicode(), default='noname')
dcount = db.Column(db.Integer(),nullable=True)
money = db.Column(db.Numeric(12,2),nullable=True)
added_on = db.Column(db.DateTime(), default=get_now_datetime())
added_date = db.Column(db.String(10), default=get_now_date_text())
def __repr__(self):
return '{}<{}>'.format(self.nickname, self.id)
-- error from code:
u1 = await Worker.create(nickname='fantix}',dcount=1,money=Decimal('123.12'))
u2 = await Worker.get(u1.id)
-- error msg:
Traceback (most recent call last):
File "/usr/local/lib/python3.6/site-packages/sanic/app.py", line 503, in handle_request
response = await response
File "/usr/local/lib/python3.6/asyncio/coroutines.py", line 109, in __next__
return self.gen.send(None)
File "/src/apps/utils/log.py", line 34, in decorated_function
return await f(*args, **kwargs)
File "/usr/local/lib/python3.6/asyncio/coroutines.py", line 109, in __next__
return self.gen.send(None)
...
File "/usr/local/lib/python3.6/asyncio/coroutines.py", line 109, in __next__
return self.gen.send(None)
File "/usr/local/lib/python3.6/site-packages/gino/crud.py", line 184, in get
return await cls.__metadata__.first(clause, bind=bind)
File "/usr/local/lib/python3.6/asyncio/coroutines.py", line 109, in __next__
return self.gen.send(None)
File "/usr/local/lib/python3.6/site-packages/gino/api.py", line 207, in first
conn, clause, *multiparams, **params)
File "/usr/local/lib/python3.6/asyncio/coroutines.py", line 109, in __next__
return self.gen.send(None)
File "/usr/local/lib/python3.6/site-packages/gino/dialect.py", line 240, in do_first
connection, clause, multiparams, params)
File "/usr/local/lib/python3.6/asyncio/coroutines.py", line 109, in __next__
return self.gen.send(None)
File "/usr/local/lib/python3.6/site-packages/gino/dialect.py", line 226, in _execute_clauseelement
item = context.process_rows(rows, return_model=return_model)
File "/usr/local/lib/python3.6/site-packages/gino/dialect.py", line 107, in process_rows
rv = rows = self.get_result_proxy().process_rows(rows)
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 877, in get_result_proxy
return result.ResultProxy(self)
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/result.py", line 653, in __init__
self._init_metadata()
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/result.py", line 682, in _init_metadata
self._metadata = ResultMetaData(self, cursor_description)
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/result.py", line 215, in __init__
num_ctx_cols, cols_are_ordered, textual_ordered)
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/result.py", line 379, in _merge_cursor_description
in enumerate(result_columns)
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/result.py", line 378, in <listcomp>
) for idx, (key, name, obj, type_)
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 840, in get_result_processor
return type_._cached_result_processor(self.dialect, coltype)
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/sql/type_api.py", line 472, in _cached_result_processor
d[coltype] = rp = d['impl'].result_processor(dialect, coltype)
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/sql/sqltypes.py", line 596, in result_processor
'storage.' % (dialect.name, dialect.driver))
AttributeError: 'AsyncpgDialect' object has no attribute 'driver'
Hello! So, there's a cython extension now in GINO. I've noticed that because of this:
Complete output from command python setup.py egg_info:
zip_safe flag not set; analyzing archive contents...
Installed /tmp/pip-build-9tbfp_79/gino/.eggs/pytest_runner-2.12-py3.6.egg
Searching for Cython>=0.24
Reading https://pypi.python.org/simple/Cython/
Downloading https://pypi.python.org/packages/68/41/2f259b62306268d9cf0d6434b4e83a2fb1785b34cfce27fdeeca3adffd0e/Cython-0.26.1.tar.gz#md5=8853dcb78749a786ad5ccdc007a8932f
Best match: Cython 0.26.1
Processing Cython-0.26.1.tar.gz
Writing /tmp/easy_install-abw0vw8f/Cython-0.26.1/setup.cfg
Running Cython-0.26.1/setup.py -q bdist_egg --dist-dir /tmp/easy_install-abw0vw8f/Cython-0.26.1/egg-dist-tmp-jv4k3ocj
Unable to find pgen, not compiling formal grammar.
warning: no files found matching 'Doc/*'
warning: no files found matching '*.pyx' under directory 'Cython/Debugger/Tests'
warning: no files found matching '*.pxd' under directory 'Cython/Debugger/Tests'
warning: no files found matching '*.pxd' under directory 'Cython/Utility'
/tmp/easy_install-abw0vw8f/Cython-0.26.1/Cython/Plex/Scanners.c:4:20: fatal error: Python.h: No such file or directory
compilation terminated.
Traceback (most recent call last):
File "/usr/lib/python3.6/distutils/unixccompiler.py", line 118, in _compile
extra_postargs)
File "/usr/lib/python3.6/distutils/ccompiler.py", line 909, in spawn
spawn(cmd, dry_run=self.dry_run)
File "/usr/lib/python3.6/distutils/spawn.py", line 36, in spawn
_spawn_posix(cmd, search_path, dry_run=dry_run)
File "/usr/lib/python3.6/distutils/spawn.py", line 159, in _spawn_posix
% (cmd, exit_status))
distutils.errors.DistutilsExecError: command 'x86_64-linux-gnu-gcc' failed with exit status 1
During handling of the above exception, another exception occurred:
... and so on ....
----------------------------------------
Command "python setup.py egg_info" failed with error code 1 in /tmp/pip-build-9tbfp_79/gino/
While this is most likely problem with my environment and not the GINO itself (and, even more, I know how to fix it), the same things may cause other users to suffer. Necessity to build cython extensions complicates deployment because now one have to install gcc, cython, python-dev etc. instead of just doing pip install gino
.
So I propose making life a little easier by either
asyncpg.protocol.protocol._create_record
for updating records or ask asyncpg's community to implement an official method for updating records;Also, if we choose to use cython than I see no reason why keep other parts of project purely pythonic.
ModelType
and Model
classes are nested in the __init__
method of class Gino
. That is way too much nested - it is difficult to read, and to do any extension on it. It would be nicer if the class creation can be done in a more reusable and flat way.
Now the refactor is mostly done, it's time for tests. Let's just list all test cases here, and mark them as completed once coded.
In #9 only fetch
and cursor
are delegated, we may still need to delegate fetchrow
and execute
.
老铁,更新一下文档地址吧~
非常感谢 ^_^
Similar to asyncpg, GINO should offer prepared statement API on connection level.
Now PreparedStatement
infrastructure is ready, the tricky part is to get input parameter processors of SQLAlchemy work correctly.
If possible, use PEP 487
The following code always return empty result, but the table has records:
users = await User.query.where(or_(User.id==10,User.id==15)).gino.all()
users = await User.query.where(User.id>=10).where(User.id<=15)).gino.all()
users = await User.query.where(User.id.between(10,15)).gino.all()
Would it be better if GINO doesn't hack the API of asyncpg to accept SQLAlchemy clauses and return objects, but define new APIs for ORM operations?
Create , update, delete actions should all rollback, but only update and delete action rollback.
@bp.get('/d0418')
async def use_postgres18(request):
db = request.app.db
await User.delete.gino.all()
await User.create(nickname='testonly')
async with db.transaction() as (conn, tx):
try:
await User.create(nickname='testonly2')
await User.update(nickname='fantix').apply()
await User.delete.gino.all()
raise Exception
except:
pass
users = await User.query.gino.all()
return ajax_data({'users':users})
User.create(nickname='testonly2')
action not rollback.
When an error happens in connection acquire procedure, connection stack is not cleared. This leads to an infinitely increasing number of LazyConnection
s being stored in stack.
Bdist wheel from here doesn't contain c extensions:
amatanhead@ah01h$ pip install gino --no-cache-dir
Collecting gino
Downloading gino-0.5.2-py2.py3-none-any.whl
Requirement already satisfied: asyncpg~=0.12 in /home/amatanhead/.virtualenvs/statbox-abt-backend/lib/python3.6/site-packages (from gino)
Requirement already satisfied: SQLAlchemy==1.1.14 in /home/amatanhead/.virtualenvs/statbox-abt-backend/lib/python3.6/site-packages (from gino)
Installing collected packages: gino
Successfully installed gino-0.5.2
amatanhead@ah01h$ python
Python 3.6.2 (default, Jul 20 2017, 08:43:29)
[GCC 5.4.1 20170519] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import gino
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/home/amatanhead/.virtualenvs/statbox-abt-backend/lib/python3.6/site-packages/gino/__init__.py", line 1, in <module>
from .api import Gino
File "/home/amatanhead/.virtualenvs/statbox-abt-backend/lib/python3.6/site-packages/gino/api.py", line 9, in <module>
from .connection import GinoConnection
File "/home/amatanhead/.virtualenvs/statbox-abt-backend/lib/python3.6/site-packages/gino/connection.py", line 3, in <module>
from .dialect import GinoCursorFactory
File "/home/amatanhead/.virtualenvs/statbox-abt-backend/lib/python3.6/site-packages/gino/dialect.py", line 3, in <module>
from sqlalchemy import cutils, util
ImportError: cannot import name 'cutils'
Please advice, thanks in advance!
asyncpgsa uses these libraries for test:
pytest
pytest-asyncio
pytest-capturelog>=0.7
code:
from sanic import response
...
u1 = await User.create(nickname='fantixd')
u2 = await User.get(u1.id)
return response.json(u2)
Error:
File "/usr/local/lib/python3.6/site-packages/sanic/response.py", line 246, in json
return HTTPResponse(json_dumps(body, **kwargs), headers=headers,
OverflowError: Maximum recursion level reached
So, I've started writing tornado extension and realized that enable_task_local
doesn't work inside of the tornado handlers. The reason is that tornado doesn't wrap its request into tasks so asyncio.Task.current_task()
always returns None
.
Currently, GINO, SQLAlchemy and asyncpg works together in a way that user needs to manually take care of the three - create a GINO environment, define columns with SQLAlchemy, create asyncpg pools and feed connection object to GINO operations. Which is fine, it is expected to be used like this, as the low-level interface.
However, if GINO could delegate asyncpg interface, user code could be very much simplified - GINO shall be able to map the rows to the correct model automatically for example. Let's try to make such delegation, in an optional way on top of the GINO low-level interface.
Perhaps at least offer a request-scoped lazy-borrowed connection.
In SQLAlchemy, it may like this:
SELECT
users.name,
(SELECT count(addresses.id) AS count_1 FROM addresses WHERE users.id = addresses.user_id) AS anon_1
FROM users
stmt = select([func.count(addresses.c.id)]).where(users.c.id == addresses.c.user_id).as_scalar()
conn.execute(select([users.c.name, stmt])).fetchall()
Prefer simple but real use cases with for example Sanic.
In Model.get
and Model.get_or_404
, primary key is assumed to be exactly id
. This hard code is bad, and temporary. We should follow the way SQLAlchemy ORM does it, in sqlalchemy.orm.query.Query:get
.
I write a general sanic pagination jsonify method like this:
@bp.get('/d048')
async def use_postgres8(request):
"""use ajax_list"""
db = request.app.db
r = await db.scalar(db.select([db.func.count(User.id)]).where(User.nickname.contains('d')))
users = User.query.where(User.nickname.contains('d'))
return await ajax_list(request,users,total_count=r)
The ajax_list method like this:
async def ajax_list(request,queryset,total_count):
#URL例子:http://dserver:9901/demo/d048?limit=1&start=1&sort=id&dir=DESC
if total_count is None:
raise Exception('must provide total_count!')
params = request.args
db = request.app.db
#排序
sort_fld = params.get('sort')
if sort_fld:
if params.get('dir')=='DESC':
queryset = queryset.order_by(db.desc(sort_fld))
else:
queryset = queryset.order_by(sort_fld)
#分页
limit= int(params.get('limit',1))
start = int(params.get('start',0))
queryset = queryset.limit(limit).offset(start)
recs = await queryset.gino.all() #<==== here execute the query actually
data = {"totalCount": total_count, "rows": jsonify_model(recs)}
return ajax_data(data)
If I can write like this ,it is very good:
...
recs = await queryset.gino.all() #<==== here execute the query actually
total_count = await queryset.gino.count()
data = {"totalCount": total_count, "rows": jsonify_model(recs)}
return ajax_data(data)
In GINO 0.1 there are interfaces to INSERT
and SELECT
. We need UPDATE
and DELETE
interfaces in 0.2. It is a bit trickier to design them than implement them.
All of the current examples punt on showing how to to create the database tables:
# You will need to create the database and table manually
Requiring the user to create the database itself is ok, but having to create all application tables manually would, at least for me, be a show stopper for adopting Gino.
We would need to be able to use something equivalent to SQLAlchemy MetaData.sync_all(). I tried calling Gino().sync_all() (as Gino inherits from sa.MetaData), but it complained about not having a database connected to it. Maybe I did something wrong?
Does Gino() support a way to semi-automatically create the database tables from the application defined Models? This would be used either on application startup or from separate adminstration scripts. We might even defers to calling non-async methods of SQLAlchemy before the event loop is started. Anything that allows us to use the defined models would be good.
Simply follow the SQLAlchemy ORM way, support __table_args__
:
http://docs.sqlalchemy.org/en/latest/orm/extensions/declarative/table_config.html
The simplest way to reproduce the issue, is to run test_basic.test_get using get_or_404
instead of get
with first argument u1.id + 1
.
The error is ModuleNotFoundError: No module named 'sanic'
For example, the timeout
parameter is needed on Model.get
.
Honestly I have no idea yet.
Update:
GINO now has a loader mechanism to load a result matrix into objects on need, please see examples below. It allows relationship be implemented in a primitive way. Next, we'll try to introduce some high level relationship encapsulation.
Should GINO ever do that?
It will be as simple as a few import delegates, instead of:
from sqlalchemy import Column
# id = Column(...
you can:
from gino import Gino
db = Gino()
# id = db.Column(...
Hello!
Right now, GinoTransaction.__aenter__
returns the result of the underlying transaction wrapper's __aenter__
:
# self._ctx is usually an asyncpg.Transaction instance
return conn, await self._ctx.__aenter__()
However, asyncpg.Transaction.__aenter__()
doesn't return anything.
I propose either returning (conn , self._ctx)
from the GinoTransaction.__aenter__
or changing signature of the .transaction()
method to return just a connection (or maybe nothing).
Some methods from upstream MetaData
are not working correctly if called with Gino
object, create_all
with no parameter for example. This may cause confusions. These methods should be protected against "wrong" usage somehow, either:
NotImplementedError
with explanationso that it's more discoverable. refs https://github.com/blog/2309-introducing-topics
Without task local, it would be required to pass the connection object to Model-level APIs within transactions, or else it will be mistakingly thought to be within the same transaction but actually not.
Choose one task local implementation, and make GinoPool.acquire
use it.
Currently GINO reuses the ExecutionContext
of SQLAlchemy to extract SQL and parameters from compiled SQLAlchemy clause elements, because in SQLAlchemy column defaults and parameters as tuple are calculated in execution time.
ExecutionContext
is bound to SQLAlchemy Connection
and DB-API connection, but fortunately SQL extraction doesn't make any use of them. Therefore GINO simply mocked these two objects.
Which is hacky. Is there a not-so-hacky way to do so?
api.py
engine.py
crud.py
declarative.py
strategies.py
transaction.py
loader.py
aiocontextvars.py
json_support.py
exceptions.py
ext/__init__.py
Dialect related:
dialects/__init__.py
dialects/base.py
dialects/asyncpg.py
Won't fix:
schema.py
I use vegeta to load test.
--- to be tested Sanic view:
async def use_postgres1(request):
d = get_now_datetime()
u1 = await Worker.create(nickname=f'fantix{d}',dcount=1,money=Decimal('123.12'))
u2 = await Worker.get(u1.id)
return ajax_data({'rid':u2.id,'name':u2.nickname})
--- db server config
app.config.DB_HOST = DB_CONFIG['host']
app.config.DB_USER = DB_CONFIG['user']
app.config.DB_PORT = DB_CONFIG['port']
app.config.DB_PASSWORD = DB_CONFIG['password']
app.config.DB_DATABASE = DB_CONFIG['database']
app.config.DB_POOL_MIN_SIZE = 5
app.config.DB_POOL_MAX_SIZE = 10000
--- load test script:
[I] $echo "GET http://dserver:8023/demo/d0401" | vegeta attack -rate=200 -duration=5s | vegeta report -output t24.html -reporter plot
--- error message:
[2017-09-11 Mon 00:51:35.145][fusion2_api_min.local]Exception occured in one of response middleware handlers
Traceback (most recent call last):
File "/usr/local/lib/python3.6/site-packages/sanic/app.py", line 527, in handle_request
response)
File "/usr/local/lib/python3.6/site-packages/sanic/app.py", line 660, in _run_response_middleware
_response = await _response
File "/usr/local/lib/python3.6/site-packages/gino-0.5.2-py3.6.egg/gino/ext/sanic.py", line 85, in on_response
await ctx.__aexit__(None, None, None)
File "/usr/local/lib/python3.6/site-packages/gino-0.5.2-py3.6.egg/gino/pool.py", line 122, in __aexit__
await self.pool.release(con)
File "/usr/local/lib/python3.6/site-packages/gino-0.5.2-py3.6.egg/gino/pool.py", line 193, in release
return await connection.release(close=True)
File "/usr/local/lib/python3.6/site-packages/gino-0.5.2-py3.6.egg/gino/pool.py", line 70, in release
conn_to_release = await fut
File "/usr/local/lib/python3.6/site-packages/sanic/app.py", line 503, in handle_request
response = await response
File "/opt/app/apps/utils/log.py", line 34, in decorated_function
return await f(*args, **kwargs)
File "/opt/app/apps/demo/views/gino.py", line 38, in use_postgres1
u1 = await Worker.create(nickname=f'中文fantix{d}',dcount=1,money=Decimal('123.12'))
File "/usr/local/lib/python3.6/site-packages/gino-0.5.2-py3.6.egg/gino/crud.py", line 162, in create
row = await cls.__metadata__.first(q, bind=bind)
File "/usr/local/lib/python3.6/site-packages/gino-0.5.2-py3.6.egg/gino/api.py", line 207, in first
conn, clause, *multiparams, **params)
File "/usr/local/lib/python3.6/site-packages/gino-0.5.2-py3.6.egg/gino/dialect.py", line 242, in do_first
connection, clause, multiparams, params)
File "/usr/local/lib/python3.6/site-packages/gino-0.5.2-py3.6.egg/gino/dialect.py", line 224, in _execute_clauseelement
prepared = await context.prepare()
File "/usr/local/lib/python3.6/site-packages/gino-0.5.2-py3.6.egg/gino/dialect.py", line 117, in prepare
return await self.connection.prepare(self.statement)
File "/usr/local/lib/python3.6/site-packages/gino-0.5.2-py3.6.egg/gino/dialect.py", line 61, in prepare
rv = self._stmt = await self._conn.prepare(statement)
File "/usr/local/lib/python3.6/site-packages/gino-0.5.2-py3.6.egg/gino/pool.py", line 89, in wrapper
conn = await self.get_connection()
File "/usr/local/lib/python3.6/site-packages/gino-0.5.2-py3.6.egg/gino/pool.py", line 57, in get_connection
self._conn = await self._conn_task
File "/usr/local/lib/python3.6/site-packages/asyncpg/pool.py", line 453, in _acquire
return await _acquire_impl()
File "/usr/local/lib/python3.6/site-packages/asyncpg/pool.py", line 444, in _acquire_impl
proxy = await ch.acquire() # type: PoolConnectionProxy
File "/usr/local/lib/python3.6/site-packages/asyncpg/pool.py", line 147, in acquire
await self.connect()
File "/usr/local/lib/python3.6/site-packages/asyncpg/pool.py", line 138, in connect
connection_class=self._pool._connection_class)
File "/usr/local/lib/python3.6/site-packages/asyncpg/connect_utils.py", line 274, in _connect_addr
await asyncio.wait_for(connected, loop=loop, timeout=timeout)
File "/usr/local/lib/python3.6/asyncio/tasks.py", line 358, in wait_for
return fut.result()
asyncpg.exceptions.TooManyConnectionsError: sorry, too many clients already
Hello!
When one select columns with Table.select()
, the returned object will have only part of attributes. Currently, gino (0.4.1) would return None
when accessing attributes that weren't selected. However, accessing unselected attributes usually mean that there is a bug somewhere so it would be better to throw an appropriate error.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.