akumoth/peewee-issues huggingface dataset:
Issue: Consistency between `Dataset.insert` / `Dataset.update` and `Model.insert` / `Model.update`
Author: patarapolw
State: closed
Created: 2018-10-03 03:30:56

It's not consistent in that making transferring from Dataset to SqliteExtDatabase harder.

Furthermore, there is no upsert function. I have to create my own.

    class BaseModel(pv.Model):
        @classmethod
        def upsert(cls, columns, **kwargs):
            query = (getattr(cls, columns[0]) == kwargs.get(columns[0]))
            for col in columns[1:]:
                query = query & (getattr(cls, col) == kwargs.get(col))
    
            records = set()
            for record in cls.select().where(query):
                for k, v in kwargs.items():
                    setattr(record, k, v)
                record.save()
                records.add(record)
    
            if len(records) == 0:
                cls.create(**kwargs)


@patarapolw -- I think you may misunderstand the point of `dataset`. The `dataset` library is strictly a convenience API -- although it exposes a lot of functionality, it is not intended to implement all the features you might expect to find using standard Peewee APIs.

Also, your upsert implementation is wide open to race conditions. Peewee has sophisticated support for upsert with SQLite 3.24.0 and newer. For older versions, you probably should use an exclusive lock:

```python
with db.atomic('exclusive'):
    ...
```

How should I upsert, then?

I have just googled and found these two 
- https://github.com/coleifer/peewee/issues/1307
- https://stackoverflow.com/questions/33485312/insert-or-update-a-peewee-record-in-python

Maybe,

    @classmethod
    def upsert(cls, columns, **kwargs):
        try:
            cls.insert(**kwargs).execute()
        except pv.IntegrityError:
            query = (getattr(cls, columns[0]) == kwargs.get(columns[0]))
            for col in columns[1:]:
                query = query & (getattr(cls, col) == kwargs.get(col))

            cls.update(kwargs).where(query).execute()

This one works, but race condition? I don't know.

The docs are a good place to look:

http://docs.peewee-orm.com/en/latest/peewee/querying.html#upsert

	rowid = (User
	         .insert(username='huey', last_login=now, login_count=1)
	         .on_conflict(
	             conflict_target=[User.username],  # Which constraint?
	             preserve=[User.last_login],  # Use the value we would have inserted.
	             update={User.login_count: User.login_count + 1})
	         .execute())

I can't use preserve or update clause in SQLite 3.22. Replace everything without preserve (`User.replace()`) raises:

     peewee.IntegrityError: NOT NULL constraint failed: patient.full_name

And I can't figure out a way to update Python3-SQLite on Mac. (I successfully updated the system one, though.)

> Peewee has sophisticated support for upsert with SQLite 3.24.0 and newer. For older versions, you probably should use an exclusive lock...
Issue: FTSModel associated to table with non-integer primary key
Author: jbriales
State: closed
Created: 2018-10-01 16:18:49

I'm working on a database for notes.
The Notes table uses a non-integer primary key with hash text ids.
I would like to do fulltext search on the notes, so I wanted to use the FTS functionality of SQLite, but I see that the main id there is an integer.

The typical peewee example proceeds like:
```
query = (Entry
         .select()
         .join(FTSEntry, on=(Entry.id == FTSEntry.docid))
         .where(FTSEntry.match('javascript AND canvas')))
```
But in my case the INTEGER docid does not correspond to the TEXT id.
What's the best approach to make a search query in this scenario?

There's no good reason to use a non-integer primary key in SQLite (unless you're also using `WITHOUT ROWID` in your table). SQLite's btree uses a 64-bit integer internally to store the rowid of a given row. If you use an integer primary key, then it will utilize that internal rowid automatically.

If you want to use a hash, just store the hash in a secondary column with a unique index.

> The Notes table uses a non-integer primary key with hash text ids.

I'm not sure what "hash text ids" means. You mean a md5 or something? An md5 is 16-bytes...you could maybe 8 bytes of the hash and treat them as a 64-bit integer... `struct.unpack('q', checksum.digest()[:8])` ('q' for signed, 'Q' for unsigned).

Lastly, you could create an additional column on your FTSEntry model which stores the hash, but note that this is a TERRIBLE idea and will require a FULL TABLE SCAN every single time.

Also it's silly to store the hash as `TEXT`...just store the bytes in a `BLOB` field.

Thanks for the detailed answer.
As I said, this is a third party database, so unfortunately I cannot modify those poor design decisions without breaking the depending task.

