akumoth/peewee-issues huggingface dataset:
Issue: postgresql: create doesn't support returning
Author: james-lawrence
State: closed
Created: 2019-01-15 15:14:54

was attempting to insert records into the database and ran into this issue.

problem is two fold: 
- create doesn't support returning afaik.
- documentation around returning is [rather sparse](http://docs.peewee-orm.com/en/latest/peewee/querying.html#returning-clause).

```python
m = Example.create(title = details.title).returning(Example)
# INSERT INTO "example" ("title") VALUES (%s) RETURNING "example"."id"
# *** AttributeError: 'KbImports' object has no attribute 'returning'
```

```
m = Example.insert(title = details.title).returning(Example).execute()
x = [y for y in m]
x[0].id
# INSERT INTO "example" ("title") VALUES (%s) RETURNING "example"."id", "kb_imports"."created_at", "kb_imports"."title", "kb_imports"."updated_at"
# *** AttributeError: 'tuple' object has no attribute 'id'
```

how does one go from an insert to a model object....

I've discovered how to do it.... mainly by happenstance. but my original two issues still hold.

```
ins = Example.insert(title = details.title).returning(Example).objects(Example).execute()
```

Correct. `create()` is not intended to be used with `returning()` since the return value of `create()` is already a model instance (with any auto-incrementing ID populated).

`returning()` can be used with `insert()` (or `insert_many`). By default it returns tuples of values, but as you noted, it is possible to return model instances.

@coleifer documentation should be improved for returning at a minimum before this is closed. took far too long to discover how to get general (and expected) response from peewee when using returning.

What specifically did you find confusing?

specifically [returning clause docs](http://docs.peewee-orm.com/en/latest/peewee/querying.html#returning-clause)

makes no reference to the ability to adjust the results of the query. an explicit link back to the [previous section](http://docs.peewee-orm.com/en/latest/peewee/querying.html#retrieving-row-tuples-dictionaries-namedtuples) would be very helpful. as when I was looking into the returning clause I never saw the ability to adjust the deserialization section.

which is pretty much the first thing someone is going to want to do after looking into returning().

Thanks, I noticed a quirk when adding tests... UPDATE and DELETE will return `Model` instances by default when you have a `RETURNING` clause. I think there was a regression that caused `INSERT` to return `tuple` objects by default (when you have a non-default returning clause).

I've also addressed this inconsistency, so that now if you specify a RETURNING clause on an INSERT, the default row type returned will be model instances.

I've also updated the docs.

http://docs.peewee-orm.com/en/latest/peewee/querying.html#returning-clause

looks good thanks.
Issue: Does Peewee supports mysql Date() function?
Author: littledemon
State: closed
Created: 2019-01-15 08:13:30

I have below command :

```
SELECT Date(mydate),sum(price),username 
from shop_table 
where Date(mydate)>'2011-01-07' 
GROUP BY Date(mydate),username
```

Because the dates include the time, I convert them into 'only date' using Date() function, so regardless of hours I can understand the total amount of sales in one day.

But is there an ability in peewee using 'fn' to do this?

```python
query = (ShopTable
    .select(
      fn.DATE(ShopTable.mydate).alias('date_value'), 
      fn.SUM(ShopTable.price).alias('price_sum'),
      ShopTable.username)
    .where(fn.DATE(ShopTable.mydate) > '2011-01-07')
    .group_by(fn.DATE(ShopTable.mydate), ShopTable.username))
```

In the future please post questions like "how do I..." to StackOverflow. This is the issue tracker.
Issue: Window function in order_by causes syntax error
Author: zmwangx
State: closed
Created: 2019-01-08 13:52:21

Consider the following simple example:

```py
import logging
import peewee

db = peewee.SqliteDatabase("/tmp/test.db")


class Transaction(peewee.Model):
    user_id = peewee.IntegerField()

    class Meta:
	database = db


db.create_tables([Transaction], safe=True)

Transaction.insert_many(
    dict(user_id=user_id) for user_id in [1, 2, 1, 3, 4, 2, 3, 1]
).execute()

logger = logging.getLogger("peewee")
logger.addHandler(logging.StreamHandler())
logger.setLevel(logging.DEBUG)

# Using a pre-defined window object is okay.
win = peewee.Window(partition_by=[Transaction.user_id], order_by=[Transaction.id])
(
    Transaction.select()
    .order_by(peewee.fn.FIRST_VALUE(Transaction.id).over(win))
    .window(win)
    .execute()
)

# Defining a window directly in order_by is not okay.
(
    Transaction.select()
    .order_by(
	    peewee.fn.FIRST_VALUE(Transaction.id).over(
            partition_by=[Transaction.user_id], order_by=[Transaction.id]
        )
    )
    .execute()
)
```

Executing this produces:

```sql
('SELECT "t1"."id", "t1"."user_id" FROM "transaction" AS "t1" WINDOW w AS (PARTITION BY "t1"."user_id" ORDER BY "t1"."id") ORDER BY FIRST_VALUE("t1"."id") OVER w', [])
('SELECT "t1"."id", "t1"."user_id" FROM "transaction" AS "t1" ORDER BY FIRST_VALUE("t1"."id") OVER w AS (PARTITION BY "t1"."user_id" ORDER BY "t1"."id")', [])
Traceback (most recent call last):
  File "/path/to/venv/lib/python3.7/site-packages/peewee.py", line 2712, in execute_sql
    cursor.execute(sql, params or ())
sqlite3.OperationalError: near "AS": syntax error
```

The second query apparently shouldn't have `w AS` after `OVER`; removing that gives us a valid query. I'm not sure why a window alias is used in this case, and in a problematic manner; a window alias is not used if `FIRST_VALUE(...).over(...)` appears in `select()` instead.

Thanks for reporting. This should be fixed by 55f515b.
Issue: sqlite_ext.FTSModel: MATCH'ing a single column
Author: zmwangx
State: closed
Created: 2019-01-04 23:06:07

SQLite FTS allows MATCH'ing a single column instead of the full table. An example from https://www.sqlite.org/fts3.html:

```sql
-- Example schema
CREATE VIRTUAL TABLE mail USING fts3(subject, body);

-- Example table population
INSERT INTO mail(docid, subject, body) VALUES(1, 'software feedback', 'found it too slow');
INSERT INTO mail(docid, subject, body) VALUES(2, 'software feedback', 'no feedback');
INSERT INTO mail(docid, subject, body) VALUES(3, 'slow lunch order',  'was a software problem');

-- Example queries
SELECT * FROM mail WHERE subject MATCH 'software';    -- Selects rows 1 and 2
SELECT * FROM mail WHERE body    MATCH 'feedback';    -- Selects row 2
SELECT * FROM mail WHERE mail    MATCH 'software';    -- Selects rows 1, 2 and 3
SELECT * FROM mail WHERE mail    MATCH 'slow';        -- Selects rows 1 and 3
```

Naturally (to me) one would want to write the column matching code like this:

```py
# SELECT * FROM mail WHERE subject MATCH 'software';
Mail.select().where(Mail.subject.match('software'))
```

However, this doesn't work, and instead it seems one has to write

```py
from playhouse.sqlite_ext import match

Mail.select().where(match(Mail.subject, 'software'))
```

and the `match` function isn't documented; only the `match` method of `FTSModel` is documented.

Maybe a `match` method could be added to `SearchField`?

Please excuse me if I missed anything obvious.

Correct, one needed to use the `match()` helper. I've added a method to `SearchField` as you suggested.

Thanks for the quick fix!
Issue: db_value() doesn't work fine as expected
Author: handalin
State: closed
Created: 2019-01-03 16:26:27

This is my DB configuration:

```
database = MySQLDatabase('creditCardManager',
    **{
        'host': 'localhost',
        'password': 'xxx',
        'user': 'yyy',
        'use_unicode': True,
        'charset': 'utf8',
        'autorollback': True,
    })
```

and I define an custom field:

```
class EnumField(SmallIntegerField):
    def __init__(self, choices, default, *args, **kwargs):
        self.default_db_value = default[0]
        self.default_python_value = default[1]
        self.int_from_str = {v:k for k, v in choices}
        self.str_from_int = {k:v for k, v in choices}
        return super(SmallIntegerField, self).__init__(*args, **kwargs)

    def db_value(self, value):
        if isinstance(value, str):
            value = value.decode('utf8')
        return self.int_from_str.get(value, self.default_db_value)

    def python_value(self, value):
        return self.str_from_int.get(value, self.default_python_value)
```

I define a model:

```
MY_CHOICES = [
    (1, u'ON'),
    (2, u'OFF'),
]
class Card(BaseModel):
    cid = PrimaryKeyField()
    my_field = EnumField(choices=MY_CHOICES, default=(0, u'unknown'))
```

And I have an updating API, which do:

```
card = Card.get(cid = cid)
card.my_field = u'ON'
card.save()
```

The problem is:
if I follow the operations below, everything works fine:
[Op-A]
1. restart my server
2. send request to update this field

but when I follow the operations below, 
[Op-B]
1. refresh the page
2. send request ( by clicking some button on my page) again to update this field

this time, db_value() doesn't works as expected.

```
OperationalError: (1366, "Incorrect integer value: 'ON' for column 'bank' at row 1")
```

what's the differences between [Op-A] and [Op-B] ?
Thanks a lot.

I have no idea what "refresh the page" implies. If you can produce a test-case that replicates this I will re-open. At the moment my sense is that the problem is in your code somewhere.
Issue: insert_many(), KeyError: '"-" is not a recognized field.'
Author: michalchrzastek
State: closed
Created: 2019-01-03 12:21:02

Hello,

I'm trying to insert multiple rows, but get this error:
                     `KeyError: '"-" is not a recognized field.'`
I figured its the minus before the figures, I was able to go around it using concatenation:
                    `transAmnt = "'" + data[1] + "'"`
But then I get this error:
                     `KeyError: '"\'" is not a recognized field.'`

I think I'm just chasing my own tail and missing something out... Can you advise please.

And here is my code:

MODEL:
```
class transactions(Model):
    trans_cat = IntegerField()
    trans_amnt = DecimalField()
    trans_date = DateField()
    trans_desc = CharField()
    uploadTime = DateTimeField(default=datetime.datetime.now)
    cardType = CharField(1,default="D")
    class Meta:
        database = finDB
```

INSERT:
```
with open(filename,'r') as fr:
        lines = fr.readlines()
        dictKeys = ['trans_amnt','trans_date','trans_desc','cardType']
        dictList = []
        for line in lines: #split line into correct column
            if re.match(r"^\d+.*$",line):
                data = line.split(';')
                old_date = data[0]
                datetimeobject = datetime.strptime(old_date,'%d/%m/%Y')
                transDate = datetimeobject.strftime('%Y-%m-%d')
                transDesc = "'" + data[2][:-1] + "'"
                transAmnt = "'" + data[1] + "'"
                transCard = statementType
                dataSource = [transAmnt,transDate,transDesc,transCard]
                dictList.append(dict(zip(dictKeys,dataSource)))
    with modelFinance.finDB.atomic():
        modelFinance.transactions.insert_many(dataSource).execute()
```
PRINT:
print(dictList):
BEFORE ADDING QUOTES
```
[{'trans_date': '2018-12-05', 'cardType': 'C', 'trans_amnt': '-6.60', 'trans_desc': 'APY, text, more text **1212\n'}, {'trans_date': '2018-12-06', 'cardType': 'C', 'trans_amnt': '-1.01', 'trans_desc': 'APY, texttext, more texttext **1111\n'}]
```
AFTER ADDING QUOTES
```
[{'trans_desc': "'APY, text, more text **1212", 'cardType': 'C', 'trans_amnt': "'-6.60'", 'trans_date': '2018-12-05'}, {'trans_desc': "'APY, texttext, more texttext **1111'", 'cardType': 'C', 'trans_amnt': "'-1.01'", 'trans_date': '2018-12-06'}]
```

Thanks



You don't need to quote the values. Peewee creates a parameterized query and the driver will handle all that for you.

That's what I thought, so why this error:
```
raise KeyError('"%s" is not a recognized field.' % field)
KeyError: '"-" is not a recognized field.'
```

Perhaps the data you are passing to `insert_many` may not be in the right format. You need to pass either a list of `dict` objects, or a list of tuples (then specify the fields explicitly using the `fields=` parameter). There are many examples.
Issue: primary_key=True not working
Author: dyadav7
State: closed
Created: 2019-01-02 05:19:16

What is the issue with below Model ?

     class Tags(BaseModel):
        tag = CharField(primary_key=True)
        cmd = CharField()


I am unable to save to database. Run attached script to see the issue.
I am unable to find the issues by checking docs.

[peewe_issue.py.txt](https://github.com/coleifer/peewee/files/2719957/peewe_issue.py.txt)

I get the below output

    $ peewe_issue.py
    Show all entries: 0
    ret=0
    Traceback (most recent call last):
      File "/home/deepaky/bin/peewe_issue.py", line 37, in <module>
       main()
      File "/home/deepaky/bin/peewe_issue.py", line 32, in main
        assert(ret)
    AssertionError
    $
`


It is clearly explained here:

http://docs.peewee-orm.com/en/latest/peewee/models.html#id4

You need to specify `force_insert=True` when saving with a non-integer primary key. Please read the docs.
Issue: Prefetch with limit might give unexpected results (at least with SQLite3)
Author: moubctez
State: closed
Created: 2018-12-31 13:14:05

I have tables called **Invoice** and **Item**, which essentially look like this:
```
class Invoice(Base):
   number = IntegerField(index=True)

class Item(Base):
    invoice_id = ForeignKeyField(Invoice, backref='items', on_delete='CASCADE')
```

While executing this: `Invoice.select().where(Invoice.number == 1001).limit(1).prefetch(Item)` sometimes does not work, when there are more Invoices with the same number.

In the log I see PeeWee is making two queries with LIMIT, but each returns a different Invoice.
```
('SELECT "t1"."id", "t1"."invoice_id" FROM "item" AS "t1" WHERE ("t1"."invoice_id" IN (SELECT "t2"."id" FROM "invoice" AS "t2" WHERE ("t2"."number" = ?) LIMIT ?))', [1001, 1])
('SELECT "t1"."id", "t1"."number" FROM "invoice" AS "t1" WHERE ("t1"."number" = ?) LIMIT ?', [1001, 1])
```

Turns out, SQLite3 returns undetermined results, depending on number of columns in the query:
```
sqlite> SELECT id FROM invoice WHERE number = 1001 LIMIT 1;
225
sqlite> SELECT id, number FROM invoice WHERE number = 1001 LIMIT 1;
153
```

To fix this I've added `order_by()`.

I propose this issue gets documented. I am not sure if it is only a problem with SQLite3, but maybe there should be an exception raised when using **limit** with **prefetch** without **order_by**, or something like **order_by(id)** should be added as a default.

This is mentioned here: http://docs.peewee-orm.com/en/latest/peewee/relationships.html#using-prefetch

> sometimes does not work, when there are more Invoices with the same number.

Well of course. You're limiting the selection to a single `Invoice` but multiple exist that match the `WHERE` clause. If you need them to be ordered deterministically, then you will need to explicitly order them.

Peewee contains as little magic as possible, so ordering your queries is left to you.

Thanks for the explaination.
How about this fix: query `Invoice`, then do a prefetch based on ids from the first query?
Issue: Ask: can we close a connection in pool automatically?
Author: likang
State: closed
Created: 2018-12-26 17:34:18

One of the best practices about connection management is close it when you are done. Normally a hook after request will take this job. like this:

```Python
@hook('after_request')
def _close_db():
    if not db.is_closed():
        db.close()
```

But I am wondering can we close the connection as soon as possible? especially in connection pool, since you can "return" the connection and get it back from the pool very fast. This will be very useful when you finish your database job, but still have to wait a long job before you can finish the request, like this:

```Python
@get('/')
def index():
    foo = Foo.get_by_id(1)
    bar = requests.get('/fetch-bar')
    return foo.name + bar.text
```
You can see before we get `bar` info by HTTP, we already don't need the connection, we can "return" this connection back to pool. 

Sure we can close the connection manually, but there will be lots of db operations in a project. I think it will be great if this job can be done automatically. So I try to overwrite `commit`, `rollback` and `atomic` this three methods of `Database`, like this:

```Python
class AutoReturnPooledMySQL(PooledMySQLDatabase):
    def commit(self):
        super(AutoReturnPooledMySQL, self).commit()
        if not self.in_transaction():
            self.close()

    def rollback(self):
        super(AutoReturnPooledMySQL, self).rollback()
        if not self.in_transaction():
            self.close()

    def atomic(self):
        ctx = super(AutoReturnPooledMySQL, self).atomic()
        return _auto_return_atomic(ctx)


class _auto_return_atomic:
    def __init__(self, ctx):
        self.ctx = ctx

    def __enter__(self):
        return self.ctx.__enter__()

    def __exit__(self, exc_type, exc_val, exc_tb):
        e = self.ctx.__exit__(exc_type, exc_val, exc_tb)

        if not self.ctx.db.in_transaction():
            self.ctx.db.close()

        return e

db = AutoReturnPooledMySQL(None)
```

In my test, the auto-close connection pool indeed increase the system's performance when lots of concurrent requests comes. But still, I don't know if this kind of modification is good or bad, or is there any hidden risk. I just write my thoughts down, and hope there will be an answer after a discussion, thank you.

I don't see how closing immediately would be faster in the long run. I suppose if a burst of requests was received very quickly you might save time because you are opening fewer new connections, as they get recycled faster.

In my opinion its foolish to override the transaction APIs. Should you wish to run the same application code with a non-pooled database, the behavior is going to be non-obvious, which is the cardinal sin.

Just call `db.close()` when its safe to release the connection, or use the database instance as a context manager.
Issue: SQLite not respecting CharField or FixedCharField max_length
Author: stigok
State: closed
Created: 2018-12-22 02:57:25

```python
from peewee import *

database = SqliteDatabase(':memory:')

class User(Model):
    class Meta:
        database = database
    username = CharField(max_length=21)

database.create_tables([User])

username = 'a' * 42
assert len(username) == 42

u = User.create(username=username)
assert u.username == username

u = User.get(username=username)
assert u.username == username
```
Nothing special happens when attempting to insert values of length > `max_length` into fields of type `CharField` or `FixedCharField`. Reading the code, I can not find any validation steps performed for respecting `max_length` either.

I was expecting an exception to be raised due to [docs stating](http://docs.peewee-orm.com/en/latest/peewee/api.html#CharField)
> Values that exceed length are not truncated automatically.

> Values that exceed length are not truncated automatically.

As that says, values that exceed length **are not truncated**.

I'd suggest that you should familiarize yourself with the database you're using, sqlite in this case: https://sqlite.org/datatype3.html

Sqlite has the following data-types:

* text
* blob
* integer
* real (float)
* null

Creating a `*char*` column will use the `text` data-type, but there is no validation of max-lengths or anything. You can create an explicit constraint, but the default behavior is just to treat it as text.

That is a great suggestion. I went ahead and made assumptions I shouldn't have made. Thank you for the tips.

For reference, taken from the [SQLite documentation about quirks](https://sqlite.org/quirks.html#flexible_typing):
> [...] SQLite allows you to store a 2000-character string into a column of type VARCHAR(50). Other SQL implementions would either throw an error or truncate the string. SQLite stores the entire 2000-character string with no loss of information and without complaint. [...]
Issue: Do I need additional steps to use ArrayField?
Author: msdos
State: closed
Created: 2018-12-20 09:28:47

I'm trying to use the `ArrayField` from Postgresql but I can't use your `get` method. Here is a proof of concept of the problem I'm facing it (I adapted the sample code from an old issue here in the repository):

```python
#!/usr/bin/env python

from peewee import Model
from peewee import TextField
from playhouse.postgres_ext import ArrayField
from playhouse.postgres_ext import PostgresqlExtDatabase
from playhouse.shortcuts import model_to_dict


db = PostgresqlExtDatabase(
    'peewee_test',
    user='postgres',
    password='postgres',
    host='localhost',
    autorollback=True
)


class BaseModel(Model):
    class Meta:
        database = db


class BlogPost(BaseModel):
    title = TextField()
    tags = ArrayField(TextField)


def main():
    db.create_tables([BlogPost])
    BlogPost.delete().execute()
    BlogPost.create(
        title='awesome',
        tags=['foo', 'bar', 'baz'])
    BlogPost.create(
        title='bad',
        tags=['foo', 'bax', 'baz'])
    for b in BlogPost.select().where(BlogPost.tags.contains_any('python', 'bar')):  # NOQA
        print(b.title)

    """SQL so far:
    SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname = %s ORDER BY tablename
    DELETE FROM "blogpost"
    INSERT INTO "blogpost" ("title", "tags") VALUES (%s, %s) RETURNING "blogpost"."id"
    INSERT INTO "blogpost" ("title", "tags") VALUES (%s, %s) RETURNING "blogpost"."id"
    SELECT "t1"."id", "t1"."title", "t1"."tags" FROM "blogpost" AS "t1" WHERE ("t1"."tags" && %s::TEXT[])
    """

    model_dict = model_to_dict(b)
    print(model_dict)
    b = BlogPost.get(**model_dict)

    """SQL so far:
    SELECT "t1"."id", "t1"."title", "t1"."tags" FROM "blogpost" AS "t1" WHERE ((("t1"."id" = %s) AND ("t1"."tags" = (%s, %s, %s))) AND ("t1"."title" = %s)) LIMIT %s OFFSET %s

peewee.py", line 2712, in execute_sql
    cursor.execute(sql, params or ())
psycopg2.ProgrammingError: operator does not exist: text[] = record
LINE 1: ... AS "t1" WHERE ((("t1"."id" = 3) AND ("t1"."tags" = (ARRAY['...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.    
"""


if __name__ == '__main__':
    main()
```

I'm using Postgresql 10.6, Python 3.6.7 and peewee 3.8.0 (psycopg2 is 2.7.6.1)

See 04aa7a0 for the fix.

Is this planned to a 3.8.1 release? The fix was really fast, thanks.

I'll release this fix along with 3.8.1 at some point in the coming days/weeks.

In the meantime there are a number of workarounds. The simplest being instead of using `.get()` with keyword arguments, use expressions, e.g.: `MyModel.foo == 'bar'` is *always* preferable to `foo='bar'`.