Skip to main content

Use Python with or without an ORM.

PyNoORM consists of several very loosely-coupled classes that facilitate the use of Python in a web or SQL context without having to rely on an ORM. Working with an ORM is entirely possible, in fact, it’s used with the Django ORM and SQLAlchemy in an application that interfaces with Oracle, Microsoft SQL Server and PostgreSQL all at the same time.

Focus is on:

  • simplicity for the user

  • support for databases that are not under “controlled” by the Python application or may be read-only for it.

  • performance

Class

Role

Binder

abstract SQL query binding

Linker

join objects together

TemplateGenerator (to be added)

generate Django Templates dynamically from query results, loosely inspired by Django Tables 2

The Binder class

A Binder support easier raw SQL by abstracting differences in the underlying database’s bind variable syntax and also substituting bind variables from a list of arguments, using dict, then attribute lookup.

Using native database binds also helps to protect you against SQL injection attacks.

supported: PostgreSQL, sqlite3, Oracle, MySQL, SQL Server

Basic Use

Simple sqlite3 example:

from pynoorm.binder import Binder
binder = Binder.factory("qmark")

query, parameters = binder("select * from orders where custid = %(custid)s", dict(custid="ACME"), binder)

query and parameters are now in the sqlite3/qmark format:

>>> print(query)
select * from orders where custid = ?
>>> print(parameters)
('ACME',)

Oracle, with multiple parameters

import cx_Oracle
binder_ora = Binder.factory(cx_Oracle.paramstyle)

#just for test... assign a custid for attribute lookup
binder_ora.custid = "AMAZON"

tqry = "select * from orders where custid = %(custid)s and has_shipped = %(shipped)s"
query, parameters = binder_ora(tqry, binder_ora, dict(custid="ACME", shipped=1))

>>> print(query)
select * from orders where custid = :custid and has_shipped = :shipped
>>> print(parameters)
{'shipped': 1, 'custid': 'AMAZON'}

SQL IN list criteria:

This allows binding of Python lists as standard SQL in ('foo','bar') expressions, but as a prepared statement.

It relies on using ‘l’, rather than ‘s’ as the format qualifier. Notice the %(custid)l below

from pynoorm.binder import Binder
binder = Binder.factory("qmark")

query, parameters = binder(
    "select * from orders where custid in (%(custid)l)"
    , dict(custid=["ACME","FOO","BAR"])
    )

Contents of query and parameters:

select * from orders where custid in (?, ?, ?)
('ACME', 'FOO', 'BAR')

And now with an empty list:

query, parameters = binder(
    """select *
    from orders
    where custid in (%(custid)l)
    and status=%(status)s"""
    , dict(custid=[], status="any")
    )

Contents of query and parameters:

select * from orders where custid in (NULL) and status=?
('any',)

Features

  • adjust query to support database parameter style

  • find and prepare bind parameters from *args.

The Linker class

A Linker allows you to join objects or dictionaries without the need for an ORM. You can think of it as performing parent-child linking, but it uses left-right instead as a more neutral terminology instead.

Basic use

Sample data, in dictionaries:

customers = [
    dict(id=1, xref=1),
    dict(id=2, xref=2),
]

orders = [
    dict(custid=1, xref=1, orderid=11),
    dict(custid=1, xref=1, orderid=12),
    dict(custid=2, xref=2, orderid=21),
    dict(custid=2, xref=2, orderid=22),
]

Create a linker, then a lookup dictionary for the left side. Finally, link the left and right side.

linker = Linker(key_left="id")
lookup = linker.dict_from_list(customers)
linker.link(lookup, orders, attrname_on_left="orders", key_right="custid")

The customers now have an orders list:

[ { 'id': 1,
    'orders': [ { 'custid': 1, 'orderid': 11, 'xref': 1},
                { 'custid': 1, 'orderid': 12, 'xref': 1}],
    'xref': 1},
  { 'id': 2,
    'orders': [ { 'custid': 2, 'orderid': 21, 'xref': 2},
                { 'custid': 2, 'orderid': 22, 'xref': 2}],
    'xref': 2}]

Features

  • supports objects or dictionaries

  • takes basic Python objects so can join across different databases, allowing for example tagging of objects in a read-only database

  • allows compound field keys and aliasing

  • orphans, on the left or the right, can be initialized with empty attribute values.

Note on Python 3.7 support:

3.7 tests run to success locally, but travis-ci does not support Python 3.7 yet. So expect builds to show “failing” 3.7, pending resolution of Travisissue485.

Credits

This package was created with Cookiecutter and the audreyr/cookiecutter-pypackage project template.

History

0.1.0 (2016-02-17)

  • First release on github.

0.1.1 (2016-02-22)

  • Registered on PyPI

0.2.0 (2016-04-12)

  • Added support for Python 3.3+ and MySQL

0.3.0 (2017-09-06)

  • Added SQL Server support

  • Added Linker class to support object cross-referencing

0.4.0 (2018-07-24)

  • Updating to Beta status

  • Optimized Linker class

  • Python list => SQL IN (xxx, yyy) functionality on Binder.

0.4.1 (2018-08-07)

  • adjusted for Python 3

0.4.2 (2019-01-10)

  • ran Black for code formatting

  • updated PyYaml to 4.2b4 to fix security vulnerability

0.4.3 (2019-01-10)

  • removed Python 3.7 from tox since that Python version is not supported yet by tox.

0.4.4 (2019-01-15)

  • adjusted list binding variable names from __xxx_000 to xxx_000__ because leading underscore are invalid under Oracle.

1.0.2 (2019-07-15)

  • Docs: added the D3.js Tree Linker example.

  • Code is stable and hasn’t needed fixes or API changes despite significant use cases.

Metadata

Release files for pynoorm 1.0.2

For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.

Source distribution (sdist)

Source distribution for pynoorm 1.0.2
File Size Uploaded Provenance Actions
pynoorm-1.0.2.tar.gz 30.3 kB Without provenance Details for pynoorm-1.0.2.tar.gz

Release files / pynoorm-1.0.2.tar.gz

Download URL pynoorm-1.0.2.tar.gz
Size 30.3 kB
Tags Source
SHA-256 checksum
How to use checksums
decfc78977aacf26c00c09ce436f9d5be7150b17bbfdf650823ed24451287e49
BLAKE2b-256 checksum
How to use checksums
09748da324b852519091b9143ab57590bc2e92cfbc51ff729c71b96a64f57747
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via twine/1.13.0 pkginfo/1.5.0.1 requests/2.22.0 setuptools/41.0.1 requests-toolbelt/0.9.1 tqdm/4.31.1 CPython/3.6.8

Security for pynoorm 1.0.2

Provenance

Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.

No provenance found

None of the files in this release were uploaded with provenance metadata.

A note on package security

PyPI is a repository for distributing packages, not a service for auditing them. While third-party security researchers and our staff do their best to detect and remove malicious packages, PyPI cannot guarantee the quality, security or suitability of any given package.

To learn more about protecting yourself from malicious actors, visit our package security documentation.

Anthropic, PBC Visionary sponsor Bloomberg Visionary sponsor Hudson River Trading Visionary sponsor Meta Visionary sponsor NVIDIA Visionary sponsor Microsoft Sustainability sponsor Depot Continuous Integration AWS Cloud computing and Security Sponsor Datadog Monitoring Fastly CDN Google Download Analytics Sentry Error logging StatusPage Status page