Under the constraints above (that is Note has primary field `id = TextField(null=True, primary_key=True)`), is it possible store the hashes in the virtual table (which I'm creating from scratch) like `uid = SearchField()`, then do the search on the virtual table, sth like
```
NoteIndex
        .select()
        .where(NoteIndex.match(search_string))
```
and join the corresponding rows of the main table via a join where Note.id == NoteIndex.uid? Would that keep efficient?

That's what I was saying in the last paragraph:

> Lastly, you could create an additional column on your FTSEntry model which stores the hash, but note that this is a TERRIBLE idea and will require a FULL TABLE SCAN every single time.

In the fts virtual table it is only efficient to join on the rowid/docid field of the virtual table. All other fields incur a full scan so you get O(n^2). Indexes have no effect on the virtual table. Possible? Yes. Performant? No, unless the table is small in which case you may not notice.
Issue: NameError: name 'EnclosedClause' is not defined
Author: jberkel
State: closed
Created: 2018-09-30 00:03:57

After upgrading to 3.x some FTS-related code stopped working:

```
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/Users/jan/projects/lexi/importer/src/test/python/test_search.py", line 8, in setUp
    self.reindex()
  File "/Users/jan/projects/lexi/importer/src/test/python/test_search.py", line 18, in reindex
    FTSHeadword.reindex()
  File "/Users/jan/projects/lexi/importer/src/main/python/lexi/models/fts.py", line 27, in reindex
    cls.optimize()
  File "/Users/jan/projects/lexi/importer/src/main/python/lexi/models/fts.py", line 55, in optimize
    return cls._fts_cmd('optimize')
  File "/Users/jan/projects/lexi/importer/build/virtual-env-python3/lib/python3.6/site-packages/playhouse/sqlite_ext.py", line 627, in _fts_cmd
    inner_clause = EnclosedClause(tbl)
NameError: name 'EnclosedClause' is not defined
```

Looks like `EnclosedClause` was removed in 3.x, but sqlite_ext still refers to it?

Oversight on my part -- fixed. Thanks for reporting!
Issue: Adds support for MySQL SSL connection kwargs
Author: jwilner
State: closed
Created: 2018-09-29 18:23:43

Addresses https://github.com/coleifer/peewee/issues/1742

To configure SSL connections with MySQL,`MySQLdb.connect` expects a dictionary of SSL params. Currently, `playhouse.db_url`  logic provides no way of parameterizing an SSL connection via the URI. This pull request apes `SQLAlchemy` by letting users specify nested ssl params in the query string with `ssl_*`. 

There should be no backwards compatibility issues, because `ssl_` prefixed query params would've previously had no effect.
Issue: support sqlalchemy-style MySQL URI parameters
Author: jwilner
State: closed
Created: 2018-09-29 18:01:31

The MySQL driver expects a dictionary of SSL parameters -- `{"cert": cert, "key": pkey, "ca": ca}`. SQLAlchemy supports these as URI components prefixed with `ssl` -- e.g. `?ssl_cert={cert}&ssl_key={pkey}&ssl_ca={ca}`. Currently, `playhouse.db_uri` has no comparable behavior, because although it parses out many types from the query string, it doesn't support nested objects.

I propose that `playhouse.db_uri` add a special case for the MySQL URIs which will support the same query params.

Basic idea would be that at the end of `parseresult_to_dict`, an additional postprocessing step would occur which would look like:
```python
if parsed.scheme == "mysql":
    _nest_by_prefix(connect_kwargs, prefix="ssl_", new_key="ssl")
``` 

I think I'm going to pass. MySQL-Connector uses different arguments: https://dev.mysql.com/doc/connector-python/en/connector-python-connectargs.html

I think this is going a bit above and beyond what the db url module was intended to do.
Issue: Add support for quoted creds
Author: jwilner
State: closed
Created: 2018-09-29 17:46:42

Addresses https://github.com/coleifer/peewee/issues/1740

- Maintains backwards compatibility by defaulting to current behavior
- Adds flag to all entry points.
- Tests default behavior is preserved but flag provides new behavior.

Merged an equivalent patch, thanks for providing this. I didn't think it was sensible to unquote the username so I removed that logic and changed the parameter to "unquote_password" (vs "unquote_creds").
Issue: `playhouse.db_uri.connect` doesn't support db passwords requiring escape
Author: jwilner
State: closed
Created: 2018-09-29 17:26:14

## the problem

To provide a password (or username) with characters unsafe for a URI, users must escape them to include them in the authority of a URI. However, the standard python password url parse logic -- `urllib.parse(uri).password` -- doesn't automatically unquote a password; clients of that code must . As it is, Peewee currently does not.

As a brief reproduction:
```bash
docker run \
    --name peewee-test \
    --rm \
    --detach \
    --publish=3306:3306 \
    --env "MYSQL_ROOT_PASSWORD=abcd#hijk" \ 
    --env "MYSQL_DATABASE=peewee-test" \
    mysql:5.7
```
and then:
```python
from playhouse.db_url import connect
from urllib.parse import quote

password = "abcd#hijk"

# ValueError within urllib.parse because parser reads '#' as start of fragment
connect(f"mysql://root:{password}@127.0.0.1:3306/peewee-test").connect()  

# access denied because not unquoted
connect(f"mysql://root:{quote(password)}@127.0.0.1:3306/peewee-test").connect()
```

Tested with peewee==3.7.0 FWIW.

## The proposal

Many libraries using URIs as connection string implicitly unquote the username and password. I suggest playhouse add the same ability while providing backward compatibility for those current users whose passwords  `unquote` might break (those who have the sequence which I imagine would be very very rare, but still).

```python
def connect(url, unquote_creds=False, **connect_params):
     pass
```

Merged an equivalent patch, with the difference being that it does not include logic to unquote the username -- just the password.
Issue: 3.x index renaming
Author: jberkel
State: closed
Created: 2018-09-29 13:55:40

I'm not sure if this is working as intended, but after upgrading to 3.x I get a different schema because of index renames.

I use `table_name` to set the table name, but the index name seems to use the automatic table name as prefix, e.g.

```python
class Foo
    some_text = TextField(index=True)

    class Meta
        table_name = "foos"
````

results in `CREATE INDEX "foo_some_text" ON "foos" ("some_text")` and not `CREATE INDEX "foos_some_text" ON "foos" ("some_text")` as it did previously.

I'm not seeing that, actually. I added a testcase which to me validates that the behavior in 3.x respects the table name as the prefix.

47170c381329057f26ee5fb121eef54dbb7944e3

Thanks for investigating, found the issue. I wasn't using `legacy_table_names = False`.

When using legacy table names, Peewee will prefix the index with the model name, irrespective of the actual table name.
Issue: Incorrect sql generated by custom field
Author: fy0
State: closed
Created: 2018-09-27 15:33:52

My database is PostgreSQL.

Field:
```
class INETField(peewee.Field):
    db_field = 'inet'
```

Model:
```
    reputation = IntegerField(default=0)
    ip_registered = INETField(default=None, null=True)
```

Error:
```
    cursor.execute(sql, params or ())
peewee.ProgrammingError: syntax error at or near ","
LINE 1: ...L, "reputation" INTEGER NOT NULL, "ip_registered" , "reset_k...                    
```

type, default and "not null" lost.
An old version: 3.5.2
Upgraded to 3.7.0, it still happened.

Is it a bug or some thing wrong with my code?

In 3.x you need to use `field_type` instead of `db_field`. The docs are pretty clear:

http://docs.peewee-orm.com/en/latest/peewee/models.html#creating-a-custom-field

@coleifer Sorry, in fact I had read the document before, but copy & paste my old code in the same file when I programming...
Issue: `sqlall()`gone missing in 3.x
Author: jberkel
State: closed
Created: 2018-09-26 23:08:19

I'm just migrating a bunch of code from 2.x to 3.x. Nice job! My code previously used  `Model.sqlall()` to create a file with the current SQL schema. This method is gone in 3.x, and I don't see a replacement for it. I could easily reproduce this with some extra lines of code but I still think it's a worthwhile method to have, if only for quick debugging of schemas.

> I could easily reproduce this with some extra lines of code

Go for it.

Updated the changes documentation to reflect the removal of this. Apologies for the confusion.
Issue: How to use TO_CHAR function
Author: Maheshkumar094
State: closed
Created: 2018-09-22 14:30:21

@coleifer 
How to use postgress TO_CHAR function in peewee
In native sql query 
`SELECT id, TO_CHAR(created_at at time zone 'Asia/Kolkata','DD-MON-YYYY') as date  from txn order by id desc limit 5`
-------------------------------- result ------------------------------
 **id              date**
131;  "19-SEP-2018"
130;   "19-SEP-2018"
129;   "19-SEP-2018"
128;   "19-SEP-2018"
127;   "13-SEP-2018"

In peewee
```
class Txn(BaseModel):
    created_at = DateTimeField(null=True)
    class Meta:
        table_name = 'txn'
        schema = 'public'
```
what I figure out 


```
def getprvtxn(passenger_id):
    try:
        query_get_txn_his = Txn.select(
            Txn.id,
            fn.TO_CHAR(Txn.created_at,"at time zone'Asia/Kolkata'DD-MON-YYYY HH12:MIPM").alias('date')
        ).where(Txn.passenger == passenger_id).order_by(Txn.created_at).limit(30)
        print(list(query_get_txn_his.dicts()))        
        print("SQL ={}".format(query_get_txn_his.sql()))
        if query_get_txn_his.__sizeof__() > 0 and bool(query_get_txn_his):
            for result in query_get_txn_his:
                print result.id
                print(result.date)
    except Exception as e:
        cnf.is_error = True
        cnf.statuscode = cnf.Internal_server_Error
        tb.print_exc()
        return None

```
--------------------------- Peewee Result ------------------------
```
id = 16 date = at t8me zone'As8a/Kolkata'17-AUG-2018 02:29PM
id = 17 date = at t8me zone'As8a/Kolkata'17-AUG-2018 02:29PM
id = 32 date = at t8me zone'As8a/Kolkata'20-AUG-2018 12:56PM
id = 33 date = at t8me zone'As8a/Kolkata'20-AUG-2018 01:02PM
id = 34 date = at t8me zone'As8a/Kolkata'20-AUG-2018 01:13PM
id = 35 date = at t8me zone'As8a/Kolkata'20-AUG-2018 01:14PM
id = 36 date = at t8me zone'As8a/Kolkata'20-AUG-2018 01:14PM
id = 37 date = at t8me zone'As8a/Kolkata'20-AUG-2018 01:16PM
id = 38 date = at t8me zone'As8a/Kolkata'20-AUG-2018 02:20PM
id = 39 date = at t8me zone'As8a/Kolkata'20-AUG-2018 02:38PM


```
You notice that **_at t8me zone'As8a/Kolkata'_** is  append with **20-AUG-2018 02:38PM** which not the desire output. Plase me with correct soluntion and correct way to use postgress function in peewee:) 





To translate:

```sql
TO_CHAR(created_at at time zone 'Asia/Kolkata','DD-MON-YYYY') as date
```

You'll probably need to use NodeList:

```python
expr = NodeList((Txn.created_at, SQL('at time zone'), 'Asia/Kolkata'))
date_expr = fn.TO_CHAR(expr, 'DD-MON-YYYY')

...select(Txn.id, date_expr.alias('date'))
```

Tnx:)
Issue: Psycopg2 warning
Author: yabirgb
State: closed
Created: 2018-09-21 19:49:24

I get this warning when running peewee with postgresql:

`UserWarning: The psycopg2 wheel package will be renamed from release 2.8; in order to keep installing from binary please use "pip install psycopg2-binary" instead. For details see: <http://initd.org/psycopg/docs/install.html#binary-install-from-pypi>.`

If I uninstall `psycopg2` and install `psycopg2-binary` I get the error `peewee.ImproperlyConfigured: Postgres driver not installed!`

That doesn't sound correct at all. `psycopg2-binary` should install a psycopg2 package normally. It's just a binary package rather than a source package.

See:

```
$ pip freeze | grep psycopg2
65:psycopg2-binary==2.7.5

$ ipython
Python 3.7.0 (default, Jul 15 2018, 18:17:02) 
Type 'copyright', 'credits' or 'license' for more information
IPython 6.4.0 -- An enhanced Interactive Python. Type '?' for help.

In [1]: from peewee import PostgresqlDatabase

In [2]: db = PostgresqlDatabase('peewee_test')

In [3]: db.connect()
Out[3]: True
```

Ok thanks! I will check then what other thing could be failing. Sorry for the inconvenience
Issue: id field value is None
Author: jbriales
State: closed
Created: 2018-09-21 13:05:41

I'm trying to get the id field of a SQLite table but I always get None:
# Model:
```
class Document(BaseModel):
    id = IntegerField(db_column='id', primary_key=True, null=False, index=True)

    class Meta:
        db_table = 'Documents'
```
# Query:
```
query = Document.select()
    with db.atomic():
        for doc in query:
            print(doc.id)
```
The id field exists in the table as
`id` INTEGER PRIMARY KEY AUTOINCREMENT

Uh... I know for a fact that something as fundamental as retrieving a column value from a select query is working -- it's covered quite thoroughly in the tests.

Perhaps you can provide a test-case or complete script so I can replicate the issue?

Ok, you are fully right. Your mention of tests gave me an idea.
I tried tests and a simpler toy example and it revealed I had done some (apparently) small change inside peewee which however provoked this malfunctioning...
Working fine now! Sorry for the false alarm :)
Issue: sqlite database stores 'true'/'false' in INT type
Author: jbriales
State: closed
Created: 2018-09-21 11:08:50

