CockroachDB

[ghost]

hi I use peewee with cockroachdb. worked great except a thing
INSERT INTO "gs" ('id', 'name') VALUES (379589221701222401, 'yoshi') ON CONFLICT ("id") DO UPDATE SET "name" = EXCLUDED."name" RETURNING "id"

the cockroach wants INSERT INTO "gs" (id, name) ... without the ' around id and name
is it something to fix on the peewee side or on the cockroach side?

[coleifer]

I don't think you're quite right that Peewee is using single-quotes around 'id' and 'name'. Peewee would presumably be using double-quotes.

Ex:

In [1]: from peewee import *

In [2]: db = PostgresqlDatabase('peewee_test', user='postgres')

In [3]: class GS(Model):
   ...:     name = TextField()
   ...:     class Meta:
   ...:         database = db
   ...:         

In [4]: GS.insert(id=12345, name='yoshi').on_conflict(conflict_target=[GS.id], preserve=[GS.name]).sql()
Out[4]: 
('INSERT INTO "gs" ("id", "name") VALUES (%s, %s) ON CONFLICT ("id") DO UPDATE SET "name" = EXCLUDED."name" RETURNING "id"',
 [12345, 'yoshi'])

So I'm not sure if you have just mistyped your issue or what?

[coleifer]

Could you paste the exact error you are receiving from CockroachDB?

Do normal INSERTs work (without the ON CONFLICT clause)?

[coleifer]

I just set up a Cockroach cluster on my computer and it seems to work fine:

In [1]: from peewee import *

In [2]: db = PostgresqlDatabase('peewee_test', user='root', port=26257)

In [3]: db.connect()
Out[3]: True

In [4]: class KV(Model):
   ...:     key = TextField(unique=True)
   ...:     value = TextField()
   ...:     class Meta:
   ...:         database = db
   ...:         

In [5]: KV.create_table()

In [6]: k1=KV.create(key='k1', value='v1')

In [7]: k1
Out[7]: <KV: 381083568020553729>

In [8]: KV.insert(key='k1', value='v1-x').on_conflict(conflict_target=[KV.key], preserve=[KV.value])
Out[8]: <peewee.ModelInsert at 0x7f0bb8bfc080>

In [9]: KV.insert(key='k1', value='v1-x').on_conflict(conflict_target=[KV.key], preserve=[KV.value]).execute()
Out[9]: 381083568020553729

In [10]: list(KV)
Out[10]: [<KV: 381083568020553729>]

In [11]: KV.get(KV.key == 'k1').value
Out[11]: 'v1-x'

[ghost]

my query is

GSModel.insert_many(data, fields=['id', 'name']
                        ).on_conflict(
             conflict_target=[GSModel.id], 
             preserve=[GSModel.name],
             update={}).execute()

and in pycharm i see this:

.../lib/python3.7/site-packages/peewee.py", line 2640, in execute_sql
    cursor.execute(sql, params or ())
peewee.ProgrammingError: syntax error at or near "id"
DETAIL:  source SQL:
INSERT INTO "gs" ('id', 'name') VALUES (379589221701287937, 'afghans'), (379589221701320705, 'agar-agar') ON CONFLICT ("id") DO UPDATE SET "name" = EXCLUDED."name" RETURNING "id"

[ghost]

if I print sql():

('INSERT INTO "gs" (%s, %s) VALUES (%s, %s), (%s, %s) ON CONFLICT ("id") DO UPDATE SET "name" = EXCLUDED."name" RETURNING "id"', ['id', 'name', 379589221701353473, 'albatre', 379589221701386241, 'albumine'])

[coleifer]

Your columns are being treated as if they were strings. You need to use the field instances themselves:

GSModel.insert_many(data, fields=[GSModel.id, GSModel.name])...

[coleifer]

I've fixed the logic in bulk INSERT so that it is now possible to provide the list of fields as a list of strings -- this makes things a bit more flexible and should prevent anyone from shooting themselves in the foot in the future. For what it's worth, the docs are pretty clear that you should be specifying field instances as opposed to strings:

• http://docs.peewee-orm.com/en/latest/peewee/querying.html#bulk-inserts
• http://docs.peewee-orm.com/en/latest/peewee/api.html#Model.insert_many

[ghost]

originally I was using the instances but switched to strings because of this:

lib/python3.7/site-packages/peewee.py", line 2226, in _generate_insert
    raise ValueError('Missing value for "%s".' % column)
ValueError: Missing value for "<IntegerField: GSModel.id>".

[ghost]

ok I found out my mistake...
mixing dicts with fields= was the root cause of my problem today... I needed to use tuples.
it gave me the Missing value err ^.... then I switched to strings to get rid of the error , only to get the insert error later I posted as issue.

# don't do this:
data_source = [
    {'field1': 'val1-1', 'field2': 'val1-2'},
    {'field1': 'val2-1', 'field2': 'val2-2'},
    # ...
]
MyModel.insert_many(data_source, fields=[MyModel.field1, MyModel.field2]).execute()

[coleifer]

Yeah, if you specify both a list of dicts and the fields parameter, then the fields need to match the keys of the dictionary -- which in your case caused trouble because the keys were strings and the fields were objects.