honza / anosql Goto Github PK

Hi,

When you have a query such as:

SELECT *
FROM table_name
WHERE name = %s

and you use sqlite (the %s is replaced by a '?'), Then, a mere queries.foo(cnx, 'John') raises a ProgrammingError Incorrect number of bindings supplied. The current statement uses 1, and there are 0 supplied.

Damien G.

Hi,

When try to install the package from PyPi I've got this error :

$ pip install anosql
Downloading/unpacking anosql
  Downloading anosql-0.1.0.tar.gz
  Running setup.py (path:/tmp/pip-build-IkgjXx/anosql/setup.py) egg_info for package anosql
    Traceback (most recent call last):
      File "<string>", line 17, in <module>
      File "/tmp/pip-build-IkgjXx/anosql/setup.py", line 3, in <module>
        long_desc = open('README.md').read()
    IOError: [Errno 2] No such file or directory: 'README.md'
    Complete output from command python setup.py egg_info:
    Traceback (most recent call last):

  File "<string>", line 17, in <module>

  File "/tmp/pip-build-IkgjXx/anosql/setup.py", line 3, in <module>

    long_desc = open('README.md').read()

IOError: [Errno 2] No such file or directory: 'README.md'

The package need to be regenrated as the README.md file il already present in the repository.

Hi,

I am playing with anosql that I installed from pip. I like this library and got to discover it thanks to the YeSQL project for Clojure. I see the current tree implements support for named arguments in the queries, like :n. Which is pretty cool because it is compatible with psql variables:

psql --variable "n=10" -f foo.sql <dbname>

Given for example:

-- name: top-artists-by-album
-- Get the list of the N artists with the most albums
  select artist.name, count(*) as albums
    from           artist
         left join album using(artistid)
group by artist.name
order by albums desc
   limit :n;

You can test your queries interactively (and also use \set and \i commands) and then run your application again.

Release time?

I'd like to make Anosql easier to develop by adding unit tests. I'm writing this as a proposal to verify that you'd have the time and interest in merging these.

I'm thinking the pull requests would look something like:

• Add Holgers Peter's unit tests from pull request #3 .
• Add analogous tests for Postgres using testing.postgresql.
• Add his tox tests, also from pull request #3.

Once these pull requests are finished, maybe the next issue is removing the "very alpha" warning? 😺

In the github doc it is written that the update "[...] will also return the number of affected rows." but looking at the code, it does not seem the case.

Is there a simple way to retrieve it without using the psycopg2 cursor.execute() myself ?

When using the <! the query is no longer found on the Queries object

An example:
SQL

-- name: add_description<!
-- Insert a description if it is not all ready present
insert into info (description) select :description
  where not exists (select id from info where description = :description);

Code

import anosql
import psycopg2

conn = psycopg2.connect("...")
queries = anosql.load_queries('postgres', 'sql/error_demo.sql')
print(queries.add_description(conn, description='something'))

Results in..

Traceback (most recent call last):
  File "error-test.py", line 14, in <module>
    print(queries.add_description(conn, description='something'))
AttributeError: 'Queries' object has no attribute 'add_description'

Using just ! works fine

Versions:
python: 3.6
postgres: 10.0

I cloned the repo and updated tox to run using 3.6 and all tests passed

Issue: nackjicholson/aiosql#2
PR: nackjicholson/aiosql#3

Wanted to drop this here to let people in on what I've been adding over on aiosql for my work. I refactored a lot to separate the logic of query loading/parsing from the dynamic creation and binding of methods. I found that adding new features was difficult because these tasks were entangled. By separating them and introducing the QueryDatum as an intermediary my hope is that it will be simpler to add new features and directives to aiosql. One such feature was the record_class directive I added.

Generally I'm working on aiosql first because

1. I depend on it.
2. it has less compatibility restrictions .
3. I need the python3.7 and asyncio features.

I then backport new stuff to anosql if it makes sense because I don't want to abandon our py3.5 and py2.7 friends 😁

I made this issue in order to track getting some of the features into this project as well.

• ^ select one operator
• record_class: MyNamedTuple
• Separation of QueryLoader and Queries method binding.
• A simpler way to provide external driver adapters.
• Changes to exceptions

Got and error when importing anosql.

>>> import anosql
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/lib/python3.7/site-packages/anosql/__init__.py", line 1, in <module>
    from anosql.core import from_path, from_str, SQLOperationType
  File "/usr/local/lib/python3.7/site-packages/anosql/core.py", line 3, in <module>
    from anosql.adapters.psycopg2 import PsycoPG2Adapter