I'm using peewee to handle the database stored by the Mendeley app.
It turns out in its database Mendeley defines binary fields as INT but populate its values with 'true'/'false' strings.
As a result, when reading this database through the models automatically imported by pwiz (which parses these fields as 'IntegerField'), the program throws an exception as it tries to cast `int('true')`.
I'm not very familiar with databases, so I don't know if this that Mendeley does is a very unusual behavior, or why wouldn't they just store 0/1, but this is out of my control. So I was wondering which could be the best approach to deal with this issue.
Should I force the `IntegerField.adapt` method to check if the string is 'true' or 'false' in case of exception? This seems a little invasive though wrt peewee, that's why I ask for advice here :)

> I'm not very familiar with databases, so I don't know if this that Mendeley does is a very unusual behavior, or why wouldn't they just store 0/1, but this is out of my control. So I was wondering which could be the best approach to deal with this issue.

This is very bad behavior, in my opinion.

You can do one of several things:

* Update the tables, e.g. `update my_table set my_col = case when my_col='true' then 1 else 0 end;`
* Implement a custom field type that correctly handles the strings "true" / "false"

There's no excuse for monkey-patching `IntegerField.adapt` in my opinion...just subclass. It's Python.
Issue: Match FLOAT  and CHAR[...] in sqlite reflection.
Author: jbriales
State: closed
Created: 2018-09-21 09:02:40

