akumoth/peewee-issues huggingface dataset:
Issue: Unexpected LIKE operations
Author: dmwyatt
State: closed
Created: 2013-05-01 18:10:20

The following LIKE query gets me nothing:

```
In [22]: movies =models.Movie.select().where(models.Movie.name % '%I%')         

In [23]: for m in movies:
   ....:     print m
   ....:     
```

Using ILIKE works:

```
In [26]: movies =models.Movie.select().where(models.Movie.name ** '%i%')        

In [27]: for m in movies:                                               
    print m
   ....:     
<Movie: 'Iron Man'>
<Movie: 'Wreck-It Ralph'>
```

Using the command line sqlite3 tool:

```
sqlite> SELECT * FROM movie WHERE name LIKE '%I%';                              
1|Iron Man
2|Wreck-It Ralph
sqlite> 
```

This is more likely my idiocy than a bug in peewee, but I can't see where I'm going wrong.  

I just pip installed peewee a couple hours ago, so I'm using whatever version is most recent.


I don't blame you for being a bit confused...  SQLite's `LIKE` operator is case-insensitive by default.  To get case-sensitive matching, peewee uses the SQLite `GLOB` operator instead, which uses "*" as wildcards.

So -- if you want case-insensitive matching, use ILIKE (peewee expresses this as `**`) with "%" wildcards.  If you want case-sensitive, use LIKE (peewee expresses this as `%`) with "*" wildcards.  Confusing, right?

Hopefully this will clarify things:

```
sqlite> create table movie (title varchar(255));
sqlite> insert into movie (title) values ('Iron Man');
sqlite> insert into movie (title) values ('Wreck-It Ralph');
sqlite> select * from movie where title like '%i%';
Iron Man
Wreck-It Ralph
sqlite> select * from movie where title like '%I%';
Iron Man
Wreck-It Ralph
sqlite> select * from movie where title glob '*i*';
sqlite> select * from movie where title glob '*I*';
Iron Man
Wreck-It Ralph
```

I will add a note to the documentation on this.


Added note: http://peewee.readthedocs.org/en/latest/peewee/querying.html#column-lookups


This behavior got me too (before I looked at the output, noticed it was using GLOB with SQLite, then saw the note in the docs). It seems quite surprising, and would mean if you change your code to use `field % 'whatever*'` to work with SQLite, then if you switch to PostgreSQL it'll break.

I think it'd be more cross-database compatible and less surprising if the `%` operator on SQLite changed the `%`'s in your match string to `*` as well as using `GLOB`, so you didn't have to worry about which database you were on. Thoughts?
Issue: OR operator in multiple where conditions
Author: niedbalski
State: closed
Created: 2013-04-29 16:08:32

Currently is not possible to specify the operator kind between multiple WHERE conditions, AND condition is assumed:

``` sql
WHERE ((((t2."kind" = ?) AND (t2."value" <= ?)) *OR* ((t2."kind" = ?) AND (t2."value" <= ?))) AND (t1."state" = ?))
```

Now is possible to specify the operator for each WHERE condition, using the op=(peewee.OP_OR, peewee.OP_AND)

``` python
q = Foo.select()

for i, val in conditions:
     q = q.where((Foo.i == val), op=peewee.OP_OR)
```


You can actually use python's `reduce` function to generate dynamic lists of OR-ed together clauses.

``` python

import operator
or_conditions = reduce(operator.or_, conditions)
q = q.where(or_conditions)
```


OK, But now you are forcing the AND operator on WHERE conditions. Why you cannot expose this as a parameter?


Yes, this was a design decision that I made early on that multiple calls to `.where()` would be `AND-ed` together.  That does not mean you cannot dynamically generate query trees that will `OR` things together, it just means you will use a different API than multiple calls to `.where()`.


I can see that your solution looks more compact but less self explanatory . Maybe is a good idea to reflect on the documentation that multiple .where() conditions will be AND-ed by default. I had to debug the code before to understand why this happened. 

Thanks you for your time!


Cheers, yeah I did document this but it's buried in a note: http://peewee.readthedocs.org/en/latest/peewee/api.html#Query.where

I will make it more clear and also present a way to "OR" parts together dynamically.
Issue: Integer primary keys are expected to become AUTO_INCREMENT?
Author: moraes
State: closed
Created: 2013-04-26 02:47:35

For the snippet:

```
class foo(peewee.Model):
    id = peewee.IntegerField(primary_key=True)

db.compiler().create_table(foo, False)
```

The SQL generated for a MySQL database was:

