Codebase list anosql / b8a1b989-2b7c-4d59-b999-2023b9664451/upstream/1.0.2+git20200909.1.903d82f
Import upstream version 1.0.2+git20200909.1.903d82f Debian Janitor 1 year, 4 months ago
24 changed file(s) with 736 addition(s) and 83 deletion(s). Raw diff Collapse all Expand all
+0
-11
.gitignore less more
0 *.pyc
1 docs/_build
2 venv
3 .venv/
4 .eggs
5 .cache
6 .tox
7 .python-version
8 anosql.egg-info
9 .idea/
10 scratch/
+0
-15
.travis.yml less more
0 language: python
1 python:
2 - "2.7"
3 - "3.5"
4 services:
5 - postgresql
6 addons:
7 postgresql: "10"
8 apt:
9 packages:
10 - postgresql-10
11 - postgresql-client-10
12 - postgresql-server-dev-10
13 install: "pip install tox"
14 script: tox
0 include README.md
0 include README.rst
1 include CHANGELOG
2 include LICENSE
3 include tox.ini
4 include test_requirements.txt
5 recursive-include docs *.rst Makefile conf.py make.bat
6 recursive-include tests *.csv *.sql
0 Metadata-Version: 2.1
1 Name: anosql
2 Version: 1.0.2
3 Summary: Easy SQL in Python
4 Home-page: https://github.com/honza/anosql
5 Author: Honza Pokorny
6 Author-email: me@honza.ca
7 Maintainer: Honza Pokorny
8 Maintainer-email: me@honza.ca
9 License-File: LICENSE
10
11 anosql
12 ======
13
14 **NOTICE**: This project is now deprecated in favor of `aiosql`_.
15
16 Unfortunately, I no longer have the time to devote to this project, and aiosql
17 is now a lot more popular. I don't think it makes sense to maintain both.
18 Open source ftw! Thanks for your hard work, `Will`_!
19
20 .. _aiosql: https://github.com/nackjicholson/aiosql
21 .. _Will: https://github.com/nackjicholson
22
23 .. image:: https://badge.fury.io/py/anosql.svg
24 :target: https://badge.fury.io/py/anosql
25 :alt: pypi package version
26
27 .. image:: http://readthedocs.org/projects/anosql/badge/?version=latest
28 :target: http://anosql.readthedocs.io/en/latest/?badge=latest
29 :alt: Documentation Status
30
31 .. image:: https://travis-ci.org/honza/anosql.svg?branch=master
32 :target: https://travis-ci.org/honza/anosql
33 :alt: Travid build status
34
35 A Python library for using SQL
36
37 Inspired by the excellent `Yesql`_ library by Kris Jenkins. In my mother
38 tongue, *ano* means *yes*.
39
40 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>`_.
41
42 Complete documentation is available at `Read The Docs <https://anosql.readthedocs.io/en/latest/>`_.
43
44 Installation
45 ------------
46
47 ::
48
49 $ pip install anosql
50
51 Usage
52 -----
53
54 Basics
55 ******
56
57 Given a ``queries.sql`` file:
58
59 .. code-block:: sql
60
61 -- name: get-all-greetings
62 -- Get all the greetings in the database
63 SELECT * FROM greetings;
64
65 -- name: select-users
66 -- Get all the users from the database,
67 -- and return it as a dict
68 SELECT * FROM USERS;
69
70 We can issue SQL queries, like so:
71
72 .. code-block:: python
73
74 import anosql
75 import psycopg2
76 import sqlite3
77
78 # PostgreSQL
79 conn = psycopg2.connect('...')
80 queries = anosql.from_path('queries.sql', 'psycopg2')
81
82 # Or, Sqlite3...
83 conn = sqlite3.connect('cool.db')
84 queries = anosql.from_path('queries.sql', 'sqlite3')
85
86 queries.get_all_greetings(conn)
87 # => [(1, 'en', 'Hi')]
88
89 queries.get_all_greetings.__doc__
90 # => Get all the greetings in the database
91
92 queries.get_all_greetings.sql
93 # => SELECT * FROM greetings;
94
95 queries.available_queries
96 # => ['get_all_greetings']
97
98
99 Parameters
100 **********
101
102 Often, you want to change parts of the query dynamically, particularly values in
103 the ``WHERE`` clause. You can use parameters to do this:
104
105 .. code-block:: sql
106
107 -- name: get-greetings-for-language
108 -- Get all the greetings in the database for given language
109 SELECT *
110 FROM greetings
111 WHERE lang = %s;
112
113 And they become positional parameters:
114
115 .. code-block:: python
116
117 visitor_language = "en"
118 queries.get_greetings_for_language(conn, visitor_language)
119 # => [(1, 'en', 'Hi')]
120
121
122 One Row Query
123 *************
124
125 Often, you would expect at most one row from a query, so that getting a list
126 is not convenient. Appending ``?`` to the query name makes it return either one
127 tuple if it returned one row, or ``None`` in other cases.
128
129 .. code-block:: sql
130
131 -- name: get-a-greeting?
132 -- Get a greeting based on its id
133 SELECT *
134 FROM greetings
135 WHERE id = %s;
136
137 Then a tuple is returned:
138
139 .. code-block:: python
140
141 queries.get_a_greeting(conn, 1)
142 # => (1, 'en', 'Hi')
143
144
145 Named Parameters
146 ****************
147
148 To make queries with many parameters more understandable and maintainable, you
149 can give the parameters names:
150
151 .. code-block:: sql
152
153 -- name: get-greetings-for-language-and-length
154 -- Get all the greetings in the database for given language and length
155 SELECT *
156 FROM greetings
157 WHERE lang = :lang
158 AND len(greeting) <= :length_limit;
159
160 If you were writing a Postgresql query, you could also format the parameters as
161 ``%s(lang)`` and ``%s(length_limit)``.
162
163 Then, call your queries like you would any Python function with named
164 parameters:
165
166 .. code-block:: python
167
168 visitor_language = "en"
169
170 greetings_for_texting = queries.get_greetings_for_language_and_length(
171 conn, lang=visitor_language, length_limit=140)
172
173 Update/Insert/Delete
174 ********************
175
176 In order to run ``UPDATE``, ``INSERT``, or ``DELETE`` statements, you need to
177 add ``!`` to the end of your query name. Anosql will then execute it properly.
178 It will also return the number of affected rows.
179
180 Insert queries returning autogenerated values
181 *********************************************
182
183 If you want the auto-generated primary key to be returned after you run an
184 insert query, you can add ``<!`` to the end of your query name.
185
186 .. code-block:: sql
187
188 -- name: create-user<!
189 INSERT INTO person (name) VALUES (:name)
190
191 Adding custom query loaders.
192 ****************************
193
194 Out of the box, ``anosql`` supports SQLite and PostgreSQL via the stdlib ``sqlite3`` database driver
195 and ``psycopg2``. If you would like to extend ``anosql`` to communicate with other types of databases,
196 you may create a driver adapter class and register it with ``anosql.core.register_driver_adapter()``.
197
198 Driver adapters are duck-typed classes which adhere to the below interface. Looking at ``anosql/adapters`` package
199 is a good place to get started by looking at how the ``psycopg2`` and ``sqlite3`` adapters work.
200
201 To register a new loader::
202
203 import anosql
204 import anosql.core
205
206 class MyDbAdapter():
207 def process_sql(self, name, op_type, sql):
208 pass
209
210 def select(self, conn, sql, parameters):
211 pass
212
213 @contextmanager
214 def select_cursor(self, conn, sql, parameters):
215 pass
216
217 def insert_update_delete(self, conn, sql, parameters):
218 pass
219
220 def insert_update_delete_many(self, conn, sql, parameters):
221 pass
222
223 def insert_returning(self, conn, sql, parameters):
224 pass
225
226 def execute_script(self, conn, sql):
227 pass
228
229
230 anosql.core.register_driver_adapter("mydb", MyDbAdapter)
231
232 # To use make a connection to your db, and pass "mydb" as the db_type:
233 import mydbdriver
234 conn = mydbriver.connect("...")
235
236 anosql.load_queries("path/to/sql/", "mydb")
237 greetings = anosql.get_greetings(conn)
238
239 conn.close()
240
241 If your adapter constructor takes arguments, you can register a function which can build
242 your adapter instance::
243
244 def adapter_factory():
245 return MyDbAdapter("foo", 42)
246
247 anosql.register_driver_adapter("mydb", adapter_factory)
248
249 Tests
250 -----
251
252 ::
253
254 $ pip install tox
255 $ tox
256
257 License
258 -------
259
260 BSD, short and sweet
261
262 .. _Yesql: https://github.com/krisajenkins/yesql/
00 anosql
11 ======
22
3 **NOTICE**: This project is now deprecated in favor of `aiosql`_.
4
5 Unfortunately, I no longer have the time to devote to this project, and aiosql
6 is now a lot more popular. I don't think it makes sense to maintain both.
7 Open source ftw! Thanks for your hard work, `Will`_!
8
9 .. _aiosql: https://github.com/nackjicholson/aiosql
10 .. _Will: https://github.com/nackjicholson
11
312 .. image:: https://badge.fury.io/py/anosql.svg
413 :target: https://badge.fury.io/py/anosql
14 :alt: pypi package version
515
616 .. image:: http://readthedocs.org/projects/anosql/badge/?version=latest
717 :target: http://anosql.readthedocs.io/en/latest/?badge=latest
919
1020 .. image:: https://travis-ci.org/honza/anosql.svg?branch=master
1121 :target: https://travis-ci.org/honza/anosql
22 :alt: Travid build status
1223
1324 A Python library for using SQL
1425
1526 Inspired by the excellent `Yesql`_ library by Kris Jenkins. In my mother
1627 tongue, *ano* means *yes*.
1728
18 If you are on python3.6+ or need ``anosql`` to work with ``asyncio`` based database drivers.
19 See the related project `aiosql <https://github.com/nackjicholson/aiosql>`_.
29 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>`_.
30
31 Complete documentation is available at `Read The Docs <https://anosql.readthedocs.io/en/latest/>`_.
2032
2133 Installation
2234 ------------
3951 -- Get all the greetings in the database
4052 SELECT * FROM greetings;
4153
42 -- name: $select-users
54 -- name: select-users
4355 -- Get all the users from the database,
4456 -- and return it as a dict
4557 SELECT * FROM USERS;
5870
5971 # Or, Sqlite3...
6072 conn = sqlite3.connect('cool.db')
61 queries = anosql.from_path('queries.sql', 'sqlite3)
73 queries = anosql.from_path('queries.sql', 'sqlite3')
6274
6375 queries.get_all_greetings(conn)
64 # => [(1, 'Hi')]
76 # => [(1, 'en', 'Hi')]
6577
6678 queries.get_all_greetings.__doc__
6779 # => Get all the greetings in the database
8193
8294 .. code-block:: sql
8395
84 -- name: get-greetings-for-language-and-length
85 -- Get all the greetings in the database
96 -- name: get-greetings-for-language
97 -- Get all the greetings in the database for given language
8698 SELECT *
8799 FROM greetings
88100 WHERE lang = %s;
92104 .. code-block:: python
93105
94106 visitor_language = "en"
95 queries.get_all_greetings(conn, visitor_language)
96
107 queries.get_greetings_for_language(conn, visitor_language)
108 # => [(1, 'en', 'Hi')]
109
110
111 One Row Query
112 *************
113
114 Often, you would expect at most one row from a query, so that getting a list
115 is not convenient. Appending ``?`` to the query name makes it return either one
116 tuple if it returned one row, or ``None`` in other cases.
117
118 .. code-block:: sql
119
120 -- name: get-a-greeting?
121 -- Get a greeting based on its id
122 SELECT *
123 FROM greetings
124 WHERE id = %s;
125
126 Then a tuple is returned:
127
128 .. code-block:: python
129
130 queries.get_a_greeting(conn, 1)
131 # => (1, 'en', 'Hi')
97132
98133
99134 Named Parameters
105140 .. code-block:: sql
106141
107142 -- name: get-greetings-for-language-and-length
108 -- Get all the greetings in the database
143 -- Get all the greetings in the database for given language and length
109144 SELECT *
110145 FROM greetings
111146 WHERE lang = :lang
121156
122157 visitor_language = "en"
123158
124 greetings_for_texting = queries.get_all_greetings(
159 greetings_for_texting = queries.get_greetings_for_language_and_length(
125160 conn, lang=visitor_language, length_limit=140)
126161
127162 Update/Insert/Delete
145180 Adding custom query loaders.
146181 ****************************
147182
148 Out of the box ``anosql`` supports SQLite and PostgreSQL via the stdlib ``sqlite3`` database driver
149 and ``psycopg2``. If you would like to extend ``anosql`` to communicate with another type of databases
150 you may create a driver adapeter class and register it with ``anosql.register_driver_adapter()``.
183 Out of the box, ``anosql`` supports SQLite and PostgreSQL via the stdlib ``sqlite3`` database driver
184 and ``psycopg2``. If you would like to extend ``anosql`` to communicate with other types of databases,
185 you may create a driver adapter class and register it with ``anosql.core.register_driver_adapter()``.
151186
152187 Driver adapters are duck-typed classes which adhere to the below interface. Looking at ``anosql/adapters`` package
153188 is a good place to get started by looking at how the ``psycopg2`` and ``sqlite3`` adapters work.
155190 To register a new loader::
156191
157192 import anosql
193 import anosql.core
158194
159195 class MyDbAdapter():
160196 def process_sql(self, name, op_type, sql):
180216 pass
181217
182218
183 anosql.register_driver_adapter("mydb", MyDbAdapter)
219 anosql.core.register_driver_adapter("mydb", MyDbAdapter)
184220
185221 # To use make a connection to your db, and pass "mydb" as the db_type:
186222 import mydbdriver
191227
192228 conn.close()
193229
194 If your adapter constructor takes arguments you can register a function which can build
230 If your adapter constructor takes arguments, you can register a function which can build
195231 your adapter instance::
196232
197233 def adapter_factory():
0 from anosql.core import from_path, from_str, SQLOperationType
1 from anosql.exceptions import SQLLoadException, SQLParseException
0 from .core import from_path, from_str, SQLOperationType
1 from .exceptions import SQLLoadException, SQLParseException
22
33 __all__ = ["from_path", "from_str", "SQLOperationType", "SQLLoadException", "SQLParseException"]
4
00 from contextlib import contextmanager
11
2 from anosql.patterns import var_pattern
2 from ..patterns import var_pattern
33
44
55 def replacer(match):
4040 cur.execute(sql, parameters)
4141
4242 @staticmethod
43 def insert_update_delete_many(conn, _query_name, sql, parmeters):
43 def insert_update_delete_many(conn, _query_name, sql, parameters):
4444 with conn.cursor() as cur:
45 cur.executemany(sql, parmeters)
45 cur.executemany(sql, parameters)
4646
4747 @staticmethod
4848 def insert_returning(conn, _query_name, sql, parameters):
00 import os
11
2 from anosql.adapters.psycopg2 import PsycoPG2Adapter
3 from anosql.adapters.sqlite3 import SQLite3DriverAdapter
4 from anosql.exceptions import SQLLoadException, SQLParseException
5 from anosql.patterns import (
2 from .adapters.psycopg2 import PsycoPG2Adapter
3 from .adapters.sqlite3 import SQLite3DriverAdapter
4 from .exceptions import SQLLoadException, SQLParseException
5 from .patterns import (
66 query_name_definition_pattern,
77 empty_pattern,
88 doc_comment_pattern,
9595 INSERT_UPDATE_DELETE_MANY = 2
9696 SCRIPT = 3
9797 SELECT = 4
98 SELECT_ONE_ROW = 5
9899
99100
100101 class Queries:
171172 return driver_adapter.insert_update_delete_many(conn, query_name, sql, *parameters)
172173 elif op_type == SQLOperationType.SCRIPT:
173174 return driver_adapter.execute_script(conn, sql)
175 elif op_type == SQLOperationType.SELECT_ONE_ROW:
176 res = driver_adapter.select(conn, query_name, sql, parameters)
177 return res[0] if len(res) == 1 else None
174178 elif op_type == SQLOperationType.SELECT:
175179 return driver_adapter.select(conn, query_name, sql, parameters)
176180 else:
211215 query_name = query_name[:-1]
212216 elif query_name.endswith("#"):
213217 op_type = SQLOperationType.SCRIPT
218 query_name = query_name[:-1]
219 elif query_name.endswith("?"):
220 op_type = SQLOperationType.SELECT_ONE_ROW
214221 query_name = query_name[:-1]
215222 else:
216223 op_type = SQLOperationType.SELECT
2222 var_pattern = re.compile(
2323 r'(?P<dblquote>"[^"]+")|'
2424 r"(?P<quote>\'[^\']+\')|"
25 r"(?P<lead>[^:]):(?P<var_name>[\w-]+)(?P<trail>[^:])"
25 r"(?P<lead>[^:]):(?P<var_name>[\w-]+)(?P<trail>[^:]?)"
2626 )
2727 """
2828 Pattern: Identifies variable definitions in SQL code.
0 Metadata-Version: 2.1
1 Name: anosql
2 Version: 1.0.2
3 Summary: Easy SQL in Python
4 Home-page: https://github.com/honza/anosql
5 Author: Honza Pokorny
6 Author-email: me@honza.ca
7 Maintainer: Honza Pokorny
8 Maintainer-email: me@honza.ca
9 License-File: LICENSE
10
11 anosql
12 ======
13
14 **NOTICE**: This project is now deprecated in favor of `aiosql`_.
15
16 Unfortunately, I no longer have the time to devote to this project, and aiosql
17 is now a lot more popular. I don't think it makes sense to maintain both.
18 Open source ftw! Thanks for your hard work, `Will`_!
19
20 .. _aiosql: https://github.com/nackjicholson/aiosql
21 .. _Will: https://github.com/nackjicholson
22
23 .. image:: https://badge.fury.io/py/anosql.svg
24 :target: https://badge.fury.io/py/anosql
25 :alt: pypi package version
26
27 .. image:: http://readthedocs.org/projects/anosql/badge/?version=latest
28 :target: http://anosql.readthedocs.io/en/latest/?badge=latest
29 :alt: Documentation Status
30
31 .. image:: https://travis-ci.org/honza/anosql.svg?branch=master
32 :target: https://travis-ci.org/honza/anosql
33 :alt: Travid build status
34
35 A Python library for using SQL
36
37 Inspired by the excellent `Yesql`_ library by Kris Jenkins. In my mother
38 tongue, *ano* means *yes*.
39
40 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>`_.
41
42 Complete documentation is available at `Read The Docs <https://anosql.readthedocs.io/en/latest/>`_.
43
44 Installation
45 ------------
46
47 ::
48
49 $ pip install anosql
50
51 Usage
52 -----
53
54 Basics
55 ******
56
57 Given a ``queries.sql`` file:
58
59 .. code-block:: sql
60
61 -- name: get-all-greetings
62 -- Get all the greetings in the database
63 SELECT * FROM greetings;
64
65 -- name: select-users
66 -- Get all the users from the database,
67 -- and return it as a dict
68 SELECT * FROM USERS;
69
70 We can issue SQL queries, like so:
71
72 .. code-block:: python
73
74 import anosql
75 import psycopg2
76 import sqlite3
77
78 # PostgreSQL
79 conn = psycopg2.connect('...')
80 queries = anosql.from_path('queries.sql', 'psycopg2')
81
82 # Or, Sqlite3...
83 conn = sqlite3.connect('cool.db')
84 queries = anosql.from_path('queries.sql', 'sqlite3')
85
86 queries.get_all_greetings(conn)
87 # => [(1, 'en', 'Hi')]
88
89 queries.get_all_greetings.__doc__
90 # => Get all the greetings in the database
91
92 queries.get_all_greetings.sql
93 # => SELECT * FROM greetings;
94
95 queries.available_queries
96 # => ['get_all_greetings']
97
98
99 Parameters
100 **********
101
102 Often, you want to change parts of the query dynamically, particularly values in
103 the ``WHERE`` clause. You can use parameters to do this:
104
105 .. code-block:: sql
106
107 -- name: get-greetings-for-language
108 -- Get all the greetings in the database for given language
109 SELECT *
110 FROM greetings
111 WHERE lang = %s;
112
113 And they become positional parameters:
114
115 .. code-block:: python
116
117 visitor_language = "en"
118 queries.get_greetings_for_language(conn, visitor_language)
119 # => [(1, 'en', 'Hi')]
120
121
122 One Row Query
123 *************
124
125 Often, you would expect at most one row from a query, so that getting a list
126 is not convenient. Appending ``?`` to the query name makes it return either one
127 tuple if it returned one row, or ``None`` in other cases.
128
129 .. code-block:: sql
130
131 -- name: get-a-greeting?
132 -- Get a greeting based on its id
133 SELECT *
134 FROM greetings
135 WHERE id = %s;
136
137 Then a tuple is returned:
138
139 .. code-block:: python
140
141 queries.get_a_greeting(conn, 1)
142 # => (1, 'en', 'Hi')
143
144
145 Named Parameters
146 ****************
147
148 To make queries with many parameters more understandable and maintainable, you
149 can give the parameters names:
150
151 .. code-block:: sql
152
153 -- name: get-greetings-for-language-and-length
154 -- Get all the greetings in the database for given language and length
155 SELECT *
156 FROM greetings
157 WHERE lang = :lang
158 AND len(greeting) <= :length_limit;
159
160 If you were writing a Postgresql query, you could also format the parameters as
161 ``%s(lang)`` and ``%s(length_limit)``.
162
163 Then, call your queries like you would any Python function with named
164 parameters:
165
166 .. code-block:: python
167
168 visitor_language = "en"
169
170 greetings_for_texting = queries.get_greetings_for_language_and_length(
171 conn, lang=visitor_language, length_limit=140)
172
173 Update/Insert/Delete
174 ********************
175
176 In order to run ``UPDATE``, ``INSERT``, or ``DELETE`` statements, you need to
177 add ``!`` to the end of your query name. Anosql will then execute it properly.
178 It will also return the number of affected rows.
179
180 Insert queries returning autogenerated values
181 *********************************************
182
183 If you want the auto-generated primary key to be returned after you run an
184 insert query, you can add ``<!`` to the end of your query name.
185
186 .. code-block:: sql
187
188 -- name: create-user<!
189 INSERT INTO person (name) VALUES (:name)
190
191 Adding custom query loaders.
192 ****************************
193
194 Out of the box, ``anosql`` supports SQLite and PostgreSQL via the stdlib ``sqlite3`` database driver
195 and ``psycopg2``. If you would like to extend ``anosql`` to communicate with other types of databases,
196 you may create a driver adapter class and register it with ``anosql.core.register_driver_adapter()``.
197
198 Driver adapters are duck-typed classes which adhere to the below interface. Looking at ``anosql/adapters`` package
199 is a good place to get started by looking at how the ``psycopg2`` and ``sqlite3`` adapters work.
200
201 To register a new loader::
202
203 import anosql
204 import anosql.core
205
206 class MyDbAdapter():
207 def process_sql(self, name, op_type, sql):
208 pass
209
210 def select(self, conn, sql, parameters):
211 pass
212
213 @contextmanager
214 def select_cursor(self, conn, sql, parameters):
215 pass
216
217 def insert_update_delete(self, conn, sql, parameters):
218 pass
219
220 def insert_update_delete_many(self, conn, sql, parameters):
221 pass
222
223 def insert_returning(self, conn, sql, parameters):
224 pass
225
226 def execute_script(self, conn, sql):
227 pass
228
229
230 anosql.core.register_driver_adapter("mydb", MyDbAdapter)
231
232 # To use make a connection to your db, and pass "mydb" as the db_type:
233 import mydbdriver
234 conn = mydbriver.connect("...")
235
236 anosql.load_queries("path/to/sql/", "mydb")
237 greetings = anosql.get_greetings(conn)
238
239 conn.close()
240
241 If your adapter constructor takes arguments, you can register a function which can build
242 your adapter instance::
243
244 def adapter_factory():
245 return MyDbAdapter("foo", 42)
246
247 anosql.register_driver_adapter("mydb", adapter_factory)
248
249 Tests
250 -----
251
252 ::
253
254 $ pip install tox
255 $ tox
256
257 License
258 -------
259
260 BSD, short and sweet
261
262 .. _Yesql: https://github.com/krisajenkins/yesql/
0 CHANGELOG
1 LICENSE
2 MANIFEST.in
3 README.rst
4 setup.cfg
5 setup.py
6 test_requirements.txt
7 tox.ini
8 anosql/__init__.py
9 anosql/core.py
10 anosql/exceptions.py
11 anosql/patterns.py
12 anosql.egg-info/PKG-INFO
13 anosql.egg-info/SOURCES.txt
14 anosql.egg-info/dependency_links.txt
15 anosql.egg-info/top_level.txt
16 anosql/adapters/__init__.py
17 anosql/adapters/psycopg2.py
18 anosql/adapters/sqlite3.py
19 docs/Makefile
20 docs/conf.py
21 docs/defining_queries.rst
22 docs/extending.rst
23 docs/getting_started.rst
24 docs/index.rst
25 docs/make.bat
26 docs/upgrading.rst
27 docs/source/anosql.adapters.psycopg2.rst
28 docs/source/anosql.adapters.rst
29 docs/source/anosql.adapters.sqlite3.rst
30 docs/source/anosql.core.rst
31 docs/source/anosql.exceptions.rst
32 docs/source/anosql.patterns.rst
33 docs/source/anosql.rst
34 docs/source/modules.rst
35 tests/__init__.py
36 tests/conftest.py
37 tests/test_psycopg2.py
38 tests/test_simple.py
39 tests/test_sqlite3.py
40 tests/blogdb/data/blogs_data.csv
41 tests/blogdb/data/users_data.csv
42 tests/blogdb/sql/blogs/blogs.sql
43 tests/blogdb/sql/blogs/blogs_pg.sql
44 tests/blogdb/sql/blogs/blogs_sqlite.sql
45 tests/blogdb/sql/users/users.sql
1818 # import os
1919 # import sys
2020 # sys.path.insert(0, os.path.abspath('.'))
21 import pkg_resources
2122
2223 # -- General configuration ------------------------------------------------
2324
5657 # built documents.
5758 #
5859 # The short X.Y version.
59 version = u'1.0.0'
60 version = pkg_resources.get_distribution('anosql').version
6061 # The full version, including alpha/beta/rc tags.
61 release = u'1.0.0'
62 release = version
6263
6364 # The language for content autogenerated by Sphinx. Refer to documentation
6465 # for a list of supported languages.
152153 # Add any paths that contain custom static files (such as style sheets) here,
153154 # relative to this directory. They are copied after the builtin static files,
154155 # so a file named "default.css" will overwrite the builtin "default.css".
155 html_static_path = ['_static']
156 html_static_path = []
156157
157158 # Add any extra paths that contain custom files (such as robots.txt or
158159 # .htaccess) here, relative to this directory. These files are copied
55 ======================
66
77 Name definitions are how ``anosql`` determines how to name the SQL code blocks which are loaded.
8 A query name definition is a normal SQL comment starting with "-- name:" and is followed by the
9 name of the query. You can use ``-`` or ``_`` in your query names, but the methods in python
10 will always be valid python names using underscores.
8 A query name definition is a normal SQL comment starting with "\-\- name:" and is followed by the
9 name of the query. You can use ``-`` or ``_`` in your query names, but the methods in Python
10 will always be valid Python names using underscores.
1111
1212 .. code-block:: sql
1313
1717 The above example when loaded by ``anosql.from_path`` will return an object with a
1818 ``.get_all_blogs(conn)`` method.
1919
20 Your SQL comments will be added to your methods as python documentation, and accessible by calling
20 Your SQL comments will be added to your methods as Python docstrings, and accessible by calling
2121 ``help()`` on them.
2222
2323 .. code-block:: sql
3232 queries = anosql.from_path("blogs.sql", "sqlite3")
3333 help(anosql.get_all_blogs)
3434
35 output
35 returns
3636
3737 .. code-block:: text
3838
5151 characters trailing it. This lack of operator is actually the most basic operator which performs
5252 SQL ``select`` statements and returns a list of rows. When writing an application you will often
5353 need to perform other operations besides selects, like inserts, deletes, and bulk opearations. The
54 operators detailed in this section let you declare in your SQL, how your code should be executed
54 operators detailed in this section let you declare in your SQL how your code should be executed
5555 by the database driver.
5656
5757 Insert/Update/Delete with ``!``
141141 :published
142142 )
143143
144 Applying this to a list of blogs in python::
144 Applying this to a list of blogs in Python::
145145
146146 queries = anosql.from_path("blogs.sql", "psycopg2")
147147 blogs = [
154154 Execute SQL script statements with ``#``
155155 ---------------------------------------------
156156
157 Executes some sql statements as a script. These methods don't do variable substitution, or return
158 any rows. An example usecase is using data definition statements like create table in order to
157 Executes some SQL statements as a script. These methods don't do variable substitution, or return
158 any rows. An example use case is using data definition statements like `create` table in order to
159159 setup your database.
160160
161161 .. code-block:: sql
33 Extending anosql
44 ################
55
6 .. _driver-adapters:
7
68 Driver Adapters
79 ---------------
810
9 Database driver adapters in ``anosql`` are a duck-typed class which follow the below interface.::
11 Database driver adapters in ``anosql`` are duck-typed classes which follow the below interface.::
1012
1113 class MyDbAdapter():
1214 def process_sql(self, name, op_type, sql):
3234 pass
3335
3436
35 anosql.register_driver_adapter("mydb", MyDbAdapter)
37 anosql.core.register_driver_adapter("mydb", MyDbAdapter)
3638
3739 If your adapter constructor takes arguments you can register a function which can build
3840 your adapter instance::
4042 def adapter_factory():
4143 return MyDbAdapter("foo", 42)
4244
43 anosql.register_driver_adapter("mydb", adapter_factory)
45 anosql.core.register_driver_adapter("mydb", adapter_factory)
4446
4547 Looking at the source of the builtin
4648 `adapters/ <https://github.com/honza/anosql/tree/master/anosql/adapters>`_ is a great place
2323 from worlds
2424 where world_name = :world_name;
2525
26 By specifying ``db_driver="sqlite3"`` we can use the python stdlib ``sqlite3`` driver to execute these sql queries and
26 By specifying ``db_driver="sqlite3"`` we can use the Python stdlib ``sqlite3`` driver to execute these SQL queries and
2727 get the results. We're also using the ``sqlite3.Row`` type for our records to make it easy to access our data via
2828 their column names rather than as tuple indices.
2929
77
88 .. image:: https://badge.fury.io/py/anosql.svg
99 :target: https://badge.fury.io/py/anosql
10 :alt: pypi package version
1011
1112 .. image:: http://readthedocs.org/projects/anosql/badge/?version=latest
1213 :target: http://anosql.readthedocs.io/en/latest/?badge=latest
14 :alt: Documentation status
1315
1416 .. image:: https://travis-ci.org/honza/anosql.svg?branch=master
1517 :target: https://travis-ci.org/honza/anosql
18 :alt: Travis build status
1619
1720 A Python library for using SQL
1821
1922 Inspired by the excellent `Yesql`_ library by Kris Jenkins. In my mother
2023 tongue, *ano* means *yes*.
2124
22 If you are on python3.6+ or need ``anosql`` to work with ``asyncio`` based database drivers.
23 See the related project `aiosql <https://github.com/nackjicholson/aiosql>`_.
25 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>`_.
2426
2527 Installation
2628 ------------
5759
5860 # Or, Sqlite3...
5961 conn = sqlite3.connect('cool.db')
60 queries = anosql.from_path('queries.sql', 'sqlite3)
62 queries = anosql.from_path('queries.sql', 'sqlite3')
6163
6264 queries.get_all_greetings(conn)
6365 # => [(1, 'Hi')]
7577 Parameters
7678 **********
7779
78 Often, you want to change parts of the query dynamically, particularly values in the WHERE clause.
80 Often, you want to change parts of the query dynamically, particularly values in the ``WHERE`` clause.
7981 You can use parameters to do this:
8082
8183 .. code-block:: sql
8284
83 -- name: get-greetings-for-language-and-length
84 -- Get all the greetings in the database
85 -- name: get-greetings-for-language
86 -- Get all the greetings in the database for a given language
8587 SELECT *
8688 FROM greetings
8789 WHERE lang = %s;
9193 .. code-block:: python
9294
9395 visitor_language = "en"
94 queries.get_all_greetings(conn, visitor_language)
96 queries.get_all_greetings_for_language(conn, visitor_language)
9597
9698
9799
102104
103105 .. code-block:: sql
104106
105 -- name: get-greetings-for-language-and-length
106 -- Get all the greetings in the database
107 -- name: get-greetings-for-language
108 -- Get all the greetings in the database for given language and length
107109 SELECT *
108110 FROM greetings
109111 WHERE lang = :lang
0 [bdist_wheel]
1 universal = 1
2
3 [egg_info]
4 tag_build =
5 tag_date = 0
6
44
55 setup(
66 name='anosql',
7 version='1.0.1',
7 version='1.0.2',
88 url='https://github.com/honza/anosql',
99 install_requires=[],
1010 description='Easy SQL in Python',
44 -- name: get-all-sorted
55 -- Get all user records sorted by username
66 select * from users order by username asc;
7
8 -- name: get-one?
9 -- Get one user based on its id
10 select username, firstname, lastname from users where userid = %s;
1919 firstname integer not null,
2020 lastname text not null
2121 );
22
22
2323 create table blogs (
2424 blogid integer not null primary key,
2525 userid integer not null,
7373 assert q.get_all_values(sqlite) == [(10, 11, 12)]
7474
7575
76 def test_one_row(sqlite):
77 _test_one_row = ("-- name: one-row?\n"
78 "SELECT 1, 'hello';\n\n"
79 "-- name: two-rows?\n"
80 "SELECT 1 UNION SELECT 2;\n")
81 q = anosql.from_str(_test_one_row, "sqlite3")
82 assert q.one_row(sqlite) == (1, 'hello')
83 assert q.two_rows(sqlite) is None
84
85
7686 def test_simple_query_pg(postgresql):
7787 _queries = ("-- name: create-some-table#\n"
7888 "-- testing insertion\n"
192202 q.insert_some_value(postgresql)
193203
194204 assert q.get_all_values(postgresql, a=1) == [(1, 2, 3)]
205
206
207 def test_without_trailing_semi_colon_pg():
208 """Make sure keywords ending queries are recognized even without
209 semi-colons.
210 """
211 _queries = ("-- name: get-by-a\n"
212 "SELECT a, b, c FROM foo WHERE a = :a\n")
213 q = anosql.from_str(_queries, "psycopg2")
214 assert q.get_by_a.sql == "SELECT a, b, c FROM foo WHERE a = %(a)s"
00 [tox]
1 envlist = py27, py35
1 envlist = py27, py35, py37, flake8, docs, check-manifest
22 skip_missing_interpreters = True
33
44 [testenv]
55 deps = -rtest_requirements.txt
6 commands = py.test tests
6 commands = {envpython} -m pytest {posargs:tests}
7
8 [testenv:flake8]
9 skip_install = true
10 deps = flake8
11 commands = {envpython} -m flake8 {posargs}
12
13 [flake8]
14 max-line-length=100
15
16 [testenv:docs]
17 deps =
18 sphinx
19 alabaster
20 commands = {envpython} -m sphinx.cmd.build -W -b html docs docs/_build/html
21
22 [testenv:check-manifest]
23 skip_install = true
24 deps =
25 check-manifest
26 commands = {envpython} -m check_manifest