{ "info": { "author": "Vladimir Magamedov", "author_email": "vladimir@magamedov.com", "bugtrack_url": null, "classifiers": [ "Development Status :: 5 - Production/Stable", "Intended Audience :: Developers", "License :: OSI Approved :: BSD License", "Operating System :: OS Independent", "Programming Language :: Python", "Programming Language :: Python :: 3", "Programming Language :: Python :: Implementation :: CPython", "Programming Language :: Python :: Implementation :: PyPy", "Topic :: Database :: Front-Ends" ], "description": "============\nSQLConstruct\n============\n\n`SQLConstruct` is a functional approach to query database using `SQLAlchemy`\nlibrary. It was written to reach more speed without introducing unmaintainable\nand verbose code. On the contrary, code becomes simpler, so there are less\nchances of shooting yourself in the foot.\n\nMain problems it aims to solve:\n\n- ORM overhead in read-only ``SELECT`` queries;\n- Network traffic when loading unnecessary columns;\n- Code complexity;\n- N+1 problem.\n\nFinal\n=====\n\nYou describe what you want to get from the database:\n\n.. code-block:: python\n\n from sqlconstruct import Construct, if_\n\n product_struct = Construct({\n 'name': Product.name,\n 'url': url_for_product.defn(Product),\n 'image_url': if_(\n Image.id,\n then_=url_for_image.defn(Image, 100, 100),\n else_=None,\n ),\n })\n\nAnd you get it. `SQLConstruct` knows which columns you need and how transform\nthem into suitable to use format:\n\n.. code-block:: python\n\n >>> product = (\n ... session.query(product_struct)\n ... .outerjoin(Product.image)\n ... .first()\n ... )\n ...\n >>> product.name\n 'Foo product'\n >>> product.url\n '/p1-foo-product.html'\n >>> product.image_url\n '//images.example.st/123-100x100-foo.jpg'\n\nFull story\n==========\n\nBasic preparations:\n\n.. code-block:: python\n\n from sqlalchemy import create_engine\n from sqlalchemy import Column, Integer, String, Text, ForeignKey\n from sqlalchemy.orm import Session, relationship, eagerload\n from sqlalchemy.ext.declarative import declarative_base\n\n engine = create_engine('sqlite://')\n Base = declarative_base()\n\n class Image(Base):\n __tablename__ = 'image'\n\n id = Column(Integer, primary_key=True)\n name = Column(String)\n\n class Product(Base):\n __tablename__ = 'product'\n\n id = Column(Integer, primary_key=True)\n name = Column(String)\n image_id = Column(Integer, ForeignKey(Image.id))\n description = Column(Text)\n\n image = relationship(Image)\n\n Base.metadata.create_all(engine)\n\n session = Session(engine)\n session.add(Product(name='Foo product', image=Image(name='Foo.jpg')))\n session.commit()\n\n def slugify(name):\n # very dumb implementation, just for an example\n return name.lower().replace(' ', '-')\n\n def url_for_product(product):\n return '/p{id}-{name}.html'.format(\n id=product.id,\n name=slugify(product.name),\n )\n\n def url_for_image(image, width, height):\n return '//images.example.st/{id}-{width}x{height}-{name}'.format(\n id=image.id,\n width=width,\n height=height,\n name=slugify(image.name),\n )\n\nUsual way:\n\n.. code-block:: python\n\n >>> product = (\n ... session.query(Product)\n ... .options(eagerload(Product.image))\n ... .first()\n ... )\n ...\n >>> product.name\n u'Foo product'\n >>> url_for_product(product)\n '/p1-foo-product.html'\n >>> url_for_image(product.image, 100, 100) if product.image else None\n '//images.example.st/1-100x100-foo.jpg'\n\nDisadvantages:\n\n- ``description`` column isn't deferred, it will be loaded every time;\n- if you will mark ``description`` column as deferred, this can introduce N+1\n problem somewhere else in your project;\n- if you forgot to ``eagerload`` ``Product.image`` you will also get N+1\n problem;\n- you have to pass model instances as arguments everywhere in the project and\n this tends to code complexity, because you don't know how they will be used in\n the future;\n- model instances creation isn't cheap, CPU time grows with number of columns,\n even if they are all deferred.\n\nInitial solution:\n\n.. code-block:: python\n\n from sqlconstruct import Construct, apply_, if_\n\n def url_for_product(product_id, product_name):\n return '/p{id}-{name}.html'.format(\n id=product_id,\n name=slugify(product_name),\n )\n\n def url_for_image(image_id, image_name, width, height):\n return '//images.example.st/{id}-{width}x{height}-{name}'.format(\n id=image_id,\n width=width,\n height=height,\n name=slugify(image_name),\n )\n\n product_struct = Construct({\n 'name': Product.name,\n 'url': apply_(url_for_product, args=[Product.id, Product.name]),\n 'image_url': if_(\n Image.id,\n then_=apply_(url_for_image, args=[Image.id, Image.name, 100, 100]),\n else_=None,\n ),\n })\n\nUsage:\n\n.. code-block:: python\n\n >>> product = (\n ... session.query(product_struct)\n ... .outerjoin(Product.image)\n ... .first()\n ... )\n ...\n >>> product.name\n u'Foo product'\n >>> product.url\n '/p1-foo-product.html'\n >>> product.image_url\n '//images.example.st/1-100x100-foo.jpg'\n\nAdvantages:\n\n- you're loading only what you need, no extra network traffic, no need to\n defer/undefer columns;\n- ``url_for_product`` and ``url_for_image`` functions can't add complexity,\n because they are forced to define all needed columns as arguments;\n- you're working with precomputed values (urls in this example).\n\nDisadvantages:\n\n- code of functions is hard to refactor and reuse, because you should specify or\n pass all the arguments every time;\n- you should be careful with joins, because if you wouldn't specify them\n explicitly, `SQLAlchemy` will produce cartesian product of the tables\n (``SELECT ... FROM product, image WHERE ...``), which will return wrong\n results and hurt your performance.\n\nTo address first disadvantage, `SQLConstruct` provides ``define`` decorator,\nwhich gives you ability to define hybrid functions to use them in different\nways:\n\n.. code-block:: python\n\n from sqlconstruct import define\n\n @define\n def url_for_product(product):\n def body(product_id, product_name):\n return '/p{id}-{name}.html'.format(\n id=product_id,\n name=slugify(product_name),\n )\n return body, [product.id, product.name]\n\n @define\n def url_for_image(image, width, height):\n def body(image_id, image_name, width, height):\n return '//images.example.st/{id}-{width}x{height}-{name}'.format(\n id=image_id,\n width=width,\n height=height,\n name=slugify(image_name),\n )\n return body, [image.id, image.name, width, height]\n\nNow these functions can be used in these ways:\n\n.. code-block:: python\n\n >>> product = session.query(Product).first()\n >>> url_for_product(product) # objective style\n '/p1-foo-product.html'\n >>> url_for_product.defn(Product) # apply_ declaration\n \n >>> url_for_product.func(product.id, product.name) # functional style\n '/p1-foo-product.html'\n\nModified final ``Construct`` definition:\n\n.. code-block:: python\n\n product_struct = Construct({\n 'name': Product.name,\n 'url': url_for_product.defn(Product),\n 'image_url': if_(\n Image.id,\n then_=url_for_image.defn(Image, 100, 100),\n else_=None,\n ),\n })\n\nInstallation\n============\n\nTo install `SQLConstruct`, simply:\n\n.. code-block:: shell\n\n $ pip install sqlconstruct\n\nTested `Python` versions: 2.7, 3.4, PyPy.\n\nTested `SQLAlchemy` versions: 0.7, 0.8, 0.9, 1.0, 1.1.\n\nExamples above are using `SQLAlchemy` >= 0.9, if you are using older versions,\nyou will have to do next changes in your project configuration:\n\n.. code-block:: python\n\n from sqlconstruct import QueryMixin\n from sqlalchemy.orm.query import Query as BaseQuery\n\n class Query(QueryMixin, BaseQuery):\n pass\n\n session = Session(engine, query_cls=Query)\n\nFlask-SQLAlchemy:\n\n.. code-block:: python\n\n from flask.ext.sqlalchemy import SQLAlchemy\n\n db = SQLAlchemy(app, session_options={'query_cls': Query})\n\nor\n\n.. code-block:: python\n\n db = SQLAlchemy(session_options={'query_cls': Query})\n db.init_app(app)\n\nLicense\n=======\n\n`SQLConstruct` is distributed under the BSD license. See LICENSE.txt for more\ndetails.", "description_content_type": null, "docs_url": null, "download_url": "", "downloads": { "last_day": -1, "last_month": -1, "last_week": -1 }, "home_page": "https://github.com/vmagamedov/sqlconstruct", "keywords": "", "license": "BSD", "maintainer": "", "maintainer_email": "", "name": "SQLConstruct", "package_url": "https://pypi.org/project/SQLConstruct/", "platform": "", "project_url": "https://pypi.org/project/SQLConstruct/", "project_urls": { "Homepage": "https://github.com/vmagamedov/sqlconstruct" }, "release_url": "https://pypi.org/project/SQLConstruct/0.2.3/", "requires_dist": null, "requires_python": "", "summary": "Functional approach to query database using SQLAlchemy", "version": "0.2.3" }, "last_serial": 2611813, "releases": { "0.2.0": [ { "comment_text": "", "digests": { "md5": "bb0171320d55212a810a7eb2e31d5dbf", "sha256": "1c9ce8af5068157dd3ccf28bf3746dfb52ab11dc3a42c48cfe0d9f53c94730ef" }, "downloads": -1, "filename": "SQLConstruct-0.2.0.tar.gz", "has_sig": false, "md5_digest": "bb0171320d55212a810a7eb2e31d5dbf", "packagetype": "sdist", "python_version": "source", "requires_python": null, "size": 9744, "upload_time": "2014-10-07T12:34:24", "url": "https://files.pythonhosted.org/packages/7f/f2/1b76380d8d8df4d6f73c82be1ce7aa7119a2e5695abcb4565f652b89093c/SQLConstruct-0.2.0.tar.gz" } ], "0.2.1": [ { "comment_text": "", "digests": { "md5": "c663f6587226d97dbf097a6ce75c728f", "sha256": "1cee622fddb811de5194e4fbdb821fd31e74b540a51ca0f0c9f927901ade7d2b" }, "downloads": -1, "filename": "SQLConstruct-0.2.1.tar.gz", "has_sig": false, "md5_digest": "c663f6587226d97dbf097a6ce75c728f", "packagetype": "sdist", "python_version": "source", "requires_python": null, "size": 9773, "upload_time": "2015-08-13T13:33:35", "url": "https://files.pythonhosted.org/packages/41/7d/e9433b2eab4e258e8b183cf2c156b17245471991450d6761d3ecdec83f66/SQLConstruct-0.2.1.tar.gz" } ], "0.2.2": [ { "comment_text": "", "digests": { "md5": "4e58e83327d79b7ad4d730880b153fce", "sha256": "17a6415ea9349ce49e8ec5f259b98f4c5760ecfee5723f512b2fc7c39093eb4d" }, "downloads": -1, "filename": "SQLConstruct-0.2.2.tar.gz", "has_sig": false, "md5_digest": "4e58e83327d79b7ad4d730880b153fce", "packagetype": "sdist", "python_version": "source", "requires_python": null, "size": 9812, "upload_time": "2015-08-13T13:38:27", "url": "https://files.pythonhosted.org/packages/c4/d1/968f5da66722d82094ddb85548f14975d209e4e3349d44ac646db294d221/SQLConstruct-0.2.2.tar.gz" } ], "0.2.3": [ { "comment_text": "", "digests": { "md5": "080eabf49301088d4a0cc348788a4407", "sha256": "ae1cf72c04d80c9ed76f982abd90779ee9a8bd6f29f3611c09b24c0c8a9f6f28" }, "downloads": -1, "filename": "SQLConstruct-0.2.3.tar.gz", "has_sig": false, "md5_digest": "080eabf49301088d4a0cc348788a4407", "packagetype": "sdist", "python_version": "source", "requires_python": null, "size": 9773, "upload_time": "2017-02-01T15:12:43", "url": "https://files.pythonhosted.org/packages/b2/87/36f5bac7a384b4210a007f622686cbb99acd902d94713e9285faa4505729/SQLConstruct-0.2.3.tar.gz" } ] }, "urls": [ { "comment_text": "", "digests": { "md5": "080eabf49301088d4a0cc348788a4407", "sha256": "ae1cf72c04d80c9ed76f982abd90779ee9a8bd6f29f3611c09b24c0c8a9f6f28" }, "downloads": -1, "filename": "SQLConstruct-0.2.3.tar.gz", "has_sig": false, "md5_digest": "080eabf49301088d4a0cc348788a4407", "packagetype": "sdist", "python_version": "source", "requires_python": null, "size": 9773, "upload_time": "2017-02-01T15:12:43", "url": "https://files.pythonhosted.org/packages/b2/87/36f5bac7a384b4210a007f622686cbb99acd902d94713e9285faa4505729/SQLConstruct-0.2.3.tar.gz" } ] }