Fix issue related to https://github.com/coleifer/peewee/issues/1730#issuecomment-423461785

So SQLite only supports the following data-types:

* TEXT
* BLOB
* INTEGER
* REAL
* NULL

I think I'm going to pass on the square bracket change, but will add support for float along with some others:

d31e7bf972e69e227c10694fac71b966959e715f
Issue: VARCHAR not correctly parsed in pwiz
Author: jbriales
State: closed
Created: 2018-09-20 08:12:23

Hi,
I was using pwiz in python3 with sqlite and it seems VARCHAR fields are not correctly parsed:

If no max length is provided, the field is not recognized:
```
CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           VARCHAR NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        TEXT,
   SALARY         REAL
);
```
```
class Company(BaseModel):
    address = TextField(db_column='ADDRESS', null=True)
    age = IntegerField(db_column='AGE')
    id = PrimaryKeyField(db_column='ID')
    name = UnknownField(db_column='NAME')  # VARCHAR
    salary = FloatField(db_column='SALARY', null=True)

    class Meta:
        db_table = 'COMPANY'
```

If max length is provided, I don't get it in models.py:
```
CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           VARCHAR(50) NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        TEXT,
   SALARY         REAL
);
```
```
class Company(BaseModel):
    address = TextField(db_column='ADDRESS', null=True)
    age = IntegerField(db_column='AGE')
    id = PrimaryKeyField(db_column='ID')
    name = CharField(db_column='NAME')
    salary = FloatField(db_column='SALARY', null=True)

    class Meta:
        db_table = 'COMPANY'
```


