This is a pre-production deployment of Warehouse, however changes made here WILL affect the production instance of PyPI.
Latest Version Dependencies status unknown Test status unknown Test coverage unknown
Project Description

SQL management for people that don’t mind SQL.

Motivation

I can write SQL faster than I can figure out the various library and ORM abstraction layers. Writing SQL directly in code is pretty disgusting. So I stole an idea from the Axamol SQL Library and along with a couple updates have created this library.

What is it?

First, we’ll consider some basic query definitions:

>>> queries = """
... name: create_roles_table
... type: update
... sql: |
...     CREATE  TABLE roles
...     (
...         scene   text,
...         name    text,
...         actor   text,
...         UNIQUE(scene, name)
...     )
... ---
... name: add_role
... type: update
... sql: |
...     INSERT
...     INTO    roles(scene, name, actor)
...     VALUES  (${scene}, ${name}, ${actor})
... ---
... name: list_roles
... sql: |
...     SELECT  name,
...             actor
...     FROM    roles
...     ORDER   BY
...             name ASC
... ---
... name: get_role_attr
... sql: |
...     SELECT  ${col | ident}
...     FROM    roles
...     WHERE   name = ${name}
... """
>>>

This is a YAML file that defines a couple queries for working with a simple table that lists scenes, roles, and actors. Generally, you’ll want to define your queries in a file in your Python package and use the static method sheba.Library.from_file(path) to load the queries.

Now, to use these in some code:

>>> import sheba
>>> conn = sheba.connect(queries, driver='sqlite3', args=(':memory:',))

Now that we have a connection, lets create the roles table:

>>> conn.u.create_roles_table()
-1

The -1 is due to DDL statements not returning row information. I could technically create a third class of statement types so avoid this, but in the not doctest world you can just ignore that return value.

Next we’ll insert a couple rows:

>>> conn.u.add_role(scene="Parrot Sketch", name="MR PRALINE", actor="John Cleese")
1
>>> conn.u.add_role(scene="Parrot Sketch", name="SHOP OWNER", actor="Machale Palin")
1
>>> conn.u.add_role(scene="Parrot Sketch", name="DEAD PARROT", actor="Fake Parrot")
1

Yep, it’s that simple. And lastly, we’ll list the roles in the table:

>>> for row in conn.q.list_roles():
...     print "Name: %(name)s Actor: %(actor)s" % row
...
Name: DEAD PARROT Actor: Fake Parrot
Name: MR PRALINE Actor: John Cleese
Name: SHOP OWNER Actor: Machale Palin

As they say, “Wicked awesome.”

Possible Query Attributes

The example above only used name, type, and sql. Only name and sql are absolutely required. The full list of attributes is:

  • name - The name used to reference this query from Python

  • desc - An explanation of the query for documentation.

  • type - query or update. Defaults to query.

  • dbs - A list of database names that this SQL will work with. The default

    value is None which is interpreted as “Use this query when no SQL has been defined for the current connections database connection.”

  • sql - The actual query. SQL is passed through Mako with access to

    any parameter names that were provide to the query. To bind a parameter in a query, simply print the value with standard Mako syntax like ${my_parameter_name}. Sheba will automatically replace it with the proper bind variable syntax and pass the supplied value onto the database connection.

Note

It may appear at first glance that the dynamic queries are writing parameter values directly into the SQL. They are NOT. The actual values passed along into the template context are UUID’s that will be replaced with bind parameters syntax appropriate to your database driver. If you’re asking yourself, “What if I do want the actual value?” you should go fix all of your SQL injection vulnerabilities.

Identifiers in SQL

If you’re feeling particularly precocious there’s support for dynamically setting identifier names in SQL statements.

Given the following YAML query:

name: get_role_attr
sql: |
    SELECT ${col | ident}
    FROM    roles
    WHERE   name = ${name}

You can then execute this query like such:

>>> for row in conn.q.get_role_attr({"col": "actor", "name": "MR PRALINE"}):
...     print "%(actor)s" % row
...
John Cleese

Specifying Connection Details

Instead of specifying your database connection details in code you can instead create a document at the top of your YAML file that lists the parameters to use when connection to the database. These settings are labeled with a name that can be used to refer to a particular config. For instance:

