akumoth/peewee-issues huggingface dataset:
Issue: Can you elaborate more on why AutoField doesn't add 'AUTOINCREMENT' to the column definition in SQLite?
Author: dougthor42
State: closed
Created: 2019-03-28 20:21:59

Let me preface by saying that I was originally going to ask if this was intended behavior, but I did *at least some* research 😆.

I'm well aware of how SQLite handles `INTEGER PRIMARY KEY` (alias for the int64 `ROWID`) and how adding `AUTOINCREMENT` modifies that. https://www.sqlite.org/autoinc.html which you and others have linked many times before.

In [#805](https://github.com/coleifer/peewee/issues/805#issuecomment-168504439) you say:

> SQLite and autoincrement is more involved than you might think. There's a reason peewee doesn't specify AUTOINCREMENT with SQLite ... Peewee instead will use the unique rowid which uses the algorithm MAX(rowid) + 1 to generate new rowids, rather than ensuring they are unique across deletions, etc.

In [#147](https://github.com/coleifer/peewee/issues/147#issuecomment-12056456):

> Because SQLite incurs additional overhead to guarantee monotonically incrementing PKs, I will keep the default behavior which simply guarantees they are unique.

Questions:
+ Is this still true in peewee version 3.8 and above?
+ If so, can you expand on the reasoning for that?
+ Is there a way to force `AUTOINCREMENT` to be added, cpu/memory/disk overhead be damned?
    + In #147 you tell someone to use the `fields` arg of `SqliteDatabase`, but that doesn't work (`TypeError: 'fields' is an invalid keyword argument for this function`)
    + I haven't found anything in the docs ([sqlite extensions](http://docs.peewee-orm.com/en/latest/peewee/sqlite_ext.html#sqlite-ext), [primary keys](http://docs.peewee-orm.com/en/latest/peewee/models.html#primary-keys-composite-keys-and-other-tricks)) that mentions it. Perhaps I'm just blind?


You can use this, from the sqlite extension: http://docs.peewee-orm.com/en/latest/peewee/sqlite_ext.html#AutoIncrementField

The info in #147 is for a very old version.
Issue: Playhouse migrate MySQL DESCRIBE does not escape the table name
Author: arnulfojr
State: closed
Created: 2019-03-27 14:44:56

Hello,
Logs of `migrator.add_column` when a table name is not escaped, e.g., `order`
```bash
('ALTER TABLE `order` ADD COLUMN `type` VARCHAR(255)', [])
[2019-03-27 14:37:27 +0000] [1] [DEBUG] ('UPDATE `order` SET `type` = %s', ['RECURRING_FEE'])
[2019-03-27 14:37:27 +0000] [1] [DEBUG] ('DESCRIBE order;', None)
[2019-03-27 14:37:27 +0000] [1] [ERROR] (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order' at line 1")
```
This could be easily avoided by doing ```DESCRIBE `order`;``` in:
https://github.com/coleifer/peewee/blob/3e00b633ef4733df34d39e5c789a8b6826e3942f/playhouse/migrate.py#L502

Thanks, I don't know how I missed that before. Fixed.

Thanks for the quick fix!
Issue: Add support for Postgres's plainto_tsquery
Author: kkinder
State: closed
Created: 2019-03-25 18:28:10

peewee's `TSVectorField` helpfully includes a `match` method to add a `tsquery` to a query using `to_tsquery`. However, `to_tsquery` is one of two ways you can convert a string to a `tsquery`, per the [Postgres docs](https://www.postgresql.org/docs/9.1/textsearch-controls.html):

> PostgreSQL provides the functions to_tsquery and plainto_tsquery for converting a query to the tsquery data type. to_tsquery offers access to more features than plainto_tsquery, but is less forgiving about its input.

This pull request adds a `use_plain` parameter to `TSVectorField.match`, thus letting the programmer choose whether to use `to_tsquery` or `plainto_tsquery`. That's especially helpful when you're writing an application with casual user input for a search query.

I've made a slightly more compact patch for the actual implementation, plus some improvements to the docs and added a couple tests:

fa8fc729f37b1ab4da420fb51f8bbc90d4074fb7
Issue: ForeignKeyField(..., primary_key=True)
Author: fredrikchabot
State: closed
Created: 2019-03-24 19:30:24

When defining a ForeignKeyField who is also the Primary key for the table, It would make sense to have the backref to be a reference to the record and not a select instance as there can be only one record it is referring to.

class Rone(BaseModel):
    xxx = CharField(primary_key=True)
    name = CharField(null=True)

class Rtwo(BaseModel):
    xxx = ForeignKeyField(Rone, db_column='xxx', backref='rtwo', primary_key=True)
    num = IntegerField(null=True)

Works as expected:
rtwo=Rtwo().get(xxx='monkey')
print(rtwo.xxx.name)

Works differently than I would like:
rone=Rone().get(xxx='monkey')
print(rone.rtwo.num) # doesn't work
print(rone.rtwo.get().num) # works


Just add a `property` or whatever:

```python
class Rone(Model):
    @property
    def r2(self):
        return self.rtwo.get()
```
Issue: insert_many fails on sqlite 3.9.2
Author: mining8
State: closed
Created: 2019-03-22 14:06:43

hi, Thank you for your great project.

On peewee version 3.9.2, I'm trying to use insert_many with sqite 3.6.21, python 2.7.10:

Test code:
=========================
from peewee import *
db = SqliteDatabase('test.db')

class BaseModel(Model):
    class Meta:
        database = db

class TestDB(BaseModel):
    username = CharField(default='')

def Test2():
    usernames = ['charlie', 'huedddy', 'peewee', 'mickey']  # only 1 item is success
    row_dicts = ({'username': username} for username in usernames)

    # Insert 4 new rows
    TestDB.insert_many(row_dicts).execute()

db.connect()
db.create_tables([TestDB])

Test2()

=========================
If insert 1 row record is successful, Inserting multiple items will get the following error


  File "D:/Code/py/main.py", line 79, in Test
    TestDB.insert_many(row_dicts).execute()
  File "C:\Python27\lib\site-packages\peewee.py", line 1698, in inner
    return method(self, database, *args, **kwargs)
  File "C:\Python27\lib\site-packages\peewee.py", line 1769, in execute
    return self._execute(database)
  File "C:\Python27\lib\site-packages\peewee.py", line 2473, in _execute
    return super(Insert, self)._execute(database)
  File "C:\Python27\lib\site-packages\peewee.py", line 2236, in _execute
    cursor = database.execute(self)
  File "C:\Python27\lib\site-packages\peewee.py", line 2848, in execute
    return self.execute_sql(sql, params, commit=commit)
  File "C:\Python27\lib\site-packages\peewee.py", line 2839, in execute_sql
    raise
  File "C:\Python27\lib\site-packages\peewee.py", line 2627, in __exit__
    reraise(new_type, new_type(*exc_args), traceback)
  File "C:\Python27\lib\site-packages\peewee.py", line 2835, in execute_sql
    cursor.execute(sql, params or ())
peewee.OperationalError: near ",": syntax error



Holy shit, Sqlite 3.6? That's like 10 years old.

It doesn't support bulk insert, so you will need to use a loop in this case.
Issue: Order by FIELD function of MySQL
Author: SparkleBo
State: closed
Created: 2019-03-22 05:50:57

MySQL support FIELD function to sort result with specified order. But I didn't find the usage in peewee. I wonder that if peewee support that?

You should be able to just use `fn.FIELD()`, since FIELD is a sql function:

```python
my_query = User.select().order_by(fn.FIELD(User.id, 4, 1, 3, 2))
```
Issue: How to realize `count + distinct + group_by` query?
Author: hustlibraco
State: closed
Created: 2019-03-20 12:18:59

SQL like this:
```
-- portinfo: ip port state
SELECT
	state,
	count( DISTINCT ip, port )
FROM
	port_info
GROUP BY
	state
```
I have tried 2 method, use `concat_ws`  or `subquery`.  Is there a more elegant way to write this sql in peewee?

Pretty straightforward:

```python
query = (PortInfo
         .select(PortInfo.state, fn.COUNT(PortInfo.ip.distinct(), PortInfo.port))
         .group_by(PortInfo.state))
```

Translates into:

```sql
SELECT "t1"."state", COUNT(DISTINCT "t1"."ip", "t1"."port") 
FROM "portinfo" AS "t1" 
GROUP BY "t1"."state"
```
Issue: Make None a default when initializing SqliteDatabase
Author: impredicative
State: closed
Created: 2019-03-15 12:53:23

Instead of me having to do `SqliteDatabase(None)`, can `None` be made a default? I should then just be able to do `SqliteDatabase()`. Currently it raises `TypeError`. It seems needlessly noisy to have to specify `None` explicitly. Thanks.

If you don't initialize your `SqliteDatabase` instance with a database parameter, then you have to call `.init()` at a later time. By explicitly requiring `None`, it reinforces the idea that the database is not initialized yet, and the actual initialization is happening later in a call to `.init()`.

A very clear exception is raised, and a message is logged too if the user forgets to use `.init()`:

```
    raise InterfaceError('Error, database must be initialized '
peewee.InterfaceError: Error, database must be initialized before opening a connection.
```

The above error is IMHO sufficient reinforcement. Why is further reinforcement necessary?

You can always subclass it and override

```python
class SpecialSqliteDatabase(SqliteDatabase):
    def __init__(self, database=None, *args, **kwargs):
        super(SpecialSqliteDatabase, self).__init__(database, *args, **kwargs)
```
Issue: ForeignKeyField error
Author: vannesspeng
State: closed
Created: 2019-03-14 08:51:47

class Message(BaseModel):
    sender = ForeignKeyField(User, verbose_name="发送者")
    receiver = ForeignKeyField(User, verbose_name="接受者")
    message_type = IntegerField(choices=MESSAGES_TYPES, verbose_name="类别")
    message = CharField(max_length=500, verbose_name="内容", null=True)
    parent_content = CharField(max_length=500, verbose_name="标题", null=True)

I do not kown why?  error like this:
Traceback (most recent call last):
  File "D:/python-study/gitcode/TornadoForum/Myforum/tools/init_db.py", line 8, in <module>
    from Myforum.apps.messages.models import Message
  File "D:\python-study\gitcode\TornadoForum\Myforum\apps\messages\models.py", line 18, in <module>
    class Message(BaseModel):
  File "C:\Users\sd\.virtualenvs\TornadoForum-e7zC9psu\lib\site-packages\peewee.py", line 4903, in __new__
    field.add_to_class(cls, name)
  File "C:\Users\sd\.virtualenvs\TornadoForum-e7zC9psu\lib\site-packages\peewee.py", line 1523, in add_to_class
    invalid('The related_name of %(field)s ("%(backref)s") '
  File "C:\Users\sd\.virtualenvs\TornadoForum-e7zC9psu\lib\site-packages\peewee.py", line 1517, in invalid
    raise AttributeError(msg % context)
AttributeError: The related_name of message.receiver ("message_set") is already in use by another foreign key.

First: format your code. PLEASE. Here is a link: https://guides.github.com/features/mastering-markdown/

Second: you have two foreign-keys pointing to `User`. You need to specify a name for the back-reference that doesn't collide.

In your example, I would do something like:

```python
sender = ForeignKeyField(User, backref='outbox')
receiver = ForeignKeyField(User, backref='inbox')
```

Then you would do:

```python
user = User(...)
for message in user.outbox:  # Messages where "user" is "message.sender"
    # ...

for message in user.inbox:  # Messages where "user" is "message.receiver"
    # ...
```
Issue: Binding to EXCLUDED in upsert WHERE expression
Author: brandond
State: closed
Created: 2019-03-13 22:10:34

For languages with upsert support, the row that would have been inserted if not for the conflict can be addressed in SQL as 'excluded':

```Column names in the expressions of a DO UPDATE refer to the original unchanged value of the column, before the attempted INSERT. To use the value that would have been inserted had the constraint not failed, add the special "excluded." table qualifier to the column name. ```

How do I bind to this in the on_conflict statement? I would expect to be able to do something like this, but I can't figure out where to import EXCLUDED from.
```python
             .on_conflict(conflict_target=[Host.ip_address],
                          preserve=[Host.instance_id, Host.account_id, Host.tags],
                          where=(EXCLUDED.state != 'terminated'))
```


Seems like I can do this, but I'm not sure if this is correct or not:

```python
EXCLUDED = Entity('EXCLUDED') 
```

I mistakenly thought that `EXCLUDED` only applied to the SET part...didn't realize it could also be used in the `WHERE` portion. Thanks, I will put together a fix.

Fixed. Docs along with examples are now online: http://docs.peewee-orm.com/en/latest/peewee/api.html#EXCLUDED

It is exported if you use `from peewee import *`, or you can use `from peewee import EXCLUDED`.
Issue: auto field for postgres but not primary key
Author: ra-esmith
State: closed
Created: 2019-03-13 11:38:32

Good Morning,

How would I, using the current version of peewee.
Define a field on my table which is an integer, serially increases in value, and is not a primary key field.  Auto Field gets the serial, but always looks to be primary.  I have some old code which already has a primary key, but it the primary key is not serial and I wish to chunk some operations based this new serial field.

Thanks,
Evan

I am going to try

from playhouse.signals import Model, pre_save

class MyModel(Model):
    data = IntegerField()

@pre_save(sender=MyModel)
def on_save_handler(model_class, instance, created):
    # find max value of temp_id in model
    # increment it by one and assign it to model instance object
    next_value = MyModel.select(fn.Max(MyModel.temp_id))[0].temp_id +1
    instance.temp_id = next_value

which seems likely to work ... though rather involved perhaps ...

You use sequences for that.

```python
my_field = IntegerField(sequence='my_field_seq')
```
Issue: MaxConnectionsExceeded: Exceeded maximum connections.
Author: ghost
State: closed
Created: 2019-03-12 08:34:12

I wrote a wrapper to handle each request I received. Each request is in a separate thread.
Code Samples:
```
t = Thread(target=handle_request)
t.start()
```
The db config and wrapper:
```
    peewee_database = PooledMySQLDatabase(mysql_config['db'], host=mysql_config['host'], user=mysql_config['user'],
                                          password=mysql_config['password'], charset='utf8mb4', max_connections=20,
                                          stale_timeout=300)
    def wrap(*args, **kwargs):
        db = peewee_database
        try:
            db.connect()
            return func(*args, **kwargs)
        finally:
            db.manual_close()
```


Exception:
```
  File "/usr/lib/python2.7/site-packages/peewee.py", line 2681, in execute_sql
    cursor = self.cursor(commit)
  File "/usr/lib/python2.7/site-packages/peewee.py", line 2667, in cursor
    self.connect()
  File "/usr/lib/python2.7/site-packages/playhouse/pool.py", line 108, in connect
    return super(PooledDatabase, self).connect(reuse_if_open)
  File "/usr/lib/python2.7/site-packages/peewee.py", line 2630, in connect
    self._state.set_connection(self._connect())
  File "/usr/lib/python2.7/site-packages/playhouse/pool.py", line 154, in _connect
    raise MaxConnectionsExceeded('Exceeded maximum connections.')
MaxConnectionsExceeded: Exceeded maximum connections.
```

My peewee version: 3.3.4

Sry，I miss applying the wrapper in some functions.

Running each request in a new thread is kinda silly...but ok? Presumably your wsgi server would handle all that. So you're probably running threads-in-threads?

Anyway...ok?
Issue: defining table_name dynamically in _meta database not functioning properly
Author: eliteuser26
State: closed
Created: 2019-03-10 16:15:38

I am trying to define the table_name in the _meta database dynamically in the Python code. When defining the default table to use for a select query it doesn't access the proper table when it has been defined at the start even do the connection to the database was closed. 

Used this command for defining the table_name before opening the database dynamically:

```
Weathervalue._meta.table_name = self.selectedstation+'_weathervalue'
with db:
    year_function=fn.YEAR(Weathervalue.Date_Value)
    year_db=Weathervalue.select(year_function.alias('year')).group_by(year_function)
                   .order_by(year_function)
db.close()
```

It does use the table_name the first time around but it doesn't use it on subsequent calls when the name changes. I tried to define the table_name after opening the database connection but to no avail. It doesn't seem to have any effect after opening at least once. It retains the table_name on subsequent calls to the database. It doesn't reset the table_name to None when closing the database. 


```python
Weathervalue._meta.table_name = self.selectedstation+'_weathervalue'
del Weathervalue._meta.table  # Remove cached table property, will regenerate
```

Can you test making the addition above?

Issue resolved. According to the documentation, I needed to create a table model for each table in the database in this case 2 tables even though fields are identical. I added the table name in the table model in the meta field. Based on which one I selected I accessed the corresponding table model. I originally created only one table model for both which didn't worked. 

I noted in the context manager when using the with statement it doesn't properly close the database connection as I was able to verify with the db.is_closed() statement. I needed to add the db.close() to close the connection. Should I create a separate issue for this?

> I noted in the context manager when using the with statement it doesn't properly close the database connection as I was able to verify with the db.is_closed() statement. I needed to add the db.close() to close the connection. Should I create a separate issue for this?

This is tested. Probably some issue on your end:

https://github.com/coleifer/peewee/blob/master/tests/database.py#L165-L186

Thanks. It is probably in my code the way I initialized the database. I will look at it again.
Issue: NULLS not supported on SQLite
Author: mkoura
State: closed
Created: 2019-03-08 10:56:57

Syntax like `ORDER BY somevalue DESC NULLS LAST` doesn't work on SQLite. We are using `column.desc(nulls='LAST')` in one of our models and when using SQLite (for unit testing), this fails with

```
Traceback (most recent call last):
  File "<venv>/lib64/python3.7/site-packages/peewee.py", line 2835, in execute_sql
    cursor.execute(sql, params or ())
sqlite3.OperationalError: near "NULLS": syntax error

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
...
  File "<venv>/lib64/python3.7/site-packages/peewee.py", line 2835, in execute_sql
    cursor.execute(sql, params or ())
peewee.OperationalError: near "NULLS": syntax error
```

I didn't find any mention that `nulls` argument is PostgreSQL specific so I assume this behavior is a bug.

NULLS is not supported by Sqlite. For SQLite you can use a case expression, however: http://docs.peewee-orm.com/en/latest/peewee/api.html#Case

I've added a feature check and compatibility layer so that you can specify ``nulls='first'`` or ``nulls='last'`` with any database, and Peewee will generate the appropriate SQL. For postgres, it uses the usual non-standard ``nulls first/last``, but for sqlite and mysql, an equivalent ``CASE`` statement will be generated.

Awesome, thanks a lot!
Issue: AttributeError: 'Connection' object has no attribute 'server_version'
Author: strongbugman
State: closed
Created: 2019-03-06 09:17:47

Help! I got a problem when I call `create_tables` function with 3.90 version:

```
../../../.local/share/virtualenvs/shanbay_sea/lib/python3.6/site-packages/peewee.py:2989: in create_tables
    model.create_table(**options)
../../../.local/share/virtualenvs/shanbay_sea/lib/python3.6/site-packages/peewee.py:5999: in create_table
    and cls.table_exists():
../../../.local/share/virtualenvs/shanbay_sea/lib/python3.6/site-packages/peewee.py:5989: in table_exists
    return cls._schema.database.table_exists(M.table.__name__, M.schema)
../../../.local/share/virtualenvs/shanbay_sea/lib/python3.6/site-packages/peewee.py:2967: in table_exists
    return table_name in self.get_tables(schema=schema)
../../../.local/share/virtualenvs/shanbay_sea/lib/python3.6/site-packages/peewee.py:3620: in get_tables
    return [table for table, in self.execute_sql(query, ('VIEW',))]
../../../.local/share/virtualenvs/shanbay_sea/lib/python3.6/site-packages/peewee.py:2827: in execute_sql
    cursor = self.cursor(commit)
../../../.local/share/virtualenvs/shanbay_sea/lib/python3.6/site-packages/peewee.py:2813: in cursor
    self.connect()
../../../.local/share/virtualenvs/shanbay_sea/lib/python3.6/site-packages/playhouse/pool.py:108: in connect
    return super(PooledDatabase, self).connect(reuse_if_open)
../../../.local/share/virtualenvs/shanbay_sea/lib/python3.6/site-packages/peewee.py:2776: in connect
    self._state.set_connection(self._connect())
../../../.local/share/virtualenvs/shanbay_sea/lib/python3.6/site-packages/playhouse/pool.py:155: in _connect
    conn = super(PooledDatabase, self)._connect()
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

self = <playhouse.pool.PooledMySQLDatabase object at 0x1079802e8>

    def _connect(self):
        if mysql is None:
            raise ImproperlyConfigured('MySQL driver not installed!')
        conn = mysql.connect(db=self.database, **self.connect_params)
        if self._server_version is None:
>           version_raw = conn.server_version
E           AttributeError: 'Connection' object has no attribute 'server_version'

conn       = <_mysql.connection open to 'mysql' at 0x7fbddbb4fc18>
self       = <playhouse.pool.PooledMySQLDatabase object at 0x1079802e8>
```

Are there some broken changes?

Thanks for the quick report. This should be fixed -- I had been testing against pymysql and neglected to handle `MySQLdb` correctly.

I've pushed 3.9.1 which contains the fix.