```
CREATE TABLE `foo` (`id` INTEGER NOT NULL PRIMARY KEY)
```

Should I expect `AUTO_INCREMENT` to be added to `id`? I may have misunderstood the docs.


I must use PrimaryKeyField. Dooh. Sorry for being so stupid.
Issue: Peewee with Google Cloud SQL
Author: moraes
State: closed
Created: 2013-04-26 01:44:30

For the record, here's a recipe to use Peewee with Google Cloud SQL on App Engine Python SDK. Cloud SQL uses MySql, so the only difference is the connection:

```
from google.appengine.api import rdbms
import peewee

class AppEngineDatabase(peewee.MySQLDatabase):
    def _connect(self, database, **kwargs):
        if 'instance' not in kwargs:
            raise peewee.ImproperlyConfigured('Missing "instance" keyword to connect to database')
        return rdbms.connect(database=database, **kwargs)
```

It works like a charm.


Hmm...I wonder where a good place would be to put this information?  Having not tested cloud sql myself I'm reluctant to add something like this to "playhouse", but it does seem like useful information to put somewhere...Maybe I'll add a new section to the docs.


http://peewee.readthedocs.org/en/latest/peewee/database.html#peewee-and-other-databases

Thanks @moraes 


Google recommends using MySQLdb over their rdms driver whenever possible. See this page of their documentation: https://developers.google.com/appengine/docs/python/cloud-sql/ Below is what I ended up doing to get peewee to work in both development and production. Along with the changes in my fork of peewee.

```
if (os.getenv('SERVER_SOFTWARE') and
        os.getenv('SERVER_SOFTWARE').startswith('Google App Engine/')):
    db = MySQLDatabase('<db_name>', unix_socket='/cloudsql/<instance_name>', user='root')
else:
    db = MySQLDatabase('<db_name>', user='user', passwd='password')
```
Issue: save field_dict len validation.
Author: niedbalski
State: closed
Created: 2013-04-24 18:43:14

Running this code:

> > > x = ModelInstance()
> > > x.save()  

Trigger this:

```
 1719     def execute_sql(self, sql, params=None, require_commit=True):
   1720         cursor = self.get_cursor()
-> 1721         res = cursor.execute(sql, params or ())
   1722         if require_commit and self.get_autocommit():
   1723             self.commit()

OperationalError: near ")": syntax error
```


I think the correct fix is to make the INSERT generation smarter, I've been aware of this issue for a while but it only crops up with models consisting of a single auto-incrementing primary key.  I'm not sure why anyone would have a model like that, so I haven't bothered to fix it yet.


Hello Coleifer,

My impression is that to have a forced incremental ID is not a good idea because you can have tables without primary keys.

My patch is just a cosmetic change that guarantees that an AttributeError is raised just in the case of no fields are modified before save the entity.


To clarify, the autoincrement id is not forced but is just the default.

I fixed the issue by making the insert statement generation a bit smarter, thanks for bringing this issue to my attention.


Super, thanks you for your work!.
Issue: Only use valid kwargs for update and insert
Author: tax
State: closed
Created: 2013-04-05 10:49:16

Hi Charles, thanks for creating this excellent lightweight project, it is super simple and fun to work with.

I ran in to a problem in a project when passing on a dict to the update query of a model this raised a KeyError because one of the keys in the dict was not a field in the model.
This could be by design, if not I hope you will merge this pull request.

Again thanks for all the hard work on this project.


@tax, thank you for your message and the PR, I'm glad you're liking using "peewee".

The KeyError is intentional -- I think its better to raise an exception than not insert/update something because the user made a typo.
Issue: Updating peewee class fields via dictionary
Author: henrikstroem
State: closed
Created: 2013-03-30 19:00:40

Feature request.

In Python, you can update object fields via `self.__dict__.update(dictionary)` - this saves a lot of code when reading in data from text files.

Peewee object fields can't be updated in this way, so it would be nice with a convenience method that let you hand a dictionary to a Model object, and have the object fields updated accordingly. 


Rather than implementing this as a part of peewee, feel free to subclass `Model` and implement it yourself.
Issue: Change default charset in MySql's create table statements
Author: ibraaaa
State: closed
Created: 2013-03-29 19:58:13

For MySql, when I try to insert text encoded in utf8 into a table, the text is scrambled. The issue is that the default charset for tables is latin1, to fix this I need to ALTER a table after its creation to change the charset. Providing such option from inside peewee will be helpful.


I can appreciate the confusion this problem may cause, but this is not within the scope of peewee which is just an ORM.