>>> yaml = """\
... name: dev
... type: connection
... driver: sqlite3
... args: [":memory:"]
... ---
... name: prod
... type: connection
... driver: sqlite3
... args: ["/path/to/prod.db"]
... ---
... name: create_table
... type: update
... sql: CREATE TABLE foo(a int primary key);
... ---
... name: insert_a
... type: update
... sql: INSERT INTO foo(a) values(3);
... ---
... name: get_a
... sql: SELECT a from foo where a = 3;
... """
>>> conn = sheba.connect(yaml, "dev")
>>> conn.u.create_table()
-1
>>> conn.u.insert_a()
1
>>> conn.q.get_a().fetchone()["a"]
3

License

Released under the MIT license. See the LICENSE file for more details.

Release History

Release History

0.0.7

This version

History Node

TODO: Figure out how to actually get changelog content.

Changelog content for this version goes here.

Donec et mollis dolor. Praesent et diam eget libero egestas mattis sit amet vitae augue. Nam tincidunt congue enim, ut porta lorem lacinia consectetur. Donec ut libero sed arcu vehicula ultricies a non tortor. Lorem ipsum dolor sit amet, consectetur adipiscing elit.

Show More

0.0.6

History Node

TODO: Figure out how to actually get changelog content.

Changelog content for this version goes here.

Donec et mollis dolor. Praesent et diam eget libero egestas mattis sit amet vitae augue. Nam tincidunt congue enim, ut porta lorem lacinia consectetur. Donec ut libero sed arcu vehicula ultricies a non tortor. Lorem ipsum dolor sit amet, consectetur adipiscing elit.

Show More

0.0.5

History Node

TODO: Figure out how to actually get changelog content.

Changelog content for this version goes here.

Donec et mollis dolor. Praesent et diam eget libero egestas mattis sit amet vitae augue. Nam tincidunt congue enim, ut porta lorem lacinia consectetur. Donec ut libero sed arcu vehicula ultricies a non tortor. Lorem ipsum dolor sit amet, consectetur adipiscing elit.

Show More

0.0.4

History Node

TODO: Figure out how to actually get changelog content.

Changelog content for this version goes here.

Donec et mollis dolor. Praesent et diam eget libero egestas mattis sit amet vitae augue. Nam tincidunt congue enim, ut porta lorem lacinia consectetur. Donec ut libero sed arcu vehicula ultricies a non tortor. Lorem ipsum dolor sit amet, consectetur adipiscing elit.

Show More

0.0.3

History Node

TODO: Figure out how to actually get changelog content.

Changelog content for this version goes here.

Donec et mollis dolor. Praesent et diam eget libero egestas mattis sit amet vitae augue. Nam tincidunt congue enim, ut porta lorem lacinia consectetur. Donec ut libero sed arcu vehicula ultricies a non tortor. Lorem ipsum dolor sit amet, consectetur adipiscing elit.

Show More

0.0.2

History Node

TODO: Figure out how to actually get changelog content.

Changelog content for this version goes here.

Donec et mollis dolor. Praesent et diam eget libero egestas mattis sit amet vitae augue. Nam tincidunt congue enim, ut porta lorem lacinia consectetur. Donec ut libero sed arcu vehicula ultricies a non tortor. Lorem ipsum dolor sit amet, consectetur adipiscing elit.

Show More

Download Files

Download Files

TODO: Brief introduction on what you do with files - including link to relevant help section.

File Name & Checksum SHA256 Checksum Help Version File Type Upload Date
sheba-0.0.7.tar.gz (22.3 kB) Copy SHA256 Checksum SHA256 Source Jul 22, 2010

Supported By

WebFaction WebFaction Technical Writing Elastic Elastic Search Pingdom Pingdom Monitoring Dyn Dyn DNS HPE HPE Development Sentry Sentry Error Logging CloudAMQP CloudAMQP RabbitMQ Heroku Heroku PaaS Kabu Creative Kabu Creative UX & Design Fastly Fastly CDN DigiCert DigiCert EV Certificate Rackspace Rackspace Cloud Servers DreamHost DreamHost Log Hosting