akumoth/peewee-issues huggingface dataset:
Issue: Add backref after foreign key declaration
Author: Rizhiy
State: closed
Created: 2020-01-20 15:06:49

Is it possible to add `backref` after foreign key is declared?

I'm trying to do something like:
```python
class User(Model):
	username = CharField(unique=True)


class BasePost(Model):
	date = DateField()
	user = ForeignKeyField(User)
	
	class Meta:
		primary_key = CompositeKey('date', 'ticker')


class ShortPost(BasePost):
	text = CharField()
User.add_ref(ShortPost, 'short_posts')

class LongPost(BasePost):
	text = TextField()
User.add_ref(LongPost, 'long_posts')


class PicturePost(BasePost):
	url = CharField()
User.add_ref(PicturePost, 'pictures')

... other types of posts
```
i.e. I want to have a limit of one type of post per user per day and repeating `CompositeKey('date', 'ticker')` along with the fields is ugly and tiresome.

I don't want to combine all posts into one table, since with many types of posts the table will get very sparse very fast.

You're going to have a really bad time inheriting foreign-keys. It just doesn't work well.

Furthermore, composite primary-keys are not well-supported, e.g. Peewee doesn't support foreign-keys to models with composite PKs.

Also, having different tables for various "post" types is almost guaranteed to be a mistake. If you ever want to combine the results you're forced to do unions and then you will be fighting to get the data you need for each type.

Play to the strengths of your database and your tools and use a single table with multiple columns (post_type, text, e.g.). It won't waste space. You know what they say about premature optimization, right?

Do not use a composite PK (really). Just use an auto-incrementing integer or a UUID if you must. You can add a multi-column unique index on anything you need to constrain.
Issue: Changing single column primary key to a composite primary key
Author: neenuj
State: closed
Created: 2020-01-19 11:00:52

I have a 'person' table with auto generated 'id' as the primary key. I want to change this primary key to a composite primary key with first_name and last_name as the columns, using schema migrator. Is it possible to achieve this? Or is executing a SQL my only alternative? I am using PostgreSQL. 

    def perform_schema_migration(db):
        migrator = PostgresqlMigrator(db)
        migrate(
                migrator.drop_constraint('person', 'person_pkey')
                )

I would strongly advise against trying to use a composite primary-key for this, as Peewee provides much better support for scalar primary-keys (integers, uuids, etc). Many things will be harder for you if you switch to a composite primary-key - specifically creating foreign-keys to your "Person" table. Peewee **does not support FKs to models with composite PKs**.

If you are just trying to enforce the uniqueness of first/last, then create a multi-column UNIQUE index:

```python
# Specify the table, column names, and whether the index should be
# UNIQUE or not.
migrate(
    # Create a unique index on the first and last name fields:
    migrator.add_index('person', ('first', 'last'), True),
)
```

If you insist on making your life harder, though, you can consult the Postgresql documentation for instructions on dropping a primary-key and creating a new multi-column PK, as creating/dropping PKs is not supported by the migrations library.
Issue: [Question] Searching through binary data in Postgres
Author: its-a-feature
State: closed
Created: 2020-01-18 20:53:37

I'm storing user output into a BlobField within my database (instead of a TextField) in case the user's output includes null characters. However, I still want to do regex searching across this data.

From the postgresql command line, I can do a command like:
`select encode(Response.response, 'escape') LIKE 'test%'  from Response;`
which escapes the response back to text and then allows me to search like normal. This is a built-in functionality of postgres.

How do I do this from within Peewee? I've tried something like peewee's cast to 'text', but it just uses the hex string representation. 

I'm using the latest peewee and python3.6

Got it working with the `fn` hook into the underlying functions.
Issue: how write one code in peewee for mysql, sqlite
Author: TechComet
State: closed
Created: 2020-01-15 21:55:45

Hi,

how write one code in peewee for mysql, sqlite

I use peewee and sqlite on local server but on server use mysql need write one code to use mysql and sqlite

Like
```
database_type = 'mysql'

if database_type == 'mysql':
    db = peewee.mysql_connect('localhost', 'root', 'password', 'test1234.db')
else:
    db = peewee.sqlite_connect('test123.db')
```

if write MEDIUMTEXT Field and sqlite in database_type use this like TEXT 

You can specify a custom field type, e.g.

```python
class MediumTextField(TextField):
    field_type = 'MEDIUMTEXT'
```

And just use that if the db is mysql.