Check out http://dev.mysql.com/doc/refman/5.0/en/charset-database.html if you want to set the charset for your database.


When the peewee create table it sets the charset as latin1_swedish_ci. so when data insert to table it scramble the data.
so it need a way to change column charset


You can also:

```
db = MySQLDatabase(...)
db.execute_sql("SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;")
```

Beyond that I'm not familiar enough with MySQL to be able to advise you what parameters or settings to modify. Consult the docs or try stackoverflow if you are still stuck.
Issue: Mod function or escape 
Author: andmart
State: closed
Created: 2013-03-26 15:38:29

I can't do a query where have to recover only even numbers.

Something like that:

Number.select().where(Number.number % 2 == 0)

This way, peewee 'translates' % for GLOB.

So, is there a way to escape % operator or a peewee.fn.Mod function?


Unfortunately, peewee does not support modulo out of the box (because I ran out of operators to overload).

You can create your own operators, though...perhaps I should add this to the docs.  There are some cool examples in `playhouse.postgresql_ext` where I add operators for various hstore functions.  Here's how you do modulo and regexp:

``` python

from peewee import *
from peewee import Expr

def mod(lhs, rhs):
    return Expr(lhs, 'mod', rhs)

def regexp(lhs, rhs):
    return Expr(lhs, 're', rhs)

# tell sqlite what to do with these expressions
SqliteDatabase.register_ops({'mod': '%', 're': 'REGEXP'})

class User(Model):
    username = CharField()

# users whose username is all numbers
User.select().where(regexp(User.username, '[0-9]+')

# users with even ids
User.select().where(mod(User.id, 2) == 0)
```


Docs: http://peewee.readthedocs.org/en/latest/peewee/querying.html#adding-user-defined-operators
Issue: can't create a model that inherits from "dict"
Author: farialima
State: closed
Created: 2013-03-26 11:52:44

the following code:

```
class MyModel(Model, dict):
    class Meta:
        database = SqliteDatabase(':memory:')
    id = CharField(primary_key=True)
o = MyModel()
print repr(o.id)
```

will show:
`<peewee.CharField object at 0x1079ca5d0>`
instead of the expected:
`None`

The problem is that o is an empty dictionary "{}", which is a boolean false.
Workaround is to ensure that the object never evaluate to false, eg.:

```
class MyModel(Model, dict):
    def __init__(self, *k, **v):
        super(MyModel, self).__init__(*k, **v)
        self['default'] = ''                                                                                                                                                   
    class Meta:
        database = SqliteDatabase(':memory:')
    id = CharField(primary_key=True)
```

The correct solution is to replace 

```
class FieldDescriptor
    (...)
    def __get__(self, instance, instance_type=None):
    (...)
        if instance:
            return instance._data.get(self.att_name)
```

with:

```
       if instance != None:
           return instance._data.get(self.att_name)
```

and possibly in a few other places (I haven't checked).

It took me time to debug it -- the fact that many objects evaluate to boolean False is really a pain in Python (the only one ?) 
Thanks for this great library, in any case.


Ah, thanks -- I will clarify that if statement!


aren't there a few other places where a similar fix would be needed ?


Curious, how would you use peewee to inherit from dict, and then use it as a dict?

I tried farialima's model but standard dict features didn't work.

I have a need to use the DB as a dict, and am not seeing it :)


http://peewee.readthedocs.org/en/latest/peewee/playhouse.html#key-value-store
Issue: Primary key reset on save
Author: henrikstroem
State: closed
Created: 2013-03-24 08:02:43

In some situations, the Peewee will reset the (non-integer) primary key when saving an object. I have constructed this example to clarify:

```
#!/usr/bin/python

from peewee import *
import uuid

db = SqliteDatabase('./db/' + str(uuid.uuid4()) + '.db')

class A(Model):
    id = CharField(primary_key=True)
    def __init__(self):
        super(A, self).__init__()
        self.id = str(uuid.uuid4())
    class Meta:
        database = db

class B(A):
    name = CharField()    
    def __init__(self, name):
        super(B, self).__init__()
        self.name = name

A.create_table()
a = A()
print a.id
a.save(force_insert=True)
print a.id
print "--"    
B.create_table()
b = B(name='Test')
print b.id
b.save(force_insert=True)
print b.id
```

An example output:

```
$ ./pkey.py 
0bd49fa9-c5cc-40e7-aff7-24e0b17247cb
0bd49fa9-c5cc-40e7-aff7-24e0b17247cb
--
2fe23bac-4cb2-46a2-827a-8a1c6395e665
1
```

