akumoth/peewee-issues huggingface dataset:
Issue: Drop Constraint Syntax Error in MySQL 8.0.18
Author: hfudev
State: closed
Created: 2020-07-23 08:55:47

Hi,

I'm using `peewee.playhouse.migrate.drop_constraint` to do some migrations on MySQL 8.0.18.
If failed with error message: 
<pre>
`Error in query (1064): Syntax error near 'CONSTRAINT `constraint_name`' at line 1`
</pre>

I've checked the SQL.
This will fail: <pre>ALTER TABLE xxx DROP CONSTRAINTS `XXX`</pre>
This will succeed: <pre>ALTER TABLE xxx DROP FOREIGN KEY `XXX`</pre>

Is there anything peewee can improve? Or configurations in MySQL I ignored?

Thanks for your help!

For MySQL you would use the `drop_foreign_key_constraint` migrator method.

I couldn't find it in peewee's documentation. Maybe could add it later. Thanks!

Oh, it's spent a lot of time to find `drop_foreign_key_constraint` method... It will be very usefull to add it to the docs.

The foreign-key constraint should be dropped automatically when dropping a foreign-key column.
Issue: Is there a way to join across databases?
Author: skjbulcher
State: closed
Created: 2020-07-21 17:34:40

I have a MySQL server with a number of databases, e.g. `customer`, `dba_company_a`, `dba_company_b`. I need to join across databases to execute queries that include customer information from either dba_company database. I haven't been able to figure out how to do that with peewee.

I've tried using different `Meta` classes indicating it belongs to a different database

```
db = MySQLDatabase('customer')
class Customer(Model):
    id = AutoField()
    ....
    class Meta:
        database = db
```

as well as binding

```
db = MySQLDatabase('customer')
class Customer(Model):
    id = AutoField()
Customer.bind(db)
```

but in either case when I try to perform a join, _(something like `Orders.select(Orders, Customers).join(Customers).select()`, where Orders is a table on dba_company_a/b)_, I get a message like:

```
peewee.ProgrammingError: (1146, "Table 'dba_company_a.customer' doesn't exist")
```

Is there anyway to do joins across databases? Am I missing something simple?

You can use the `Meta.schema` to achieve an extra layer of namespacing.
Issue: .switch() and .join_from() for CTE's
Author: BigPictures
State: closed
Created: 2020-07-21 17:14:53

It looks to me like the `.switch()` and `.join_from()` methods are only defined on the `ModelSelect` class, meaning it's impossible to join from a CTE to multiple other tables. It might be good to move the join context stuff up the class hierarchy to `Select`, unless there's some reason doing so would be tricky.

Is there a workaround for this? I could join starting from one of the other tables, but I think that can have bad performance implications. Maybe making a new CTE for each join would work.

The join context only really has meaning for model-select, where peewee utilizes the structure of the model relationships to infer predicates and other things. A CTE can join multiple times, you just need to specify the appropriate predicate in the `on=` clause.

That makes sense.

Wouldn't join context also be helpful for chains of joins from a CTE? In those cases you'd also be able to take advantage of the structure of intermediate models.

This is definitely less important than the original issue I thought I'd found, though I did actually run into it in my code just now.

Well, the way you create a cte in the first place is using the regular model select apis:

http://docs.peewee-orm.com/en/latest/peewee/query_examples.html#find-the-upward-recommendation-chain-for-member-id-27

This doc section also shows how CTE is commonly used. In short, you write your select query regularly, and at the end you use the `cte()` method:

http://docs.peewee-orm.com/en/latest/peewee/querying.html#common-table-expressions

It’s okay, I got my thing working. Thanks!
Issue: Removing trailing square-brackets
Author: wilberh
State: closed
Created: 2020-07-10 03:19:03

Is there a way of removing the square-brackets from getting added into sql queries?  
I'm getting this sql statement - 

SELECT t1.rname, t1.mrole, t1.rmaterial FROM stg.instruments AS t1 []

You may be confusing the representation of the SQL statement with what actually gets sent to the database.

Those brackets are the representation of an empty list. The list is used to pass parameters to the driver, which handles escaping and all that (note to Always use parameterized queries).
Issue: Question: is comma equivalent to & in where clause?
Author: dsmurrell
State: closed
Created: 2020-07-07 20:38:18

In this example in the docs: http://docs.peewee-orm.com/en/latest/peewee/querying.html#filtering-records

`.where(Tweet.user == user, Tweet.is_published == True)`

Is this the same as `.where((Tweet.user == user) & (Tweet.is_published == True))` ?

I can't seem to find this comma syntax documented anywhere else.