Alternatively, you can configure MySQL to treat all textfields as MEDIUMTEXT if you wish:

```python
MySQLDatabase.field_types['TEXT'] = 'MEDIUMTEXT'
```

I think something like this is what I was looking for

```
class UnknownField(object):
    def __init__(self, *_, **__): pass
```

thank you
Issue: Support indexing into array using expression w/postgres
Author: coleifer
State: closed
Created: 2020-01-13 18:35:47

Question on SO: https://stackoverflow.com/questions/59708993/how-do-i-access-the-last-index-of-an-arrayfield-in-a-peewee-query-with-postgres

Example:

```python

from peewee import *
from playhouse.postgres_ext import *

db = PostgresqlExtDatabase('peewee_test')

class Test(Model):
    value = ArrayField(IntegerField)
    class Meta:
        database = db
        table_name = 'test_1'

db.connect()
db.create_tables([Test])

Test.create(value=[1,2,3,4])
Test.create(value=[2,7,5])
Test.create(value=[5,6,7])
VAL = 6

query = Test.select().where(Test.value[fn.array_length(Test.value) - 1] > VAL)
for row in query:
    print(row.value)
```

Error:

```python

Traceback (most recent call last):
  File "ww.py", line 22, in <module>
    query = Test.select().where(Test.value[fn.array_length(Test.value) - 1] > VAL)
  File "/home/charles/pypath/playhouse/postgres_ext.py", line 201, in __getitem__
    return ObjectSlice.create(self, value)
  File "/home/charles/pypath/playhouse/postgres_ext.py", line 135, in create
    parts = map(int, value.split(':'))
AttributeError: 'Expression' object has no attribute 'split'
```

Fixed.
Issue: mysql query limit 0
Author: hereisok
State: closed
Created: 2020-01-10 07:05:56

