akumoth/peewee-issues huggingface dataset:
Issue: Problems with playhouse.db_url.connect objects when initialising database connection
Author: andy47
State: closed
Created: 2017-12-01 03:10:28

I'm trying to dynamically initialise my database connection (at run time) using connection strings. This works fine with PostgreSQL

```
>>> from playhouse.db_url import connect
>>> db = connect('postgres://user:password@localhost/database')
>>> import models
>>> models.db.init(db.database, **db.connect_kwargs)
```

Then any subsequent operations on the classes in the models module work fine.

But if I try the same thing with a Sqlite connection string, e.g.

```
...
>>> db = connect('sqlite:////Users/andy47/my_db.sqlite')
...
```

Operations on the db object are fine (e.g. `execute_sql`) but when I use this in the `db.init` and then subsequently try and query a model class I get an error message;

`OperationalError: FATAL:  database "/Users/andy47/my_db.sqlite" does not exist`

I note that the documentation (http://docs.peewee-orm.com/en/latest/peewee/playhouse.html#db-url) only mentions databases in the current directory when discussing `sqlite` connection strings. But the connect works and provides a viable database connection. It's just when I use this to initialise my model database connection at run time that I get the error.

I've got a question at StackOverflow talking about this as well - https://stackoverflow.com/questions/47273956/run-time-initialisation-of-peewee-database-for-different-database-backends 

You might try removing one of the slashes on the sqlite filename.

It seems a bit wonky to use `connect` to instantiate a db object, only to throw it away and call `init` on a different instance. I get that you want to use the logic in `connect()` -- but it seems like if you can do it in one place you should be able to do it in the other (e.g. in `models.py`).

See also:

* http://docs.peewee-orm.com/en/latest/peewee/database.html#run-time-database-configuration
* http://docs.peewee-orm.com/en/latest/peewee/database.html#dynamically-defining-a-database

Thanks for the response. How do I do this with a connection string? I'm working in an environment with different database backends on different machines - just Sqlite and PostgreSQL at the moment but I'd like to be able to use others in the future. How can I do this so that I don't have to parse the database config to work out which type of database object to create? 

Using database URLs is perfect, as long as I can do lazy instantiation at run time.

> OperationalError: FATAL: database "/Users/andy47/my_db.sqlite" does not exist

Wait a sec...is that error occurring because in your models you have `db = PostgresqlDatabase(None)`?

Could have been, after your advice a couple of comments earlier I've changed this to a `Proxy()` object.

I'm not sure why you have closed this, I still can't do a lazy instantiation of a database connection based on a connection string.

```python

db = Proxy()

class BaseModel(Model):
    class Meta:
        database = db

# ... some time later ...
real_db = connect(connection_string)
db.initialize(real_db)
```
Issue: Cast json string as decimal, dates
Author: aitchnyu
State: closed
Created: 2017-11-28 08:14:00

In SQLAlchemy, I could do:

```
        session\
            .query(Model)\
            .filter(Model.json['num'].astext.cast(db.Numeric) >= Decimal(100))\
            .count()
```

I want to use the JSON field to store custom attributes in the form of decimal, dates and references to other objects (to do joins). Is there a similar cast method? Could I do joins with it?

Peewee 3.0a supports a `Cast` type and corresponding method.

```python
# untested, but should be about right
query = MyModel.select().where(Model.json['num'].cast('numeric') >= 100)
print query.count()
```

In Peewee 2.x you can:

```python
from playhouse.shortcuts import cast
query = MyModel.select().where(cast(Model.json['num'], 'numeric') >= 100)
print query.count()
```

And yes, you can use these approaches for joining.
Issue: Question: is there a best practice changing schema configuration depending on the model used?
Author: thxv3n0lvl
State: closed
Created: 2017-11-21 15:54:16

So I would like to know if there's a good way / approach to change schema configuration depending on the model used. I thought that `class Meta` was able to do that, but I can't find anything related to that on the docs, or probably not looking for it properly.

Any advice?
Best Regards!

Could you be more specific?

I just wanted to switch the schema using class Meta. I went through the docs and achieve it with this:
http://docs.peewee-orm.com/en/latest/peewee/models.html#model-options-and-table-metadata
Issue: Do not force primary_key value on returning clause
Author: gdraynz
State: closed
Created: 2017-11-17 10:00:57

Using Postgresql, when calling `.returning(*)` on an `InsertQuery`, the `RETURNING` clause was always set to my single primary_key.
I simply inverted the two possible returning clauses to ensure `.returning()` takes the priority.

Cheers, thanks!
Issue: Help wanted on querying 
Author: dashingspidy
State: closed
Created: 2017-11-16 22:13:49

I have User and Photo table and when i join them while querying, it duplicates User based on how many photo is associated with.

```
class User(BaseModel): 
      id = PrimaryKeyField()
      looking_for = CharField()
```

```
class Photo(BaseModel): 
      id = PrimaryKeyField()
      url = CharField()
      user = ForeignKeyField(User, related_name='photos')
```
Query:
`User.select(User, Photo).join(Photo).where(User.looking_for == 'params')`


This kind of question is not appropriate for the issue tracker. Please post to stackoverflow.

Because I'm a nice guy :) you're getting duplicate rows because you're joining on Photo...why are you joining on photo in the above query?
Issue: KeyError when fields specified in select()
Author: jspayne
State: closed
Created: 2017-11-15 18:30:29

Test_File_Options is a many-many foreign key table with references to TestFile and TestOptions.
```
q1 = Test_File_Options.select(TestFile.file_name).join(TestFile).switch(Test_File_Options).join(TestOption).where(TestOption.option == '-G').distinct().alias('q1a')
```
I can directly run the query generated by the above, and get what I'd expect.

q1.get() results in `KeyError: <class 'compile_opts_db_mysql.Test_File_Options'>`

If I remove the `TestFile.file_name` from the select(), there is no error

[I'm trying to select the field here because later I'm trying to join q1 to a q2 with a different option based on file_name, and need to specify the join field]

The issue is you're selecting a column on a joined model, but not selecting anything from Test_File_Options.

You probably want to rewrite as:

```python
(TestFile
 .select(TestFile.filename)
 .join(Test_File_Options)
 .join(TestOption)
 .where(TestOption.option == '-G')
 .get())
```
Issue: Executing multiple statements in one raw query gives Commands out of sync
Author: nuclearspike
State: closed
Created: 2017-11-13 23:22:59

How do I run a query that has two statements that must be run together?
```
tr = TensorRaw.raw("""
        SET @x := 0; 
        SELECT *
        FROM (SELECT (@x:=@x+1) AS x, mt.* FROM tensor_raw mt ORDER BY servertime desc) t
        WHERE MOD(x, 5) = 0
        limit 100; 
        """).execute()
```
This throws:

peewee.ProgrammingError: (2014, "Commands out of sync; you can't run this command now")

I tried running them in one transaction but the second query returns no results that way.

Nevermind. I fixed it by running it in one transaction as two separate .raw() queries.
Issue: sqliteq: method is not supported during get_or_create
Author: gsemet
State: closed
Created: 2017-11-13 21:30:12

`get_or_create` raises an exception when the row does not exist when using sqliteq
```
except cls.DoesNotExist:
    try:
        params = dict((k, v) for k, v in kwargs.items()
                      if '__' not in k)
        params.update(defaults)
        with cls._meta.database.atomic():
            return cls.create(**params), True
```
The `cls._meta.database.atomic()` cannot works, because we have in `sqlite.py`:
```
atomic = transaction = savepoint = __unsupported__
```

Correct. The docs state this:

> The downside, however, is that you cannot issue write transactions that encompass multiple queries – all writes run in autocommit mode, essentially.

To prevent users from shooting themselves in the foot, transaction APIs are disabled.

The problem is you're mixing two paradigms for multi-threaded database access. SqliteQ is only suitable for certain classes of problem...specifically, those where writes can be issued in autocommit mode. To do get-or-create you really need a multi-statement transaction... something like SELECT ... FOR UPDATE, which Sqlite doesn't support. It *does* however support the non-standard INSERT OR REPLACE query, which is perfect for get-or-create, as long as you have a constraint in place.

So I'd suggest changing your get-or-create to an INSERT OR REPLACE query, enforced by a constraint. Or alternatively do not use SqliteQ.

ok. Which sqlite engine should I use with my asyncio program (only event loop access to the sqlite file)?

Oh brother... asyncio is a garbage fire. I have no idea, but definitely not sqliteq.

why so ?
Issue: Provide an enumeration for valid on_delete values.
Author: conqp
State: closed
Created: 2017-11-13 13:15:57

Currently, the `on_delete` keyword argument of `ForeignKeyField` takes arbitrary strings.
The valid values are not documented in the [API reference](http://docs.peewee-orm.com/en/latest/peewee/api.html?highlight=cascade#ForeignKeyField) - just one example is given.
Assuming that all [options supported by MySQL](https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html) work, I request adding the following `enum.Enum` to peewee to be used alternatively to provide the field with the respective option.
```
class ForeignKeyConstraint(Enum):
    """Valid constraints for ForeignKeyFields."""

    RESTRICT = 'RESTRICT'
    CASCADE = 'CASCADE'
    SET_NULL = 'SET NULL'
    NO_ACTION = 'NO ACTION'
    SET_DEFAULT = 'SET DEFAULT'

    def __str__(self):
        return self.value

```

Enum is a python 3 thing, using it in 2.x requires a separate library if I'm not mistaken. Similarly, I prefer not to hard-code the list of supported actions since there may be some variance between options between databases (?).
Issue: Setting MySQL storage engine
Author: tomvlk
State: closed
Created: 2017-11-07 09:38:14

I wonder if it's already possible to set the MySQL storage engine for tables in a model or anywhere so it kinda works with ``create_tables`` method.
I ask this because of the limitation when the default storage engine is set to MyISAM and when you are creating tables in a utf8mb4_unicode_ci collation, it will complain about varchar indexes over the limit. I have to advice users to change their default storage engine right now, but it would be better to just create the tables with the storage engine given as a parameter.

https://github.com/coleifer/peewee/issues/178#issuecomment-232955141
Issue: prevent related attribute assignement
Author: scilo7
State: closed
Created: 2017-11-06 14:45:03

Coming from sqlalchemy, it's not obvious that related attributes shouldn't be assigned.

```python
class User(Model):
    name = CharField()

class Tweet(Model):            
    text = CharField()
    user = ForeignKeyField(User, null=True, related_name='tweets')

u = User(name='bob')
u.save()
u.tweets = [Tweet(text='baaa'), Tweet(text='boo')] # does not work at all 
```

With this code, u.tweets is mutated from peewee.SelectQuery to a list. Could the related attribute ("tweets") be handeled as a readonly property to prevent this ?



This isn't SQLAlchemy, home-skillet!

Good suggestion on making read-only :+1: 

So, I just prototyped the change and realized I can't enforce making back-references read-only because under some conditions, when resolving a query, Peewee may assign a list to the backref. For example when calling `dict_to_model`, it's possible to end up assigning a list to the back-reference. Similar goes for `prefetch`.
Issue: soundex postgres
Author: ra-esmith
State: closed
Created: 2017-11-06 01:25:23

Hello,

Rather than use contains in my where clause I would like to use postgres soundex.  Does peewee support soundex?  What would be an example of the syntax.

Thanks,
Evan

I'm not sure exactly what you mean, but Peewee supports arbitrary function calls:

```
SomeModel.select().where(fn.soundex(SomeModel.column) == fn.soundex(search_string))
```
Issue: how to do cross database join
Author: hongweishang
State: closed
Created: 2017-10-31 11:27:22

dm.A.select(dm.A.id, cm.B.name).join(cm.B, on=(dm.A.type==cm.B.id))

<class 'data_models.TRepFlow'> SELECT `t3`.`id`, `t2`.`name` FROM `t_rep_flow` AS t1 INNER JOIN `t_sys_app` AS t2 ON (`t2`.`id` = `t1`.`itemtype`) []

question:   A and B are models. A and B is not in the same db. So, how to do cross db join?

能不能在不同数据库间join并不是peewee能决定的。比如MySQL，同一个服务的不同数据库间是可以join的，但是不同服务就不要想了。

更明确一点，你定义Model的时候，是用的同一个peewee.Database 实例还是不同的Database实例，这决定了你到底能不能在不同的数据库之间join

@yanghg-basefx  数据库用的是MYSQL，MYSQL本身是支持cross db join的， 用raw sql是没有问题的；
两个model分别是不同的database属性（_meta.database）。
看了下peewee源码，它确实不支持cross db join，即使用model.select().join()...这样构造出的sql语句在cross db join的情况下是错误的。
请问是否有peewee cross db join的方案？（不使用raw sql）

哦可能我理解错你的问题了。peewee默认想要用join的话必须有一个 
ForeignKeyField，如果没有的话在compile SQL语句的时候会报错。所以如果想要 
用M.select.join()你首先要在他们之间建立一个连接，哪怕是假的连接也行。

然后，如果你没有指定on条件的话，peewee会根据你的连接自动生成，类似于(ON 
M2.m1_id = M1.id)，所以你必须要指定一条一定为True的on条件。

根据这个原理，我们就可以伪造出一条只能用来CROSS JOIN的语句了。

class M1(Model):

     class Meta:
         database = MySQLDatabase(**M1_CFG)
         db_table ='m1'


class M2(Model):
     m1 = ForeignKeyField(M1,'m2s',db_column='id')

     class Meta:
         database = MySQLDatabase(**M2_CFG)
         db_table ='m2'
for m2 in M2.select(M2, M1).join(M1,join_type='CROSS JOIN',on=M2.id.is_null(False)):
     print m2.id,
     print m2.m1.id

If you need to prefix your models with the database name, you can try using the `schema` model metadata option. Example:

```python
class MyModel(Model):
    data = TextField()
    class Meta:
        database = db1
        schema = 'db1'

MyModel.select().sql()
# SELECT "t1"."id", "t1"."data" FROM "db1"."mymodel" AS "t1"
```

thanks
Issue: BoolField for Sqlite does't get mapped to a boolean value
Author: nad2000
State: closed
Created: 2017-10-25 23:58:21

BoolField for Sqlite does't get mapped to a boolean value. Instead its DB representation (INTEGER) value gets returned.

Two reasons for that. One, sqlite has no boolean type. Two, in python true is literally the same as 1 and false is 0.

Thanks for the comment. I was aware of that. That's why I thing it would be handy to have uniform type coercion. Original issue I ran into was that marshalling select query results into JSON with Sqlite3 and PG produces different output. I was expecting that mapping (isn't that what ORM is supposed to do) would shield me form limitations of the underlying DBs. Cheers!

The values *are* coerced, though. It's just the storage type:

```ipython
In [1]: from peewee import *

In [2]: db = SqliteDatabase(':memory:')

In [3]: class Test(Model):
   ...:     value = BooleanField()
   ...:     class Meta:
   ...:         database = db
   ...:         

In [4]: db.create_tables([Test])

In [5]: t = Test.create(value=True)

In [6]: f = Test.create(value=False)

In [7]: print Test.get(Test.id == t.id).value
True

In [8]: print Test.get(Test.id == f.id).value
False
```
Issue: How to specify an icu tokenizer locale identifier for subclass of sqlite_ext.FTSModel?
Author: galeo
State: closed
Created: 2017-10-25 06:10:19

Sqlite's built in icu tokenizer supports locale identifier, for example:
```sql
CREATE VIRTUAL TABLE thai_text USING fts3(text, tokenize=icu th_TH)
```
If tried to set `Meta.extension_options = {'tokenize': 'icu th_TH'}`, it will raise error:
```
peewee.OperationalError: unknown tokenizer: icu th_TH
```
Does it support specify this option for subclass of `playhouse.sqlite_ext.FTSModel`?

It seems that Peewee is quoting the tokenizer (both 2.x and 3.0a), it looks like:

```sql
CREATE VIRTUAL TABLE "note" USING FTS3 ("content", tokenize="icu en_AU")
```

Can you do an experiment for me? Using Peewee (or just the plain old `sqlite3` module):

```python
from playhouse.sqlite_ext import *
db = SqliteExtDatabase(':memory:')
db.execute_sql('CREATE VIRTUAL TABLE thai_text USING fts3 (content, tokenize=icu th_TH);')
```

Or

```python
import sqlite3
conn = sqlite3.connect(':memory:')
conn.execute('CREATE VIRTUAL TABLE thai_text USING fts3 (content, tokenize=icu th_TH);')
```

And let me know what happens?

They work well, both of the methods above, by not quoting the tokenizer.

Cool I didn't compile sqlite with icu so I wanted to double check that would work. I'll get a patch up today.

Thanks!

You mind trying out c88fc2ef08c4ed5e7214b7d30f0b20707db43995 and seeing if that resolves the issue?

It works! Thanks so much!
Issue: Nested result in JSON format
Author: rogercorrea
State: closed
Created: 2017-10-24 03:01:51

Please,

How can I use a query with Peewee to return a JSON format with Nested objects?
Example:

```
[
  {
    "active": 1, 
    "id": 1, 
    "name": "Los Angeles",
    "idState": 2,
    "TBState": {
         "id": 2,
         "name": "California"                
     }
  }
]
```
I can it using SqlAlchemy using relationship by model but in Peewee I don't know as make.
Thanks!

Peewee has a helper called "model_to_dict" which can construct (recursively, if you desire) a dictionary containing model data, which can then be serialized as JSON.

http://docs.peewee-orm.com/en/latest/peewee/playhouse.html#model_to_dict

Thanks for answer, but I did try use backrefs parameter in model_to_dicts but don't worked for me.

#My models:
```
class TBEstados(BaseModel):
    ativo = IntegerField(null=True)
    nome = CharField()
    uf = CharField(null=True)

    class Meta:
        db_table = 'TBEstados'

class TBCidades(BaseModel):
    ativo = IntegerField(null=True)
    idestado = ForeignKeyField(db_column='idEstado', null=True, rel_model=TBEstados, to_field='id', backrefs=True)
    nome = CharField(null=True)

    class Meta:
        db_table = 'TBCidades
```
#select data 

`data = TBCidades.select().where(TBCidades.idestado == idEstado and TBCidades.ativo == True).dicts()`

#Calling the model_to_dict function

```
def toJSON(obj):
    obj = [model_to_dict(t, backrefs=True) for t in obj]
    return jsonify(obj)
```

#result JSON
```
[
  {
    "ativo": 1, 
    "id": 1, 
    "idestado": 1, 
    "nome": "Acrel\u00e2ndia"
  }
]
```

You want `recurse=True` if the foreign key is on the model you're attempting to serialize.

Thanks, but I did try use recurse=True and don't work.
```

def toJSON(obj):
    obj = [model_to_dict(t, recurse=True) for t in obj]
    return jsonify(obj)
```

I ask me if haven't problem in my model.
Issue: Add support for using verbose names as CSV header values
Author: brandond
State: closed
Created: 2017-10-20 19:34:27


CSV module is no longer included in Peewee as of 3.0.0.

@coleifer is there a suggested replacement? This was a great simple way to dump stuff out.

The dumping code is just a thin wrapper: https://github.com/coleifer/peewee/blob/stable/playhouse/csv_utils.py

You can easily backport it into your codebase.
Issue: joins and default values
Author: LolusKekus
State: closed
Created: 2017-10-19 08:55:13

Why peewee returns default values for joined tabels when i forgot to select this field?

I have 2 models:
```
class ModelOne(Model):
    fieldOne = IntegerField(default=666)
    fieldTwo = IntegerField()

class ModelTwo(Model):
    modelOne = ForeignKeyField(ModelOne)
    someField = IntegerField()
```
For the begininng I want to query ModelTwo with all fields in it and fieldTwo from ModelOne:

```
qs = ModelTwo.select(ModelTwo, ModelOne.fieldTwo).join(ModelOne)
for i in qs:
    print(i.someField, i.modelone.fieldTwo)
```
Ok, it's work, i'm happy. BUT suddenly i understand that i need fieldOne from ModelOne too.
I don't know, maybe becaus of my inattentive, i do next thing:

```
qs = ModelTwo.select(ModelTwo, ModelOne.fieldTwo).join(ModelOne)
for i in qs:
    print(i.someField, i.modelone.fieldTwo, i.modelone.fieldOne)
```
I'm completely forgot to insert **ModelOne.fieldOne** in **select()** section and what i did get?

**default** value for **fieldOne**: 666

I think this isn't right. Maybe better raise an error that i forgot to add fieldOne in select section?


I understand your thinking, but unfortunately I'm worried about the implications of modifying this behavior in the 2.x branch. Perhaps I can introduce such a change in 3.x, though?

Fixed in 3.0a. No exception is raised, `None` is simply returned as the field value.
Issue: An InterfaceError was not catched only in PostgreSQLDatabase
Author: yanghg-basefx
State: closed
Created: 2017-10-14 08:01:54

I have searched this issue, and it looks like #334 

I can reproduce this problem by this code:

```python
from a_postgresql_module import MyModel

for instance in MyModel.select():
    print instance.id
    MyModel._meta.database.close()
```

This issue seems like postgresql will close a cursor automatically after the connection was closed by any reason, for example between a long time iteration, if something bad was happened so the connection was accidentally closed, and the cursor can't be re-opened. mysql doesn't have this problem, because its cursor never be closed.

I have a bad idea to solve that:

```python
class QueryResultWrapper(object):
    def __init__(self, model, cursor, meta=None, query=None):
        ...
        self.query=query
        ...

    def iterate(self):
        try:
            row = self.cursor.fetchone()
        except psycopg2.InterfaceError:
            self.cursor = self.query.execute().cursor
            row = self.cursor.fetchone()
            while row and self.process_row(row) in self._result_cache:
                row = self.cursor.fetchone()
        ...

class SelectQuery(Query):
    ...
    def execute(self):
        if self._dirty or self._qr is None:
            ...
            self._qr = ResultWrapper(model_class, self._execute(), query_meta, self)
            ...
        ...
```

That means if the error was happened, I will try to rebuild a cursor, and go to the last iterated point. Be easy to see, this is a bad class structure. And if someone else insert a new row between the old result wrapper's caches, the result will be wrong. Another way to avoid this error is fetching all the data one times. But both these two ideas are not the best choice.

So what's your idea? Or just wrap this error and add this case to the document and tell any developers to catch it in their own tools?

Why are you closing the connection in the middle of iterating over a cursor? That makes no sense.

This is only a reproducing. The problem is this issue often happened because some network fault so the connection was closed in the middle of iterating. 

It is only happened between two different city's database connection in our company, and we can't make the connection much better.

Gotcha -- you'll probably need to implement this on your end as some kind of "re-connecting" logic. But as far as integrating into peewee, that's above-and-beyond the scope of the project.

OK, I see.