It is not possible to create an "OR" query comparing columns from two tables. This is due to the way where clauses are created programmatically:

# where clauses between models are separated by calls to join() or switch()
Blog.select().where(Q( ??? )).join(Entry).where( Q(???) )

With the new .filter() syntax, it is syntactically possible to create these types of queries:

Blog.filter(Q(title='my blog') | Q(entry__title='some special entry'))

But there is no underlying mechanism to generate the right SQL since where clauses are stored in a dictionary keyed by the model they pertain to.

Fixing this will most likely require significant rewriting.

I'm trying to run a RawQuery that creates a view and involves several tables. Is there a way to do this in peewee? It seemed like RawQuery only accounts for simple cases tied to a single table in the data model. Is there a way to do a raw SQL query independent of the data model?

Thanks!

If I create manually my table with a boolean type I can't insert a record using a field like BooleanField because it try to insert an integer.
Postgresql handles correctly bool() and stores a proper boolean field not like MySQL or Oracle which are casting in integer.

If I do something like this:

    class User(peewee.Model):
         email = peewee.CharField()
         class Meta:
             db_table = 'users'

That doesn't get picked up. It's because none such extra arguments are actually used when creating the _meta attribute. See here: https://github.com/coleifer/peewee/blob/master/peewee.py#L1098

This code:

from peewee import *

class Message(Model):
    author = CharField()
    body = TextField()

print Message.select().group_by('author').sql()

Throws this exception:

Traceback (most recent call last):
  File "test.py", line 7, in <module>
    print Message.select().group_by('author').sql()
  File "peewee.py", line 1216, in sql
    group_by = ', '.join(group_by)
TypeError: sequence item 0: expected string, tuple found

This part of SelectQuery.sql() seems responsible:

        if self.use_aliases():
            (...)
        else:
            group_by = [c[1] for c in self._group_by]

c[1] is a sequence of field names, not a single field name, so str.join() fails. The code used when use_aliases() returns True seems to work better.

To my knowledge, the sqlite3 module from the stdlib has the same API as the external pysqlite:
http://code.google.com/p/pysqlite

In case the sqlite3 module is absent, peewee could try to use pysqlite instead:

try:
    import sqlite3
except ImportError:
    try:
        from pysqlite2 import dbapi2 as sqlite3
    except ImportError:
        sqlite3 = None

We're currently using the (hex/char) representation of UUIDs for a database, to use as primary keys. It looks like the current implementation requires Primary Key (and thus the .save() method) be an integer, and this is further checked through the isinstance call during creation of a model. It would be really great if we could use a TextField, or any field, as primary key.

Hi,

It would be nice if something like that could work

import peewee


Class CoreModel(peewee.Model):

    id = peewee.PrimaryKeyField()
    created = peewee.DatetimeField()

    Class Meta:

         database = db


Class User(CoreModel):

    firstname = peewee.CharField()


Class Car(CoreModel):

    brand = peewee.CharField()

I don't want to duplicate my fields.
Also for the id when I'm using a global ID.

At least under MySQL, DecimalField ignores the precision parameters passed to the constructor, and the created table always carries the decimal field with precision 10,5.

Example code:

class Foo(peewee.Model):
    foo_field = DecimalField(15,2)

Foo.create_table()

Produces a table with foo_field type set to decimal(10,5)

Floats, by definition, should only take 4 bytes.

Currently:

• SQLite, real, 8 bytes
• MySQL, double precision, 8 bytes
• PostgreSQL, real, 4 bytes

Proposal:

• SQLite should continue using real (I believe there is no 4 bytes floating point datatype).
• MySQL should be using float, 4 bytes.

Hello Charles,

want to use 'select' in the following way.

<< User.select(['id', 'username', R('LOWER(SUBSTR( %s, 1, 1))', 'myFirstName' , 'first_letter')]) >>

but there is no implementation using values and alias together ?
Have you any idea how to fix it?