Yes, multiple expressions passed to `where()` are `AND`-ed together. It's typically cleaner to use parentheses, though, since python operator precedence seems to trip up a lot of people. Forcing the habit of using parentheses helps ensure they don't make a mistake.

Great, thanks for clarifying.

I think it should have mentioned it in the document this
Issue: Peewee crashes when applying distinct to an expression
Author: StefanosChaliasos
State: closed
Created: 2020-07-06 15:50:58

**Describe the bug**
When applying `distinct` to an expression peewee crashes with `TypeError`. Beyond a table's column, in postgres distinct gets an [arbitrary expression](https://www.postgresql.org/docs/9.5/sql-select.html)

**Expected behavior**
```python
expr = (Model.column_a + Model.column_b).alias('expr')
Model.select(expr).order_by(expr).distinct(expr.alias())
```

I expect the following query:

```sql
SELECT DISTINCT ON ("t1"."column_a" + "t1"."column_b") "t1"."column_a" + "t1"."column_b" AS "expr" FROM "model" AS "t1" ORDER BY "t1"."column_a" + "t1"."column_b";
```

Instead I get the following exception:

```
Traceback (most recent call last):
  File "program.py", line 22, in <module>
    print(Model.select(expr).order_by(expr).distinct(expr.alias()))
  File "/home/.env/lib/python3.8/site-packages/peewee-3.13.3-py3.8.egg/peewee.py", line 1885, in inner
    return method(self, database, *args, **kwargs)
  File "/home/.env/lib/python3.8/site-packages/peewee-3.13.3-py3.8.egg/peewee.py", line 2143, in first
    return self.peek(database, n=n)
  File "/home/.env/lib/python3.8/site-packages/peewee-3.13.3-py3.8.egg/peewee.py", line 1885, in inner
    return method(self, database, *args, **kwargs)
  File "/home/.env/lib/python3.8/site-packages/peewee-3.13.3-py3.8.egg/peewee.py", line 2134, in peek
    rows = self.execute(database)[:n]
  File "/home/.env/lib/python3.8/site-packages/peewee-3.13.3-py3.8.egg/peewee.py", line 1885, in inner
    return method(self, database, *args, **kwargs)
  File "/home/.env/lib/python3.8/site-packages/peewee-3.13.3-py3.8.egg/peewee.py", line 1956, in execute
    return self._execute(database)
  File "/home/.env/lib/python3.8/site-packages/peewee-3.13.3-py3.8.egg/peewee.py", line 2128, in _execute
    cursor = database.execute(self)
  File "/home/.env/lib/python3.8/site-packages/peewee-3.13.3-py3.8.egg/peewee.py", line 3118, in execute
    sql, params = ctx.sql(query).query()
  File "/home/.env/lib/python3.8/site-packages/peewee-3.13.3-py3.8.egg/peewee.py", line 606, in sql
    return obj.__sql__(self)
  File "/home/.env/lib/python3.8/site-packages/peewee-3.13.3-py3.8.egg/peewee.py", line 2383, in __sql__
    .sql(EnclosedNodeList(self._distinct))
  File "/home/.env/lib/python3.8/site-packages/peewee-3.13.3-py3.8.egg/peewee.py", line 1760, in EnclosedNodeList
    return NodeList(nodes, ', ', True)
  File "/home/.env/lib/python3.8/site-packages/peewee-3.13.3-py3.8.egg/peewee.py", line 1740, in __init__
    self.nodes[0] = self.nodes[0].clone()
TypeError: 'tuple' object does not support item assignment
```
I assume that there is a programing error because peewee tries to assign something to a tuple object, although tuples in Python are immutable


Thanks, this is now fixed.
Issue: Override select()
Author: wilberh
State: closed
Created: 2020-07-06 05:14:29

How can I override the select() method from the Model class?  

I need to remove remove the double quotes from the sql strings created by peewee so my DBAPI v2.0 compliant driver database can take it.

Please quit spamming the same question. This is the third time you've posted some variation of this question. Asked and answered.

Apologies; just trying to figure out any other way of removing double quotes.  
I'm using Impyla which is fully DB API v2.0 compiant.  And the HiveServer2 (Hive) gives an error when it sees double quoted column-names.
Issue: How to get ridoff double quotes
Author: wilberh
State: closed
Created: 2020-07-06 04:32:40

I'm using a database driver that is DB API (PEP-249) v2.0 compliant.  
How can I tell peewee to not add double quotes on everything?
ex:
My base class is inheriting from the Model class.  Whenever I call ItemModel.select() and noticed peewee is adding double quotes.  How can I configure the class Model to not add double quotes.  

