File: README.rst

package info (click to toggle)
anosql 1.0.1-1
  • links: PTS, VCS
  • area: main
  • in suites: bookworm, bullseye, sid
  • size: 256 kB
  • sloc: python: 633; makefile: 197; sql: 66
file content (216 lines) | stat: -rw-r--r-- 5,299 bytes parent folder | download
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
anosql
======

.. image:: https://badge.fury.io/py/anosql.svg
    :target: https://badge.fury.io/py/anosql

.. image:: http://readthedocs.org/projects/anosql/badge/?version=latest
    :target: http://anosql.readthedocs.io/en/latest/?badge=latest
    :alt: Documentation Status

.. image:: https://travis-ci.org/honza/anosql.svg?branch=master
    :target: https://travis-ci.org/honza/anosql

A Python library for using SQL

Inspired by the excellent `Yesql`_ library by Kris Jenkins.  In my mother
tongue, *ano* means *yes*.

If you are on python3.6+ or need ``anosql`` to work with ``asyncio`` based database drivers.
See the related project `aiosql <https://github.com/nackjicholson/aiosql>`_.

Installation
------------

::

  $ pip install anosql

Usage
-----

Basics
******

Given a ``queries.sql`` file:

.. code-block:: sql

  -- name: get-all-greetings
  -- Get all the greetings in the database
  SELECT * FROM greetings;

  -- name: $select-users
  -- Get all the users from the database,
  -- and return it as a dict
  SELECT * FROM USERS;

We can issue SQL queries, like so:

.. code-block:: python

    import anosql
    import psycopg2
    import sqlite3

    # PostgreSQL
    conn = psycopg2.connect('...')
    queries = anosql.from_path('queries.sql', 'psycopg2')

    # Or, Sqlite3...
    conn = sqlite3.connect('cool.db')
    queries = anosql.from_path('queries.sql', 'sqlite3)

    queries.get_all_greetings(conn)
    # => [(1, 'Hi')]

    queries.get_all_greetings.__doc__
    # => Get all the greetings in the database

    queries.get_all_greetings.sql
    # => SELECT * FROM greetings;

    queries.available_queries
    # => ['get_all_greetings']


Parameters
**********

Often, you want to change parts of the query dynamically, particularly values in
the ``WHERE`` clause.  You can use parameters to do this:

.. code-block:: sql

  -- name: get-greetings-for-language-and-length
  -- Get all the greetings in the database
  SELECT *
  FROM greetings
  WHERE lang = %s;

And they become positional parameters:

.. code-block:: python

  visitor_language = "en"
  queries.get_all_greetings(conn, visitor_language)



Named Parameters
****************

To make queries with many parameters more understandable and maintainable, you
can give the parameters names:

.. code-block:: sql

  -- name: get-greetings-for-language-and-length
  -- Get all the greetings in the database
  SELECT *
  FROM greetings
  WHERE lang = :lang
  AND len(greeting) <= :length_limit;

If you were writing a Postgresql query, you could also format the parameters as
``%s(lang)`` and ``%s(length_limit)``.

Then, call your queries like you would any Python function with named
parameters:

.. code-block:: python

  visitor_language = "en"

  greetings_for_texting = queries.get_all_greetings(
                conn, lang=visitor_language, length_limit=140)

Update/Insert/Delete
********************

In order to run ``UPDATE``, ``INSERT``, or ``DELETE`` statements, you need to
add ``!`` to the end of your query name.  Anosql will then execute it properly.
It will also return the number of affected rows.

Insert queries returning autogenerated values
*********************************************

If you want the auto-generated primary key to be returned after you run an
insert query, you can add ``<!`` to the end of your query name.

.. code-block:: sql

  -- name: create-user<!
  INSERT INTO person (name) VALUES (:name)

Adding custom query loaders.
****************************

Out of the box ``anosql`` supports SQLite and PostgreSQL via the stdlib ``sqlite3`` database driver
and ``psycopg2``. If you would like to extend ``anosql`` to communicate with another type of databases
you may create a driver adapeter class and register it with ``anosql.register_driver_adapter()``.

Driver adapters are duck-typed classes which adhere to the below interface. Looking at ``anosql/adapters`` package
is a good place to get started by looking at how the ``psycopg2`` and ``sqlite3`` adapters work.

To register a new loader::

    import anosql

    class MyDbAdapter():
        def process_sql(self, name, op_type, sql):
            pass

        def select(self, conn, sql, parameters):
            pass

        @contextmanager
        def select_cursor(self, conn, sql, parameters):
            pass

        def insert_update_delete(self, conn, sql, parameters):
            pass

        def insert_update_delete_many(self, conn, sql, parameters):
            pass

        def insert_returning(self, conn, sql, parameters):
            pass

        def execute_script(self, conn, sql):
            pass


    anosql.register_driver_adapter("mydb", MyDbAdapter)

    # To use make a connection to your db, and pass "mydb" as the db_type:
    import mydbdriver
    conn = mydbriver.connect("...")

    anosql.load_queries("path/to/sql/", "mydb")
    greetings = anosql.get_greetings(conn)

    conn.close()

If your adapter constructor takes arguments you can register a function which can build
your adapter instance::

    def adapter_factory():
        return MyDbAdapter("foo", 42)

    anosql.register_driver_adapter("mydb", adapter_factory)

Tests
-----

::

   $ pip install tox
   $ tox

License
-------

BSD, short and sweet

.. _Yesql: https://github.com/krisajenkins/yesql/