"limit 0" is a legal operation in [MySQL](https://dev.mysql.com/doc/refman/8.0/en/limit-optimization.html)

> LIMIT 0 quickly returns an empty set. This can be useful for checking the validity of a query. It can also be employed to obtain the types of the result columns within applications that use a MySQL API that makes result set metadata available. With the mysql client program, you can use the --column-type-info option to display result column types.

But in peewee,  
limit 0 will be converted to LIMIT 2 ** 64-1

https://github.com/coleifer/peewee/blob/fc342ddcfda268a27e40a8178982ddf747de80a8/peewee.py#L2023
Issue: ambiguous column error on update query
Author: ydelibasi
State: closed
Created: 2019-12-26 08:49:34

I have two geometry columns with same name. I would like execute update query but I get "peewee.ProgrammingError: column reference "geom" is ambiguous"  error. 
How to use alias for this columns ?

``` 
UserFileRows.update(
    location_ids=Location.parent_ids
)
.from_(Location)
.where((fn.st_intersects(UserFileRows.geom, Location.geom)) &
       (UserFileRows.fileid == file_id) &
       (Location.location_level_id == 4)).execute()
```

Can you share the generated SQL for your query? I tried to replicate a minimal example and the generated SQL contains qualified names that would seem to resolve any ambiguity.

Also, what version of Peewee are you using?

generated SQL:
`
('UPDATE "user_data"."user_file_rows" SET "location_ids" = "parent_ids" FROM "r_data"."location" AS "t1" WHERE ((st_intersects("geom", "geom") AND ("fileid_id" = %s)) AND ("location_level_id" = %s))', ['6f3697c3-****', 4])
`

I use peewee 3.6.4
Thanks

Upgrade to a newer version - that should resolve the problem with the SQL generation.

I upgraded version. It works.

Thanks
Issue: on_conflict update is not evaluating the WHERE clause correctly
Author: neenuj
State: closed
Created: 2019-12-26 06:46:05

Thank you for creating Peewee and for being active on addressing issues and on answering questions. 

I encountered issues while upserting data using on_conflict() method. So i faked a scenario to identify the root cause and here is what i could find.

I created a Person model using Peewee inserted a record using the below dict:
personDict = {'id':'11011', 'fname':'Jane', 'lname':'Austin', 'age':28}
Then i tried updating the record using on_conflict() method. The below code should have updated the age of the above record to 30, since the id is the same and since the condition (fname != 'Jane' or lname != 'Austin') evaluates to true. But 'age' did not get updated. 

It seems like when we have a WHERE clause with an 'or' condition, if the first condition is evaluated to false, the remaining conditions in the where clause are not evaluated. In the below code, if i switch the order of the two conditions, to make it (Person.lname != EXCLUDED.lname or Person.lname != EXCLUDED.lname), then the update works. Is there a workaround to address this issue?

    personDict = {'id':'11011', 'fname':'Jane', 'lname':'Brown', 'age':30}
    upsert(personDict )

    def upsert(personDict ): 
        query = Person.insert(personDict)
        query.on_conflict(conflict_target=[Person.id], 
                                     update={Person.age: EXCLUDED.age},
                                     where=(Person.fname != EXCLUDED.fname or
                                                  Person.lname != EXCLUDED.lname)).execute()


If i run the below PostgreSQL statement, the 'age'does get updated to 30 as expected.

    INSERT INTO person (id, fname, lname, age)
    VALUES (11011, 'Jane', 'Brown', 30)
    ON CONFLICT (id) 
    DO UPDATE
    SET age = EXCLUDED.age
    WHERE (person.fname <> EXCLUDED.fname OR person.lname <> EXCLUDED.lname);

The docs are very clear, you cannot use python "or", "and" or "not" to combine expressions.

Rewrite

```python
Person.fname != EXCLUDED.fname or Person.lname != EXCLUDED.lname
```

As

```python
(Person.fname != EXCLUDED.fname) | (Person.lname != EXCLUDED.lname)
```

Thank you! This worked for me. I am struggling to find the documentation on this. Could you point me to the link if you have it handy, please?

https://docs.peewee-orm.com/en/latest/peewee/query_operators.html#
Issue: something is wrong with proxy
Author: viponedream
State: closed
Created: 2019-12-21 17:51:31

in one app, i want to use two database, but it only save to the last database.

1,  define model
database_proxy = Proxy() 

class Item_DB(Model):
   user = CharField(null=True, unique=False)
    class Meta:
        database = database_proxy  # Use proxy for our DB.

2, define my own class the use peewee.
class Database:
 def __init__(self, table_name: str, orm_mapping = None, db = None):
        self.table_name = table_name      
        self.table = None                
        self.db = db if db is not None else sqlite_database  
        database_proxy.initialize(self.db)
        self.orm_mapping = orm_mapping if orm_mapping is not None else Item_DB 
    
def get_table(self):
        table = type(self.table_name, (self.orm_mapping,), {})
        self.db.connect(reuse_if_open=True)        
        if not table.table_exists():
            try:
                table.create_table()
            except BaseException:
                print(f'建立表{table}不成功!')
        return table

   def update(self, item: Item):
        table = self.get_table()
        with self.db.atomic() as transaction:
            try:
                table.replace(item.__dict__).execute()
            except:
                transaction.rollback()

3, use it now
DB_NAME = "all_account.db"
DB_PATH = os.path.join(ROOT_PATH, DB_NAME)
_db = SqliteDatabase(str(DB_PATH))     
all_db = Database(table_name=app_name, orm_mapping=Item_DB, db=_db) 

DB_NAME = "account.db"
DB_PATH = os.path.join(ROOT_PATH, DB_NAME)
_db = SqliteDatabase(str(DB_PATH))     
main_db = Database(table_name=app_name, orm_mapping=Item_DB, db=_db)  

item=something
all_db.update(item)
i want to save the data to the first db(all_db), but in fact it save to the last one.

it seems it can not change the database.


Please edit your issue to format your code and I will reopen.
Issue: create_tables with safe=True doesn't work for indexes
Author: miguelglopes
State: closed
Created: 2019-12-20 19:30:37

When running the following code:

```
import peewee

db = peewee.MySQLDatabase(
    config["database"]["db"],
    user=config["database"]["user"],
    password=config["database"]["pwd"],
    host=config["database"]["host"],
    port=config["database"]["port"],
)

db.connect()

class Show(peewee.Model):
    showid = peewee.PrimaryKeyField()
    year = peewee.IntegerField(null=True)

    class Meta:
        db_table = "Show"
        database = db

class LatestEpisode(peewee.Model):
    show = peewee.ForeignKeyField(Show, backref="latestEpisodes")
    season = peewee.IntegerField()
    episode = peewee.IntegerField()

    class Meta:
        db_table = "LatestEpisode"
        database = db

db.create_tables([Show, LatestEpisode], safe=True)
```

Everything runs as expected if the tables don't exist.

However, if they exist, i got the following error:
peewee.InternalError: (1061, "Duplicate key name 'latestepisode_show_id'")

The problem is that, although the tables are created with "IF NOT EXISTS", due to the safe=True, the indexes are not:
('CREATE INDEX `latestepisode_show_id` ON `LatestEpisode` (`show_id`)', [])

MySQL does not support "IF NOT EXISTS" for indexes for some reason.

So what peewee does when you're using MySQL is to first check if the table exists -- and if it does -- then it's a no-op. So I think something weird may be going on in your setup.

Ok, I think I found the problem.

I capitalized the first letter in the name of the table. But on mysql workbench it showed as all lower letters. I did some tests:
table._meta.table_name returned 'Show'
db.table_exists("Show") returned false 
db.table_exists("show") returned true

If I capitalize the first letter of the name of the table, it doesn't detect the table exists and does the create table operation.

If i don't capitalize, everything works as expected.

I think by default mysql uses case insensitive tables in windows, which I'm running.

Peewee should, somehow, read this behaviour from the mysql lower_case_table_names variable.
Issue: Get the SQL output of count?
Author: ztj1993
State: closed
Created: 2019-12-18 04:21:21

Suppose I have a query

```
OrderModel.select().count()
```

I want to get SQL output, Because I only use the model, not the database.





>I want to get SQL output

I don't know what this means? The above function will query the database and return the count of rows in the select query.

You can use `.scalar()` to obtain the result of a scalar aggregate query.

http://docs.peewee-orm.com/en/latest/peewee/querying.html#retrieving-scalar-values

Please note that the issue tracker is not for "how do I..." questions. Post those on stackoverflow or ask in the #peewee IRC channel or mailing list. This is for tracking issues with the software.
Issue: Suggestions for moving from SQLIte to PostgreSQL ?
Author: nhi-vanye
State: closed
Created: 2019-12-17 13:05:56


I'm currently using SQLite as the backend for a simulation program and am interested in supporting the use of PostgreSQL to allow for multiple independent simulations to be run in parallel.

The blocker is a user-defined function.

SQLite has no SQL functions so I wrote it in python

PostgreSQL might be able to use PL/python - but its unclear how to integrate that with peewee.

I suppose I could have two implementations (python, SQL), but that would be less than optimal (and how do I get configuration data into a SQL function - plus one reason I wrote it in python was that I couldn't get my head around it in SQL). 

The function is doing set math and returning a bool that is used in the parent query.


What have other people done in tis area ?

```
    allowed = False

    try:

        relatives_1 = set()
        relatives_2 = set()

        # TODO - make the "relationship limit" configurable
        common_ancestor = censere.models.RelationshipEnum.great_great_great_grandparent

        for r1 in censere.models.Relationship.select(censere.models.Relationship.second).where( 
                ( censere.models.Relationship.first == id_1 ) &
                ( censere.models.Relationship.relationship <= common_ancestor ) 
            ).tuples():
            relatives_1.add( str(r1[0]) )

        for r2 in censere.models.Relationship.select(censere.models.Relationship.second).where( 
                ( censere.models.Relationship.first == id_2 ) &
                ( censere.models.Relationship.relationship <= common_ancestor ) 
            ).tuples():
            relatives_2.add( str(r2[0]) )

        if relatives_1 == relatives_2:
            allowed = False

        if len(relatives_1 & relatives_2) == 0:
            allowed = True

    except Exception as e:

        logging.log( logging.ERROR, 'Caught exception %s', str(e) )

    return allowed


```




This is more appropriate for stack-overflow, as the issue tracker is used for tracking issues with the library.
Issue: [Question] Query database output
Author: zaee-k
State: closed
Created: 2019-12-17 11:03:34

Hey there!
First of all a big big thank you for creating peewee, it's a nice module to work with PostgreSQL and SQLite!
I have one silly question in regard of querying database. Currently I'm playing with peewee and FastAPI. One thing I've noticed is upon executing select(), data is of course queried and returned BUT together with some metadata like "__data__", "_dirty" and "__rel__". 
How can I get only the data which is stored in db EXCEPT that metadata? I might of course just read "__data__" value from the output, but when using aliases, my alias and value is outside "__data__". Here example to visualize:

What I get now:
```
[
   {
      "__data__":{
         "id":25052,
         "l":"abc",
         "asd":"bbd",
         "ip":"218.1.1.1",
         "timestamp":"2019-12-17T11:57:46.790299"
      },
      "_dirty":[

      ],
      "__rel__":{

      }
   }
]
```

What I want to get:

```
[
   {
      "id":25052,
      "l":"abc",
      "zxc":"def",
      "ip":"218.1.1.1",
      "timestamp":"2019-12-17T11:57:46.790299"
   }
]
```

TL;DR: How to hide "_dirty", "_rel" and remove "__data__" as key for actual data?

>What I get now:

That is *not* returned by executing a `select()` query. That is metadata on the model instance. By default, issuing a `select()` will return a cursor that yields model instances for each row of the result set.

My guess is that you want something like `.dicts()`, which should give you exactly what you want. Just modify your query:

```python

query = MyModel.select()
for obj in query:
    # obj is a MyModel instance.
    ...

# This is probably what you want:
for row in query.dicts():
    # row is a dict of just the field data.
    ...
```

http://docs.peewee-orm.com/en/latest/peewee/querying.html#retrieving-row-tuples-dictionaries-namedtuples

Yes! That was the thing I've thought to do. All is good now, thanks! :)
Issue: pwiz incorrectly generates a composite unique index
Author: navolotsky
State: closed
Created: 2019-12-16 04:12:05

### Issue
There is the problem with pwiz: if there is a unique index across two fields, one of which is `text` or `varchar` and a value of the `lower()` function from a value of such field is taken into the unique index, then a composite unique index is not generated. Instead, the non-text field is assigned unique.

### Examples
Tables definition in PostgreSQL dialect:
```SQL
CREATE TABLE test_table
(
    id              SERIAL PRIMARY KEY,
    some_field      INTEGER,
    some_text_field TEXT
);
CREATE UNIQUE INDEX test_table_somes_uniq
    ON test_table (some_field, lower(some_text_field));

CREATE TABLE test_table2
(
    id                 SERIAL PRIMARY KEY,
    some_field         INTEGER,
    some_varchar_field VARCHAR(255)
);
CREATE UNIQUE INDEX test_table2_somes_uniq
    ON test_table2 (some_field, lower(some_varchar_field));
```
peewee models generated by pwiz:
```python
class TestTable(BaseModel):
    some_field = IntegerField(null=True, unique=True)
    some_text_field = TextField(null=True)

    class Meta:
        table_name = 'test_table'

class TestTable2(BaseModel):
    some_field = IntegerField(null=True, unique=True)
    some_varchar_field = CharField(null=True)

    class Meta:
        table_name = 'test_table2'
```
### Versions
* peewee: 3.12.0
* PostgreSQL: 11.3 

Yes, `pwiz` is intended to provide a reasonable best-effort at converting the schema into the db into models, but it is not 100% perfect. As the docs say, it's purpose is to produce some basic skeleton code that should handle correct column types and foreign-keys. Things like constraints across columns with arbitrary SQL functions are not introspected currently.

That being said, constraints are not enforced on the Python side at all -- so the fact that peewee models may not reflect the constraint will not have any impact on the actual constraint that exists in the database.

Ok, I agree. But I think in that case it would be better, if pwiz just do nothing (the non-text field is assigned unique), because it’s easy to overlook and it’s confusing later when you look at the models while develop. I understand that this can be difficult to fix. This is just a wish =)

Thanks, I will look into it some more.

So unfortunately this is a limitation of the data returned by postgresql when introspecting the set of indexes on a table. Peewee uses a rather complex query to ask postgres for metadata about the index, which includes an aggregation of the columns used in that index. Because the unique comprises a column and a function, our query only returns the column which was matched.

For now I don't think we can do much better and this will just have to be a known limitation.

Alternatives might be to try and parse the index sql to extract the columns, but in that case we still wouldn't be likely to get an accurate reflection of the actual schema since we'd also have to capture the functions being applied.
Issue: Update from values
Author: eternalflow
State: closed
Created: 2019-12-15 10:37:24

Is there a way to create following query for batch updating rows from given values?

```sql
UPDATE mytable m
SET 
    field1 = v.field1, 
    field2 = v.field2
FROM (SELECT * FROM (VALUES %s) as t (id, field1, field2)) v
WHERE m.id = v.id
```

Yes:

```python
# let's say we want to update two rows.
vl = ValuesList([
    (1, 'huey-x'),
    (2, 'zaizee-x')], columns=['id', 'username'], alias='vl')
query = (User
         .update({'username': vl.c.username})
         .from_(vl)
         .where(User.id == vl.c.id))
```

Also you can look at the tests:

https://github.com/coleifer/peewee/blob/fc342ddcfda268a27e40a8178982ddf747de80a8/tests/models.py#L4120-L4162