ModuleNotFoundError: No module named 'anosql.adapters'

Step to reproduce:

• install anosql using pip3 : pip3 install anosql
• open a python (v3.6.6) shell and try to import : import anosql

I got no errors downgrading to 0.3.1

I was looking at the anosql source and noticed the lines which choose the sql_type. I don't think I really understand that part. Could you provide an example of how the value after -- name: impacts how the sql gets executed and the results returned?

Another question. Is it possible (or okay) to execute sql with anosql which contains multiple statements separated by ;.

Hi,

I propose to add a attribute _query to the generated function attached to the Queries object. It can be useful to retrieve the underlying raw SQL query for a specific method.

An other solution is to add a dict attr to the Queris object where the key if the name of the function and the value the string SQL query.

Cheers,
Damien G.

Currently I can template only sql variables in my scripts. But I want to use something like
this:

-- name: select_all
-- Select all rows for fields from table
SELECT {fields}
FROM {table};

It will be nice if query will have a method for templating python variables. Thank you!

I looked through the TravisCI logs and noticed the following warning:

UserWarning: The psycopg2 wheel package will be renamed from release 2.8; in order to keep installing from binary please use "pip install psycopg2-binary" instead. For details see: http://initd.org/psycopg/docs/install.html#binary-install-from-pypi.

There is no simple way to check which version is available from the code, which is kind of anoying because the API changed (eg from_path vs load_queries…).

Providing anosql.__version__ following PEP396 would be nice.

using the following query :

SELECT creationtime::date, name
FROM question.questionnaires
ORDER BY creationtime DESC;

will give the following exception: TypeError: tuple indices must be integers or slices, not str

because anosql will internally change the sql to :

SELECT creationtime:%(date)s, name 
FROM question.questionnaires 
ORDER BY creationtime DESC;

Hey all,

@honza @AlecBrooks @steveYeah (you are people I've seen active in the repo lately)

I worked this weekend on a proof of concept library based on anosql which handles some things I thought might be useful. Here is the link if you'd like to look through it https://gitlab.com/willvaughn/aiosql

@honza I copied the License, and as it truly is a derived work based on your library you are the top line on the copyright.

Chiefly, here are the things I had in mind while working on it:

1. Out of the box support for asyncio based database drivers like aiosqlite and asyncpg. Without losing support for sync drivers like sqlite and postgresql. I tried to accomplish this by isolating the code specific to each driver into "QueryLoader" strategies.
2. Ability to load a hierarchy of file from nested directories. To namespace the queries object as a tree, i.e. queries.contacts.get_all().
3. Easy extension to accommodate custom database drivers. I wanted the ability to extend the library to mysql or mssql without necessarily having to add code to the mainline codebase.

I didn't feel like it was correct to do all of these things in mainline anosql because of compatibility. The asyncio stuff limits my library to supporting python3.6+ because of f-strings and async def functions. Time willing, please look through the library and feel free to ask me any questions you have.

Things to talk about:

• Is it possible / desirable to merge some or all of this work back to anosql as a major version bump?
• How interested are people in maintaining or extending anosql at this time?
• Maintaining both projects for separate needs, or breaking out reusable part they can share.

Cheers!

Hi, I would like to see tests also included in pypi tgz, so in terms of packaging (in my case openSUSE) it would allow to run the tests during the build.

The second benefit, would make the github tar.gz and pypi tar.gz identical.
Thanks for your consideration.

I love the concept of anosql but i think there is an issue with insert_returning function.

• i changed my pscopg2 connection cursor to dict and whenever i execute an insert returning sql it gives this error below:

user = db._model.in_acc(
File "/home/johnmba/.local/share/virtualenvs/Account-aQwD5Z7L/lib/python3.9/site-packages/anosql/core.py", line 168, in fn
return driver_adapter.insert_returning(conn, query_name, sql, parameters)
File "/home/johnmba/.local/share/virtualenvs/Account-aQwD5Z7L/lib/python3.9/site-packages/anosql/adapters/psycopg2.py", line 55, in insert_returning
return res[0] if len(res) == 1 else res
KeyError: 0

• tried looking into the package and tried returning just res.fetchone it worked but returned this below:

RealDictRow([('extid', '31681bd1-4539-44f2-b4fd-d469f54b4500')])

• the inserted record is not found in database

anosql is great for binding parameters, but what about identifiers?
psycopg2 has support for that, using format and sql.Identifier(...), so Python variables can be used to specify tables or columns.