Kind regards,

Chris

Would like a way to do self-referential foreign keys.

I have an SQLite database which has a DateTimeField() with a few different datetime formats (not on purpose). I will probably fix my datetime values, but I thought I'd post this in case you feel peewee should handle this gracefully?

2011-11-15 21:53:59.759451+0000
2011-11-28 20:12:03
2009-01-01 (error)

http://paste.pocoo.org/show/aH7tITRfn9mhczxgWq3u/

Thanks for Peewee (and Flask-Peewee)!

Given the following models:

class ModelA(model):
    blah  = CharField()

class ModelB(model):
    modela = ForeignKeyField(ModelA)
    blah  = CharField()

Doing a query like this, gives results not quite expected:

modb = ModelB.get(id=1)
modas = ModelA.select().where(id__in=modb.modela)

The second query uses ModelB's primary key, instead of the modela_id column. The above example is sort of useless, as one could just use modb.modela, but when combined with Q():

modb = ModelB.get(id=1)
modas = ModelA.select().where(~Q(id__in=modb.modela))

The above would allow one to fetch all ModelA records that aren't related to ModelB.

Changing this would break the current behavior, but in my opinion it would make it behave more like the code actually reads.

What do you think?

Problems started after Commit: 62bd00b

Whole speak is about MySQL as backend

inside one of models I have method:

def getAbbrByCountryId(self, country_id):
return CC_map.select(
{CC_map: ['id AS cc_map_id, currency_id'],
Currencies: ['currency_abbr']}
).order_by('country_id').where(country_id=country_id
).join(Currencies, on='currency_id')


When you provided "smart select_related() behavior" I have no access to attributes created in code above.

Tried latest version and got problems with not assigning table link name to column names.

I get:
SELECT id AS cc_map_id, currency_id, t2.currency_abbr
FROM cc_map AS t1
INNER JOIN currencies AS t2 ON t1.currency_id = t2.id
WHERE t1.country_id =1
ORDER BY t1.country_id ASC

instead of

SELECT t1.id AS cc_map_id, t1.currency_id, t2.currency_abbr
FROM cc_map AS t1
INNER JOIN currencies AS t2 ON t1.currency_id = t2.id
WHERE t1.country_id =1
ORDER BY t1.country_id ASC

Tell If you need any additional info - I'll provide it.

P.S.
Thanks for your great work!

get() is not suitable in some cases, for example if we need get first row from complex select-where query, using join, sub-query etc (without exception)

Can you please add a first() or using get() name too method to QueryResultWrapper class?
The method should return None on empty resultset, otherwise return first row

Please check of mind:

class QueryResultWrapper(object):
# ...
    #def get(self): or which suitable
    def first(self):
        if self._result_cache:
            return self._result_cache[0]
        else:
            row = self.cursor.fetchone()
            if row:
                row_dict = self._row_to_dict(row, self.cursor)
                instance = self.model_from_rowset(self.model, row_dict)
                self._result_cache.append(instance)
                return instance
            else:
                self._populated = True
                return None