Now, the last line should not be 1, but rather 2fe... as the line above. The funny thing is, this, as the example shows, only happens to the child object, and only after the save operation. 


Primary keys are not being inherited correctly -- I will look into it and thank you for the excellent bug report!
Issue: Order of expressions matters when doing IN subqueries
Author: coryalder
State: closed
Created: 2013-03-21 23:23:19

Hey, I've found an odd little edge case

```
device = get_object_or_404(Device, Device.id == identifier)
my_messages = Message.select().join(Notification).join(Device).where(Device.id == device.id)
messages = Message.select().where((Message.id << my_messages) | (Message.query == "*"))
```

This query fails with the error `sqlite3.OperationalError: no such column: t2.query` but if you _swap_ the order of the expressions:

```
messages = Message.select().where((Message.query == "*") | (Message.id << my_messages))
```

That query works fine.

Similarly, if you try to write it as two separate queries:

```
my_messages = Message.select().join(Notification).join(Device).where(Device.id == device.id)
everyone_messages = Message.select().where(Message.query == "*")
messages = Message.select().where((Message.id << my_messages) | (Message.id << everyone_messages))
```

That'll also fail, with the similar error `sqlite3.OperationalError: no such column: t2.id`. Switching the order of expressions doesn't do anything in this case.

p.s. forgive me if this is the most newb-y way to write this type of query. I'm learnin' :) Can't seem to find a way to do a UNION in peewee, which would be my first choice.

---

In short form, my models are:

```
class Device(db.Model):

class Message(db.Model):
    query = CharField()

class Notification(db.Model):
    device = ForeignKeyField(Device, related_name="notifications", cascade = True)
    message = ForeignKeyField(Message, related_name='notifications', cascade = True)
```


Wow, thanks for reporting.  I'll try and get a look at this tonight.


Should be fixed in 92aec9da99f4fe8bf23ffed4cf9dc8b5971e4b4a


Confirmed!

Thanks so much
Issue: Support for multiprocessing connection or sessions handlers similar to SQL Alchemy Engine
Author: ghost
State: closed
Created: 2013-03-21 18:14:38

Looking for an easy way to implement multiprocessing with peewee. I'm running into locking issues because the Pwiz generated model or basemodel class does not support closing the database connection after each call. Any ideas?


Can you get into more detail?  What kind of issues are you seeing.


We were creating the DB connection object in the model.py class and then
importing it the same in the parent and child process. So  it wasn't a
connection issue but rather a shared object/namespace issue in Python with
multiprocessing. We were hitting a 'commands out of sync' error from MySQL.
We ended up just making a model for the parent and a model for the child
process. This is messy and not the proper way for a fix but it works.
Ideally we would like to instantiate the model class instead of importing
it and move the connection to the main class within model.  For instance:

#####
Import model as dbm

model_obj = dbm.MasterModel()
#MasterModel would contain the connection obj
user = model_obj.User.get()
#####

However we had limited success building that. It's likely an issue with my
understanding of class construction rather than an issue with Peewee.

On Friday, March 22, 2013, Charles Leifer wrote:

> Can you get into more detail? What kind of issues are you seeing.
> 
> —
> Reply to this email directly or view it on GitHubhttps://github.com/coleifer/peewee/issues/173#issuecomment-15281333
> .


Unfortunately I still am not clear on what exactly happens.  Can you give me some code to replicate it?  Are you using the multiprocessing library and passing model instances around, or are you just calling fork?

The way that peewee handles the DB connection is pretty straightforward.  It is stored in a threadlocal (or a dummy threadlocal) on the database instance.  You can open a connection explicitly by calling `db.connect()` or you can open one implicitly when you execute a query.


@zahlenofzahlen -- please update so I can resolve or close.


Closing -- please reopen if you can provide me a bit more info!
Issue: Changed the test_suite thingy in setup.py
Author: sYnfo
State: closed
Created: 2013-03-21 12:05:33

...which makes 'python setup.py test' work.


Thank you! I was wondering, could you please update the pypi package with these? I would like to make a rpm package out of this and that would greatly simplify things for me.


v2.0.9 pushed!


Thank you :)
Issue: connections pool support
Author: beanyoung
State: closed
Created: 2013-03-10 09:55:07

Is peewee going to support connections pool in the future?


Thanks for your question -- at this time I have no plans to include this as part of peewee.  Typically I've seen connection pooling done upstream, e.g. pgpool/pgbouncer.  To be completely honest, I have not done the research necessary to build a robust connection pool -- if at some point i did add this i'd probably put it in the playhouse module, though.
Issue: Model.prepared not called for side loaded .join records
Author: jhorman
State: closed
Created: 2013-03-06 20:57:19

