moskytw/mosql

Build SQL with native Python data structure smoothly.

mosql's Issues

Selective `SELECT` and `AS`

Is it possible to use AS in the select clause? Like

SELECT "person_id" AS "pid" FROM "person"

benchmark script

Fix the wrong doc caused by monkey patch

Some of the doc of mosql.util now is covered by mosql.mysql. It is because Sphinx imports all of the modules referred by doc, and the mosql.mysql is a monkey patch for mosql.util.

Solution A: Avoid referred mosql.mysql and such modules in doc. Manually create the doc.

Solution B: Don't use monkey patch anymore.

It should be resolved as soon as possible.

The test of SQLite should be split into doctest and example

as title

Migrate the Tests to Nose

as title

AS does not work in JOIN

select(
    table=(('some_table', 't1'),),
    joins=(
        join(('another_table', 't2'), {'t1.relation_id': 't2.id'})
    )
)

is turned into

SELECT * FROM `some_table` AS `t1`
INNER JOIN ['`another_table` AS `t2`'] ON `t1`.`relation_id` = `t2`.`id`

Happens because the join clause uses single_identifier, which can't handle iterables. A simple fix in Clause.format fixes this: (I removed some lines to save space)

def format(self, x):
    for formatter in self.formatters:
        x = formatter(x)
    if _is_iterable_not_str(x):
        x = ''.join(x)
    if self.hidden:
        return '%s' % x
    else:
        return '%s %s' % (self.prefix, x)

Not sure if it's comprehensive, but at least nothing is broken. There are lots of other ways to solve this, I believe.

0.9.1 does not work on Python 3

0.9

>>> from mosql.query import select
>>> print(select('foo'))
SELECT * FROM "foo"

0.9.1

>>> from mosql.query import select
>>> select('foo')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/lib/python3.4/site-packages/mosql/util.py", line 830, in __call__
    return self.stringify(*positional_values, **clause_args)
  File "/usr/lib/python3.4/site-packages/mosql/util.py", line 826, in stringify
    return self.format(clause_args)
  File "/usr/lib/python3.4/site-packages/mosql/util.py", line 812, in format
    return self.statement.format(clause_args)
  File "/usr/lib/python3.4/site-packages/mosql/util.py", line 763, in format
    pieces.append(clause.format(arg))
  File "/usr/lib/python3.4/site-packages/mosql/util.py", line 690, in format
    x = formatter(x)
  File "/usr/lib/python3.4/site-packages/mosql/util.py", line 209, in qualifier_wrapper
    return f(x)
  File "/usr/lib/python3.4/site-packages/mosql/util.py", line 321, in identifier
    elif s.find('.') == -1 and s.find(' ') == -1:
TypeError: Type str doesn't support the buffer API

Haven’t had time to look into it, but basically everything is failing because of the same error.

Expand ConnContext and make it as a new feature

It will be nice to expand the ConnContext in person_class.py and make it as a new feature .

Just a reminder. :)

Supporting standard SQL functions

Currently SQL functions need to be wrapped with mosql.util.raw. But the down side of raw is that everything in it needs to be escaped manually. Maybe some limited support should be provided, at least to standard SQL functions like AVG, COUNT, etc.

What I currently have in mind is to replace (taken from the documentation)

select('person', select=raw('count(*)'), group_by=('age', ))

with some simple syntax like

select('person', select=count('*'), group_by=('age',))

which should be pretty easy to provide.

Add doc for subquery and the `AS` feature.

Just a reminder. :)

Subquery support and `raw` objects

Currently there is no subquery support.

This wouldn't work:

select('person', select=('id',), where={'first_name': select('table', select=('last_name',))})

because the inner select will be escaped by the formatter. (Yes I know this one can be avoided by splitting it into two queries, but this is just an example.)

It gets worse if you want to use a subquery as a table: (Taken from this page)

select f.type, f.variety, f.price
from (
   select type, min(price) as minprice
   from fruits group by type
) as x inner join fruits as f on f.type = x.type and f.price = x.minprice;

This fails miserably: (I'm assuming there is an as clause as mentioned in #6 and a min aggregation as in #5):

x = select('fruits', select=('type', as(min('price'), 'minprice'))), group_by=('type',))
join_x = join(as(x, 'x'), {'fruits.type': 'x.type', 'fruits.price': 'x.minprice'})
select('fruits', select=('fruits.type', 'fruits.variety', 'fruits.price'), joins=(join_x,))

I think the main problem here is that Query instances render its __call__ arguments to a plain str, which is indistinguishable to normal arguments. Maybe this can be solved by making Statement.format return a raw object instead. This way, we can distinguish raw objects from user-supplied plain strs, and "support" subqueries simply by not formatting raw objects.

Opinions?

mosql.sqlite leaks docs

help not really help for insert, delete, select ,, etc.

>>> from mosql import query
>>> help(query.insert)

Help on Query in module mosql.util object:

class Query(__builtin__.object)
 |  It makes a partial :class:`Statement`.
 |  
 |  :param statement: a statement
 |  :type statement: :class:`Statement`
 |  :param positional_keys: the positional arguments accepted by :meth:`stringify`.
 |  :type positional_keys: sequence
 |  :param clause_args: the arguments of the clauses you want to predefine
 |  :type clause_args: dict
 |  
 |  .. versionadded :: 0.6
 |  
 |  Methods defined here:
 |  
 |  __call__(self, *positional_values, **clause_args)
 |      It is same as the :meth:`stringify`. It is for backward-compatibility, and not encourage to use.
 |  
 |  __init__(self, statement, positional_keys=None, clause_args=None)
 |  
 |  __repr__(self)
 |  
 |  breed(self, clause_args=None)
 |      It merges the `clause_args` from both this instance and the argument,
 |      and then create new :class:`Query` instance by that.