Thanks, I've fixed the issue where VARCHAR without a length constraint is now recognized correctly with Sqlite -- fdeb92c1639c1dc9301ae501b95e36d83a63ee74

Regarding the max_length parameter, this has no effect in Sqlite and so it is fine to ignore it:

```sql
sqlite> create table kv (key varchar(4) not null primary key, value varchar(8) not null);
sqlite> insert into kv (key, value) values ('abcdefghij', '12345678901234567890');
sqlite> select * from kv;
abcdefghij|12345678901234567890
```

Thanks for the fix, it's working now :)
However I still found two failing cases in my database:
`# CHAR[38]`
`# FLOAT`
I will make a pull request next.

Eh.. You know SQLite only supports the following data-types, right?

* TEXT
* BLOB
* INTEGER
* REAL
* NULL

Uhm... just starting with SQL so... why would this database I'm working with have those other fields? Is it maybe not actually SQLite? (although reading/writing to it as a SQLite works)

SQLite is permissive about what it accepts, but internally it only knows about the data-types I mentioned.
Issue: Different syntax for python2 and python3
Author: jbriales
State: closed
Created: 2018-09-20 07:51:50

Hi, I'm just starting to use peewee and pwiz, and it took a while to figure out this error:
TypeError: __init__() got an unexpected keyword argument 'column_name'
Apparently the syntax is different in the python2 and python3 versions,
so I got different results calling:
`python2 -m pwiz -e sqlite "testDB.sqlite" > testmodels.py`
`python3 -m pwiz -e sqlite "testDB.sqlite" > testmodels.py`

I installed both versions via pip.

Uhh, it sounds like in one case you are running an old version of Peewee.

You can verify by running

```
python2 -c "import peewee; print(peewee.__version__)"
python3 -c "import peewee; print(peewee.__version__)"
```