Is coming out like:
SELECT "t1"."item1", "t1"."item2", "t1"."item3" FROM "trex_inventory_recipe_material" AS t1

I need it like this: 
SELECT t1.item1, t1.item2, t1.item3 FROM trex_inventory_recipe_material AS t1

Responded to this when you asked on a different issue: https://github.com/coleifer/peewee/issues/2064#issuecomment-654236281

The alternative is to maintain a list of all sql reserved words and only quote words when they happen to collide with a sql keyword, and that seems silly. Quoting identifiers is the best way to handle this. Similarly, I've seen people who use whitespace and all kinds of other unexpected things in their sql identifiers. Quoting ensures that they work in all cases.
Issue: Multiple joins on same table produces ambiguous column name
Author: Zverik
State: closed
Created: 2020-07-04 15:40:56

I've got this code that inner joins the same table twice:

```
   cnt = (                                                                         
          User.select()
          .join_from(User, MailCode, on=MailCode.sent_by)                                         
          .join_from(User, MailCode, on=MailCode.sent_to)                                         
          .where(User.is_active == True).count()                                                  
      )
```

For some reason it uses alias "t2" twice:

```
SELECT COUNT(1) FROM (
  SELECT 1 FROM "user" AS "t1"
  INNER JOIN "mailcode" AS "t2" ON ("t2"."sent_by_id" = "t1"."id")
  INNER JOIN "mailcode" AS "t2" ON ("t2"."sent_to_id" = "t1"."id")
  WHERE ("t1"."is_active" = True)
) AS "_wrapped"
```

Is this expected behaviour? There is nothing on that in the documentation.

(I have solved this with `MailCode.alias()`, but it seems a pretty common case not to be processed.)

>I have solved this with MailCode.alias()

This is the peewee-official way to do reference the same table in multiple contexts within a single query.
Issue: Any integration with Hadoop (Hive)?
Author: wilberh
State: closed
Created: 2020-07-02 16:11:48

Does Peewee have support / extension for Hadoop (Hive / Pyhive)?

never heard of it. Read the manual:

http://docs.peewee-orm.com/en/latest/peewee/database.html#adding-a-new-database-driver
Issue: peewee + asyncpg
Author: jet10000
State: closed
Created: 2020-06-30 11:29:09

I want to use peewee to manage tables and queries, use peewee to generate sql queries, and then use asyncpg to execute. How can I do it? I hope there is an example.

```
import asyncio
import asyncpg
from devtools import debug
from auth.models import User

async def main():
    conn = await asyncpg.connect('postgresql://postgres:pass@localhost:5432/testdb')
    # Execute a statement to create a new table.
    q = User.select().sql()
    debug(q)
    res = await conn.fetch(q)
    debug(res)
    await conn.close()

asyncio.get_event_loop().run_until_complete(main())

```

```
    entry = self._entries.get(query)  # type: _StatementCacheEntry
TypeError: unhashable type: 'list'
````
Issue: Add UUIDKeyField for postgres
Author: jet10000
State: closed
Created: 2020-06-30 08:46:05

I found a UUIDKeyField in cockroachdb, Please add a UUIDKeyField for PostgreSQL, the default value `gen_random_uuid()`.

```
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
```
https://medium.com/@shawnoy/generate-a-uuid-as-postgresql-default-value-fc0ae41a37d2

if extension not exists, reminder to install this extension.



CockroachDB provides this function out-of-the-box. Postgres requires an extension, so it is not provided as a built-in. It is quite easy to add this if you want it, though -- just use the implementation from cockroachdb or create your own subclass.

PostgreSQL 13 
https://www.postgresql.org/docs/13/functions-uuid.html
Issue: Fix invalid escape sequence in regex
Author: narusemotoki
State: closed
Created: 2020-06-26 18:04:51

Hello.
Lint tools told me about `\` in regex. I think it's better to be raw strings.

pycodestyle says me:
```
playhouse/cockroachdb.py:71:48: W605 invalid escape sequence '\d'
playhouse/cockroachdb.py:71:52: W605 invalid escape sequence '\.'
playhouse/cockroachdb.py:71:55: W605 invalid escape sequence '\d'
playhouse/cockroachdb.py:71:59: W605 invalid escape sequence '\.'
playhouse/cockroachdb.py:71:62: W605 invalid escape sequence '\d'
```

Also pylint says:
```
playhouse/cockroachdb.py:71:17: W1401: Anomalous backslash in string: '\d'. String constant might be missing an r prefix. (anomalous-backslash-in-string)
playhouse/cockroachdb.py:71:21: W1401: Anomalous backslash in string: '\.'. String constant might be missing an r prefix. (anomalous-backslash-in-string)
playhouse/cockroachdb.py:71:24: W1401: Anomalous backslash in string: '\d'. String constant might be missing an r prefix. (anomalous-backslash-in-string)
playhouse/cockroachdb.py:71:28: W1401: Anomalous backslash in string: '\.'. String constant might be missing an r prefix. (anomalous-backslash-in-string)
playhouse/cockroachdb.py:71:31: W1401: Anomalous backslash in string: '\d'. String constant might be missing an r prefix. (anomalous-backslash-in-string)
```

Reference https://stackoverflow.com/questions/19030952/pep8-warning-on-regex-string-in-python-eclipse
Issue: Field in query results has ") appended to its name
Author: zacc
State: closed
Created: 2020-06-26 02:32:48

Executing this query in Python\:
`Comment.select(Comment.submission_id, Comment.id, fn.Max(Comment.score)).group_by(Comment.submission_id)`

The `.sql()` output of the query is:
`('SELECT "t1"."submission_id", "t1"."id", Max("t1"."score") FROM "comment" AS "t1" GROUP BY "t1"."submission_id"', [])`

Running `print(vars(record))` on the output of the query shows:
`{'__data__': {'submission_id': '7k57rl', 'id': 'drbr3hf'}, '_dirty': set(), '__rel__': {}, 'score")': 37}`

The score field now is labelled `score")`