...

it will be helpful if

>>> help(select)

mosql.query.select(table=None, where=None, **clause_args)

It generates the SQL statement, SELECT ... .
The following usages generate the same SQL statement.

"Unloading" MySQL fixes

Consider this scenario:

from mosql.query import *

def select_normal():
    print select('question', where={'answer': 42})

def select_mysql():
    from mosql import mysql
    print select('question', where={'answer': 42})

select_normal()
select_mysql()

in which we're working with both MySQL and PostgreSQL. The functions work fine. However, there will be no way to "switch back" to PostgreSQL after select_mysql() since we can't "unload" mosql.mysql's modification.

Any preferred solution? I'm considering something like:

from mosql.query import *
from mosql import mysql   # Don't apply the fixes just yet

def select_normal():
    print select('question', where={'answer': 42})

def select_mysql():
    mysql.load()
    print select('question', where={'answer': 42})
    mysql.unload()

select_normal()
select_mysql()
select_normal()   # Now this will work

and

from mosql.query import *
from mosql import mysql   # Don't apply the fixes just yet

def select_normal():
    print select('question', where={'answer': 42})

def select_mysql():
    with mysql.apply():
        print select('question', where={'answer': 42})

select_normal()
select_mysql()
select_normal()   # Now this will work

The context manager approach seems nice, but I think the first one will still be needed sometimes.

And maybe another function like mysql.is_active()?

Multi-row INSERT

There doesn't seem to be a way to perform multi-row INSERTs. The following is invalid:

insert(
    'person',
    columns=('first_name', 'last_name'),
    values=(('Larry', 'Bernandez'), ('Melky', 'Mantle')))

because it generates

INSERT INTO "person" ("first_name", "last_name")
VALUES (('Larry', 'Bernandez'), ('Melky', 'Mantle'))

支援一些特殊語法

1. replace into (sqlite, mysql)
2. insert ignore (sqlite, mysql)
3. select distinct (sqlite, mysql, postgresql)

check 支援語法
http://www.dpriver.com/pp/sqlformat.htm

support sqlite?

does mosql support sqlite now?

Compatible with python 2 and 3

Lines from unicode : _quote_str, and isinstance(x, basestring) returnNameError` in python3 becase there is no such type anymore.

Here is a patch that make modules work in python3, in util.py after import datetime

import sys
version_3k = sys.version_info[0] == 3
if version_3k:
    str = str
    unicode = str
    basestring = (str, bytes)

Coercing values during chaining

This works

>>> print select('person', where={'id in': ('foo', 'bar')})
SELECT * FROM "person" WHERE "id" IN ('foo', 'bar')

But this doesn't

>>> print select('person', where={'id in': (1, 2, 3)})
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "mosql/util.py", line 819, in __call__
    return self.stringify(*positional_values, **clause_args)
  File "mosql/util.py", line 815, in stringify
    return self.format(clause_args)
  File "mosql/util.py", line 801, in format
    return self.statement.format(clause_args)
  File "mosql/util.py", line 752, in format
    pieces.append(clause.format(arg))
  File "mosql/util.py", line 679, in format
    x = formatter(x)
  File "mosql/util.py", line 359, in joiner_wrapper
    return f(x)
  File "mosql/util.py", line 519, in build_where
    return _build_condition(x, identifier, value)
  File "mosql/util.py", line 464, in _build_condition
    v = paren(concat_by_comma(v))
  File "mosql/util.py", line 359, in joiner_wrapper
    return f(x)
  File "mosql/util.py", line 383, in concat_by_comma
    return ', '.join(i)
TypeError: sequence item 0: expected string, int found

because join works only when the sequence contains str (and unicode) instances.

It seems pretty reasonable to coerce before concatenating values:

@joiner
def concat_by_comma(i):
    '''A joiner function which concats the iterable by ``,`` (comma).'''
    return ', '.join(str(v) for v in i)

concat_by_space, concat_by_or and concat_by_and are all similar, I believe. Maybe extract the logic into a decorator?

There may be some encoding issues, too. I would rather use unicode instead of str, but you will need to add some version detection if so.

None should be IS NULL in SQL?

table = "wants"
updated_at_field = "wants.updated_at"

select_sql = select(table, {updated_at_field: None})

print(select_sql)
print(db.session.execute(select_sql).rowcount)

Output:
SELECT * FROM wants WHERE wants.updated_at = null
0

select_sql = ("SELECT * FROM " + table
              + " WHERE " + updated_at_field + " IS NULL")
print(select_sql)
print(db.session.execute(select_sql).rowcount)

Output:
SELECT * FROM wants WHERE wants.updated_at IS NULL
37

Port MoSQL to Python 3

I don't know when I can make it. Just list it here as a reminder.

mosql.func leaks docs

BigQuery support

It may be a good idea to add BigQuery support in MoSQL.
Query Reference

Basically it follows SQL-92 standard (only select statement is supported).
I will work on this while my schedule is available.

The print statements cause the fails in doctest in Python 3

It don't affect the functionality, but it is indeed annoying to do test.

Improvement of the example table

Using key-value fields on SQL table for example may lead to incomprehensible demo of MoSQL power. Denormalization is also not a good example for newcomers to SQL village.

I'll suggest the normal usage:

detail_id  |  person_id  |  email  |  address

“Data for data's sake is a path to disaster.” *

• Stephane Faroult (2006). The Art of SQL. Retrieved from http://books.google.com

SQLite patch should stringify True and False to 1 and 0

SQLite does not have boolean literals, and both TRUE and FALSE will cause errors.

I’ll find time to implement this when I can.