Table created in MySQL by create_table method:

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `test_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Key on 'id' column is duplicated (PRIMARY KEY and UNIQUE KEY).

Hi, to speed-up my query tasks, by default I have turned-off autocommit to False in db configuration,
means we need to call db.commit() to commit the transaction

Found now behavior doesn't flush (save) dirty objects on commit.
Again, in sql-alchemy we don't need explicit call object.save() as we know there's dirty objects that need to be save() automatically on commit.

try:
    othermodel.create(blablafield = 'blabla value')

    mymodel = MyModel.select().first() # hehe new stuff :D
    mymodel.somefield = 'edit the field value'  # dirty object
    # mymodel.save() # maybe explicitly required only on autocommit scheme

    db.commit()
except :
    db.rollback()

I'm playing with more than 4 models in one task, which changing the field value anywhere,
it's a bit difficult to catch which object should call save() on the end of procedure block since changes could be applied on several place of code block logic

I sometimes get the following Failure:

======================================================================
FAIL: test_filter_both_directions (tests.FilterQueryTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/Users/diggy/Documents/dev/peewee/tests.py", line 1638, in test_filter_both_directions
    self.assertSQLEqual(f.sql(), ('SELECT t1.* FROM entry AS t1 INNER JOIN entrytag AS t2 ON t1.pk = t2.entry_id\nINNER JOIN blog AS t3 ON t1.blog_id = t3.id WHERE t2.tag = ? AND t3.title = ?', ['t1', 'b1']))
  File "/Users/diggy/Documents/dev/peewee/tests.py", line 157, in assertSQLEqual
    self.assertEqual(lhs[0].replace('?', interpolation), rhs[0].replace('?', interpolation))
AssertionError: 'SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id\nINNER JOIN entrytag AS t3 ON t1.pk = t3.entry_id WHERE t2.title = ? AND t3.tag = ?' != 'SELECT t1.* FROM entry AS t1 INNER JOIN entrytag AS t2 ON t1.pk = t2.entry_id\nINNER JOIN blog AS t3 ON t1.blog_id = t3.id WHERE t2.tag = ? AND t3.title = ?'
----------------------------------------------------------------------

And always this one:

======================================================================
FAIL: test_count (tests.QueryTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/Users/diggy/Documents/dev/peewee/tests.py", line 495, in test_count
    self.assertEqual(count, 200)
AssertionError: 240 != 200

----------------------------------------------------------------------
Ran 56 tests in 4.145s

I'm already working on these - but these seme really ODD actually. There is something interesting going on at this

for blog in SelectQuery(Blog):
    print blog
    for i in xrange(20):
        self.create_entry(title='entry%d' % i, blog=blog)

Which outputs:

<Blog: a0>
<Blog: a1>
<Blog: a0>
<Blog: a1>
<Blog: a2>
<Blog: a3>
<Blog: a4>
<Blog: a5>
<Blog: a6>
<Blog: a7>
<Blog: a8>
<Blog: a9>

Invalid method signature in Database class:

Is:

def sequence_exists(self):
    raise NotImplementedError

Should be:

def sequence_exists(self, sequence_name):
    raise NotImplementedError

Stuffing a None into any field returns a False value when retrieved. For example, setting a TextField to None returns '' (empty string), BooleanField returns False, etc..

Dunno if this intentional, (real NULL support would be nice, though IIRC django discourages it). If it is, this sentence in the docs is misleading: "Conversion between python and the database is handled transparently, including the proper handling of None/NULL." As a user of the dbapi directly, I expected storing a None to return a NULL.

I may have missed this in the docs, but does Peewee support in memory SQLite databases? Thanks

Is peewee caching any results?
I would love to see caching mechanisms here like in https://bitbucket.org/jmoiron/johnny-cache.

Hi.
I use peewee 0.8.0 and MySQL5.5.14,SQLite3.7.5 on MacOSX Lion.

Is table name not allowed like MyTable1 and My1Table?
Numerical value is replaced '_' automatically.

e.g.
MyTable1.create_table() ---> mytable_
My1Table.create_table() ---> my_table

Is this specifications?

Thank you.

With the following I can access 'min' and 'max', but not 'price1'.

products = Product.select({
Product: ['*'],
Item: ['price1', Min('price1'), Max('price1')],
}).where(category_id=id).group_by('id').join(Item)

SQL:

('SELECT t1."id", t1."brand", t1."category_id", t1."name", t1."overview", t1."details", t2."price1", MIN(t2."price1") AS min, MAX(t2."price1") AS max FROM "products" AS t1 INNER JOIN "items" AS t2 ON t1."id" = t2."product_id" WHERE t1."category_id" = ? GROUP BY t1."id" ORDER BY t1."brand" ASC, t1."name" ASC', [u'29'])

A simple example:

Model class

class Entity(Model):
    num = IntegerField(default=0)

peewee seems no way for sql:

update Entity set num=num+1 where num>?

The orm generates this sql string

CREATE TABLE thethable (stat INTEGER NOT NULL, group VARCHAR(255) NOT NULL)

for a model that has stat an integerField and group a CharField.

sqlite3 then gives an operationalError: near "group" syntax error

probably since group is a keyword, but if we put quote marks around group, the statement passes.

a possible fix is to change the line 1803 (Field class, to_sql method) in peewee.py from return '%s %s' % (self.name, rendered) to
return '"%s" %s' % (self.name, rendered)

I don't know Django very well, but as far as I could tell, Django doesn't use the Meta class in models to select the database to use, and probably for a good reason?

I really like peewee, but I had to restructure some of my base application flow because all Peewee model classes have to have the database instance available immediately on class creation and in some global scope. The examples of course look fine, but for an application like mine that is larger and uses blueprints and all that, the Meta/inheritance system was a bit awkward to use.

do you think there's a way to just have a global bind variable? Like Elixir:

 elixir.metadata.bind = "sqlite:///db.sqlite"

Put simply, it would be nice to have a way to set the database object after the Model classes have been created.

Hi can you please add BETWEEN lookup type?

Thanks

Hi, I have the following model which have a datetime field with default value=datetime.now()

from datetime import datetime

class ATable(BaseModel):
    time = DateTimeField(default=datetime.now())
    customer = CharField()

Surprised when I create several records say:

ATable.create(customer='A')
# some delay
ATable.create(customer='B')
# some delay
ATable.create(customer='C')

The time (default) filled values was all the same: 2011-12-28 21:30:38.257 on all records
The time beeween record creation surelly in different time, at least microsecond and or second value

Perhaps a caching issue?

Thanks

Although there are no automated 'migrations' in peewee, it would be helpful to give users a way to get column sql and generate alter table queries (or with sqlite, since there's no drop column, maybe the temptable thing).

Currently you can only define IntegerField, FloatField and DecimalField.

What happens if you need to store a long (bigint)?
You have to use a Decimal without decimal places?
A new LongField (or BigIntField) should be added (bigint, 8 bytes).

Also, sometimes float is not enough.
For coherence, DoubleField should also be added (double precision, 8 bytes).

I believe both cases are extremely frequent.

Currently you are limited, more or less, to expressing select queries using the available abstractions - simple field queries, queries against column data (F objects), aggregations (min/max/count/etc), and aliases (foo as bar). To go beyond that requires using the RawQuery, which exposes a stripped down interface. Be nice to be able to add arbitrary elements to the select query to express things like "LOWER()" or other functions. Also be nice to add it to the where() clause to express things like CASE statements and the like.

The docs say that MyModel.create_table will noop if the table already exists, but currently it raises a sqlite3.OperationalError instead.

It looks like the Database class method is ready for the safe version, but there is no way to use that from models.

It'd be nice if you could pass the safe boolean to MyModel.create_table like you can to MyModel.drop_table

in my model, cascade for mysql not work, peewee generate create table sql:

('CREATE TABLE space (id integer AUTO_INCREMENT NOT NULL PRIMARY KEY, name VARCHAR(255));', None)
('CREATE UNIQUE INDEX space_id ON space(id);', None)
('CREATE TABLE page (id integer AUTO_INCREMENT NOT NULL PRIMARY KEY, space_id INTEGER REFERENCES space (id) ON DELETE CASCADE, name VARCHAR(255));', None)
('CREATE  INDEX page_space_id ON page(space_id);', None)
('CREATE UNIQUE INDEX page_id ON page(id);', None)

it looks like lack mysql FOREIGN KEY statements:

[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name, ...)
    [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
    [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

I don't know the name of my database until runtime. Is there any way to support this?

Thanks

There seems to be no way to declare a foreign_key which does not end with "_id", since the expected column name is attribute_name + '_id'. Or did I miss way to declare such a key?

Hi, when initializing (create) record; sometimes; mostly we have to leave other non indexed fields with default value or null for future fill.

May I suggest you to allow null by default, it's rare of important field to be not null

class Field(object):
    def __init__(self, null=True, db_index=False, unique=False, verbose_name=None,
                 help_text=None, *args, **kwargs):

null=True

Thanks

I'm building a small site using peewee and bottle.py.
And I got Mysql error 2014 and 2006 frequently either develop env(windows) or product env(linux).

I connect mysql before every request, but don't close it. Does this matter?

Mysql error 2014 is "Commands out of sync; you can't run this command now" and 2006 is "MySQL server has gone away".

I have the following which I copied from some sample code:

from flask_peewee.auth import BaseUser
import datetime
from peewee import *
from app import db

class User(db.Model, BaseUser):
    username = CharField(unique=True)
    password = CharField()
    email = CharField()
    join_date = DateTimeField(default=datetime.datetime.now)
    active = BooleanField(default=True)
    admin = BooleanField(default=False)

def __unicode__(self):
    return self.username

and the following config.py:

# Configuration for PostgreSQL
DATABASE = {
    'name': 'whatever',
    'engine': 'peewee.PostgresqlDatabase',
    'user': 'me',
    'password': 'mine'
} 

but when I run this against Postgresql on Fedora Core 15, I get:

Traceback (most recent call last):
File "run_mine.py", line 7, in <module>
  main.create_tables()
File "/home/watson/mine/main.py", line 15, in create_tables
  User.create_table(fail_silently=True)
File "/home/watson/mine/env/lib/python2.7/site-packages/peewee.py", line 1770, in create_table
  cls._meta.database.create_table(cls)
File "/home/watson/mine/env/lib/python2.7/site-packages/peewee.py", line 287, in create_table
  self.execute(query, commit=True)
File "/home/watson/mine/env/lib/python2.7/site-packages/peewee.py", line 252, in execute
  res = cursor.execute(sql, params or ())
psycopg2.ProgrammingError: syntax error at or near "user"
LINE 1: CREATE TABLE user (username VARCHAR(255) NOT NULL, admin SMA...

It turns out that postgresql defines a user table by default and apparently its a reserved word. It was already there with my postgresql user already created in it before I even ran my flask app.

mine=> select * from user;
current_user 
--------------
me
(1 row)

I'd recommend discouraging the use of "user" in any documentation relating to postgresql tables in peewee and also perhaps catching it and throwing a warning or error before it gets to the database driver. I tried making a simple change to my code to use users in place of user, but it was still blowing up in postgresql, though I'm not sure why. I'm open to advice here if you have any - I'm still intending to use peewee but Postgresql is a requirement.

Thanks!

As of 0.3.1, peewee requires both psycopg2 and sqlite3, even if you only intend to use one of them.

http://imgur.com/r/WTF/xmydq

Would be nice to be able to populate (or partially populate) instances of related objects when querying. Example would be querying a list of blog entries and wanting to additionally select the 'name' of the blog -- this can be done in 1 query with a join.

It shouldn't be too hard... related models can be queried passing a dictionary to Model.select() -- need to add the logic to the QueryResultWrapper. Looking at the returned rows and converting them into a dictionary may not work here if column names collide, so might need to list the columns and assign based on index.

Now that fields are exposed using descriptors it sohuld be possible to do "deferred" loading of field data as well, i.e. if it wasn't selected but then is asked for.

When you create a model that has default field values they get used when persisting to the db (and subsequently when being initialized from the db) but not when a new object is instantiated. A contrived example:

class Post(peewee.Model):
    comment_count = peewee.IntegerField(default=0)

post = Post()

post.comment_count # => None
post.save()
post.comment_count # => 0

I think it'd be more consistent if the defaults were applied to all objects of the class, not just ones that have been persisted or come from the database.

The change is quite simple, I believe. We just need to modify the Model class's __init__ method like so:

def __init__(self, *args, **kwargs):
    self.get_field_dict()
    for k, v in kwargs.items():
        setattr(self, k, v)

If this is something you'd be willing to merge, I'll happily do the work and submit a pull request.

Let me know, thanks.

FROM @23doors:

There doesn't seem to be any kind of for update functionality? This is really very important for simple row-lock operations.
What I mean is a functionality like this one:
http://docs.sqlalchemy.org/en/latest/core/expression_api.html#sqlalchemy.sql.expression.select
(for_update parameter)

In [6]: sq.sql_meta()
Out[6]: 
('SELECT t2."feature", SUM(t1."count") AS count FROM "featurecount" AS t1 INNER JOIN "feature" AS t2 ON t1."feature_id" = t2."id" GROUP BY t2."feature"',
 [],
 {'columns': [(models.FeatureCount, ('SUM', 'count', 'count')),
   (models.Feature, 'feature')],
  'graph': {models.FeatureCount: [(models.Feature, None, None)]}})

Should be:

'columns': [(models.Feature, 'feature'), (models.FeatureCount, ('SUM', 'count', 'count'))]

i want to save image in database by peewee.
but peewee has not BlobField yet.

I have a model where I do something simple:

class Whatever(db.Model):
    field1 = CharField()
    field2 = CharField()
    field3 = CharField()

but in my view I'd like the query to behave like a SQL select. That is, I only get the columns back that I asked for in the select:

whatevers = Whatever.select(Whatever: ['id', "field2"]}) # at this point id and field2 are populated
columns=whatever.model._meta.get_field_names() # this gets me all the fields, not the fields matching the previous select

What I'm wondering is if there's a way to get the columns to match whatever's requested in the query. Obviously, I could do a list comprehension or the like to reduce the column list to the result set, but I'm looking for a more architectural solution - one that's supported within the model. It seemed like there would be something like your ModelConverter's only or exclude parameters, but I couldn't find an example where the select and the column list were matched in this way.

The use case here is simple: I want to be able to generate tables of data in my templates like:

<table>
<thead>
<tr>
{% for column in columns %}
<td>{{ column }}</td>
{% endfor %}
</tr>
</thead>
<tbody>
{% for whatever in whatevers %}
<tr>
 {% for column in columns %}<td>{% if loop.index == 1: %}<a href="{{ url_for('detail', id=0) }}">{{ whatever[column] }}</a>{% else %}{{ whatever[column] }}{% endif %}</td>{% endfor %}
</tr>
{% endfor %}
</tbody>
</table>

Thanks!

I was doing a query using annotate() (with the default Count aggregation) to order by the count of related models.

The query itself worked well but calling query.count() failed with:

OperationalError: no such column: count

Further investigation revealed that SelectQuery.count() replaces the query with bare COUNT(x) but doesn't remove the ORDER BY clause which in my case created an invalid statement referencing an unknown column alias.

Here's how I fixed it:

    def count(self):
        tmp = self.query, self._limit, self._offset, self._order_by
        self._limit = self._offset = None
        self._order_by = []
        try:

            if self.use_aliases():
                self.query = 'COUNT(t1.%s)' % (self.model._meta.pk_name)
            else:
                self.query = 'COUNT(%s)' % (self.model._meta.pk_name)

            res = self.database.execute(*self.sql())

        finally:
            # restore
            self.query, self._limit, self._offset, self._order_by = tmp

        return (res.fetchone() or [0])[0]

Two other things that I changed are:

• check whether fetchone() returned None (which is the case if the table is empty) and return 0 in that case
• use try..finally block to make sure the state is restored in case of an error
• replace multiple tmp variables with one