Working around it with `getattr(record, 'score\")'` for the moment!

The output appears to be correct, though!

The SQL might not be optimum for the case but I thought the bug might be of interest anyway. 
Thank you for the excellent ORM.

You're probably using an older version of Sqlite(?) or otherwise the database is generating a column-name for your MAX value like 'MAX("t1"."score")'. Just specify an alias:

```python
fn.MAX(Comment.score).alias('max_score')
```

Then it will be available as the "max_score" attribute.

Sorry yes I should have mentioned SQLite! For your reference I am using 3.22, it has a version tag `3.22.0 2018-01-22 18:45:57` . Regards.
Issue: Use JSONField with database proxy?
Author: LVerneyPEReN
State: closed
Created: 2020-06-22 16:20:46

Hi,

I see that Playhouse extensions have dedicated `JSONField` for each database implementation. However, I did not find an abstract `JSONField` which could be used in a database proxy scenario.

For instance, I am using something like:

```python
import peewee
from playhouse.db_url import connect

database_proxy = peewee.DatabaseProxy()
database_proxy.initialize(
    connect(os.environ.get('DATABASE', 'sqlite:///data.db'))
)

class BaseModel(Model):
    class Meta:
        database = database_proxy
    
    field = JSONField()  # <-- where JSONField is either the JSONField from sqlite_ext, postgres_ext or mysql_ext depending on the `connect` URI.
```

Is something like this achievable easily?

Thanks!
Best,

No, that's not really do-able, as each database tends to require it's own form of syntax and operations.

You could try to apply a hook when the database proxy is initialized, which at that point would add the correct Json field to the model instance, I guess?

Digging into `peewee` code, I came across https://github.com/coleifer/peewee/blob/master/peewee.py#L4904-L4928 which seems to have a different behavior depending on the database in use.

Wouldn't it be possible to somehow unify https://github.com/coleifer/peewee/blob/master/playhouse/sqlite_ext.py#L122-L194 and https://github.com/coleifer/peewee/blob/master/playhouse/postgres_ext.py#L294-L320 in a single `JSONField` class with a similar approach, with actual implementation depending on the database object?

Beyond the quick and dirty hack, I guess this would require either moving this code from Playhouse to Peewee core or making a super `*Database` class in Playhouse which would supercede the `*Database` classes from Peewee.


This is something I regularly need and the usual way to fix this is to create from scratch and naive `JSONField` (no knowledge of the JSON structure, just encoded/decoded from text field). If something generic as described above is achievable here in a robust way, I can definitely help write and test it.
Issue: peewee.ProgrammingError: missing FROM-clause entry for table "t1"
Author: jet10000
State: closed
Created: 2020-06-20 01:46:12

```
query3 = Bookchapter.select(
    Bookchapter.id,
    Bookchapter.chapter,
)

Books.insert_from(query3, fields=[
    Books.id,
    Books.chapter
]).on_conflict(
    conflict_target=[Books.id],
    update={Books.chapter: Bookchapter.chapter}
).execute()
```



I can't see a problem in the SQL being generated. Can you provide the SQL for a working version and highlight the differences? Also what database are you using?

PostgreSQL12



Can you provide the SQL for a working version?