I am using query.join(User) to enhance the performance of a set of queries. My model also defines a Model.prepared so I can preinitialize some virtual fields. It seems that when a record is loaded via the .join, .prepared isn't called. I ended up doing

```
def prepared(self):
    super(MyObject, self).prepared()
    self.user.prepared()
```

to account for it.

(hopefully not another non-issue)


I believe this is correct, if somewhat odd, behavior.  I'm going to mark this as wontfix for now.


To my understanding, the contract is that if you are going to populate a model, you will call "prepared" on it. The idea that sometimes a model I use will have prepared called, and sometimes not, sort of makes prepared broken/untrustworthy to use.


Yeah... you're right.  I'll look into it.
Issue: ORDER BY with multiple DESC fields broken for Postgres
Author: jhorman
State: closed
Created: 2013-03-01 21:43:46

If I try to do a 

```
query.order_by(Model.field.desc(), Model.field2.desc())
```

with a Postgres DB the generated SQL will look like

```
ORDER BY (t1.field DESC, t1.field2 DESC)
```

which is invalid SQL for Postgres (not sure on other DBs). Postgres (I think) treats that as the creation of a tuple to sort on, and then complains you can't have DESC in the creation of a tuple.

Removing the parens would fix this issue.


Thank you for pointing this out, I will address this


You know, I tried to replicate this issue and was not seeing the same problem... can you give any more specifics?

``` python

users = User.select().order_by(User.username.desc(), User.id.desc())
ipdb> users
<class 'tests.User'> SELECT t1."id", t1."username" FROM "users" AS t1 ORDER BY t1."username" DESC, t1."id" DESC []
```

If you have an example that is failing that might help me figure out where the problem is.


You're right. My example wasn't exactly what I was doing but I assumed it was the same. I am doing

```
users = User.select().order_by([User.username.desc(), User.id.desc()])
```

I am doing it this way b/c in my use case I am assembling a list of order bys.


I could do 

```
User.select().order_by(*[User.username.desc(), User.id.desc()])
```

to expand the array into the parameter list I guess.


FYI, I also in my journey tried

```
User.select().order_by(User.username.desc() & User.id.desc())
```

Which results in parens as well. 

This is the wrong way to do it anyway since it is joined with an AND.

```
ORDER BY (t1."username" DESC AND t1."id" DESC)
```


Yes, the examples you've provided can result in invalid SQL - peewee won't stop you from doing this, however I think the documentation is pretty clear:

http://peewee.readthedocs.org/en/latest/peewee/api.html#SelectQuery.order_by

I'm gonna go ahead and close this as it is working as I intended
Issue: transaction context manager does not raise the exception.
Author: rammie
State: closed
Created: 2013-02-22 20:44:52

The **exit** function in the context manager should reraise the exception so that it may be caught outside of the with statement rather than being eaten up.

```
def __exit__(self, exc_type, exc_val, exc_tb):
    if exc_type:
        self.db.rollback()
        self.db.set_autocommit(self._orig)
        return False # This is necessary for python to reraise the exception (it must be false not the implicit None). Otherwise, python will eat up the exception.
    else:
        self.db.commit()
        self.db.set_autocommit(self._orig)
```


Thank you :)
Issue: Add support for ordering by list of IDs
Author: Pacek
State: closed
Created: 2013-02-20 13:26:31

Would by nice if this worked:

```
list_of_ids = [1,2,3,4,5,6]
result = MyModel.select().where(MyModel.id << list_of_ids)
# result is now is same order as list_of_ids
```


If the list of ids is ordered, then you can simply do:

``` python

MyModel.select().where(MyModel.id << list_of_ids).order_by(MyModel.id)
```

If the ordering is arbitrary, then you could write a helper like this:

``` python

def sort_by_id(query, id_list):
    # create a mapping
    mapping = dict((model.id, model) for model in query)

    for id in id_list:
        yield mapping[id]
```


Thanks for this. I found dirty but DB driven solution that works in MySQL:

```
list_of_ids = [1,2,3,4,5,6]
result = MyModel.select().where(MyModel.id << list_of_ids).order_by(R("FIELD(t1.id, %s)" % ", ".join(map(str, list_of_ids))))
```


interesting.  another way you can do that is:

``` python

MyModel.select().where(MyModel.id << list_of_ids).order_by(
    fn.Field(MyModel.id, *[R(str(id)) for id in list_of_ids])
)
```