```
query3 = Bookchapter.select(
    Bookchapter.id,
    Bookchapter.chapter,
)

tt = Books.insert_from(query3, fields=[
    Books.id,
    Books.chapter
]).on_conflict(
    conflict_target=[Books.id],
    update={Books.chapter: Bookchapter.chapter}
).sql()

print(tt)
```

```
<peewee.PostgresqlDatabase object at 0x10d7571c0>
('INSERT INTO "books" ("id", "chapter") SELECT "t1"."id", "t1"."chapter" FROM "bookchapter" AS "t1" ON CONFLICT ("id") DO UPDATE SET "chapter" = "t1"."chapter"', [])
```


I thought you said it was not working. Please share the sql to a version of the query that works.

The proper way to write this is to reference the "new" update-value using `EXCLUDED`. Peewee provides a shortcut, which is the `preserve` option.

```python
Books.insert_from(query3, fields=[
    Books.id,
    Books.chapter
]).on_conflict(
    conflict_target=[Books.id],
    preserve=[q.c.name]  # Note we're referencing the name here.
)
```

You can also write:

```python
.on_conflict(
    conflict_target=[Books.id],
    preserve=['name']  # Name of column to preserve ("carry over") from insert.
)
```
Issue: AttributeError: module 'Person' has no attribute '_meta'
Author: teamcloud-jh
State: closed
Created: 2020-06-19 09:54:10

Unable to create a table containing foreign keys due to this error. 

Code:

    import peewee
    import Person

    db = peewee.MySQLDatabase('database', user='user', passwd='')
    class Activity(peewee.Model):
    id = peewee.CharField(primary_key=True)
    userId = peewee.ForeignKeyField(model=Person)

    class Meta:
        database = db
        db_table = 'activities'

    Activity.create_table()

Error:

      Traceback (most recent call last):
      File ".../Activity.py", line 7, in <module>
      class Activity(peewee.Model):
      File "...\AppData\Local\Programs\Python\Python38-32\lib\site-packages\peewee.py", line 6128, in __new__
      cls._meta.add_field(name, field)
      File "...\AppData\Local\Programs\Python\Python38-32\lib\site-packages\peewee.py", line 5917, in add_field
      field.bind(self.model, field_name, set_attribute)
      File "...\AppData\Local\Programs\Python\Python38-32\lib\site-packages\peewee.py", line 5180, in bind self.rel_field = self.rel_model._meta.primary_key
      AttributeError: module 'Person' has no attribute '_meta'

Other tables without foreign key can be created. 

I've checked other sites for solutions but none fit my situation.

Versions:
peewee - 3.13.3
Python - 3.8.3
OS - Windows 10

It seems like `Person` does not inherit `Model`. You did not put the code for `Person` so it's just a guess.

Thanks for the reply.

However, I do have Person inherit Model.

Code:
> from peewee import *
> class Person(Model):

Also tried
> import peewee
> class Person(peewee.Model):
Issue: LATERAL constant string doesn't have JOIN inside
Author: lephuongbg
State: closed
Created: 2020-06-16 07:34:37

`JOIN.LATERAL` constant string currently only contains `'LATERAL'`, which causes syntax error with Postgresql. It should be either `'INNER JOIN LATERAL'` or simply `'JOIN LATERAL'`

`JOIN.LEFT_LATERAL` is correct though (`'LEFT JOIN LATERAL'`).

I'm following the examples provided here in pg's doc: https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-LATERAL

I suppose we probably also need to support an inner lateral join, too, though I'm not sure how that would be used.

The way I'm currently going to address this is to add the `lateral()` method to the select query class.

This gives you the opportunity to use joins more naturally, and signifies only that the subquery being joined should be interpreted as a lateral join, which I think is more "correct" anyways.

Example:

```python

# Here we use a subquery to get the most-recent two tweets by each user.
subq = (Tweet
        .select(Tweet.content, Tweet.timestamp)
        .where(Tweet.user == User.id)
        .order_by(Tweet.timestamp.desc())
        .limit(2)
        .lateral())

# Here we do an inner join on the (lateral) subquery of tweets.
query = (User
         .select(User, subq.c.content)
         .join(subq, on=True)
         .order_by(subq.c.timestamp))
```

The example that you referred to with only `LATERAL` is actually multiple `FROM` clause, which needs a comma between them. 

![image](https://user-images.githubusercontent.com/1001176/84873220-e1186000-b0bd-11ea-8dac-8f3424047917.png)

Above clause is equivalent to `INNER JOIN LATERAL` or just `JOIN LATERAL`.

I do like the current API of `LATERAL` though (`.join(sq, join_type=JOIN.LATERAL)`), and I noticed there is also `CROSS JOIN LATERAL` too.