Issue: get_or_create failing
Author: ra-esmith
State: closed
Created: 2018-05-23 11:02:16

Hello,

So millions of times get_or_create works fine, but sometimes I am crashing on 
what is a legal select statement !  Using newest peewee ...

As a test would like to try the get and create manually, but am unsure how to write that now.

Here are some of what I have found.  The table t1 has a primary key named "code".
There is no "id" defined on the table.
AND if I run this query select below in psql it works fine ... but for some reason peewee crashes
with it ...

Naturally I have a dictionary with the value {'code': 'LR091816'}  
How would I do a select manually in peewee passing in this dictionary, figure it would be a good test.
I tried model.select().where(d) but that did not seem to work.

Here is the log ...

('SELECT "t1"."code" FROM "game" AS "t1" WHERE ("t1"."code" = %s) LIMIT %s OFFSET %s', [u'LR091816', 1, 0])
      * get create failed!
Traceback (most recent call last):
  File "/home/anon/Desktop/test/precast_ingest_util.py", line 146, in create_or_increment
    model.get_or_create(**kwargs) # the select fails
  File "/home/anon/miniconda2/lib/python2.7/site-packages/peewee.py", line 5344, in get_or_create
    return query.get(), False
  File "/home/anon/miniconda2/lib/python2.7/site-packages/peewee.py", line 5705, in get
    return clone.execute()[0]
  File "/home/anon/miniconda2/lib/python2.7/site-packages/peewee.py", line 1522, in inner
    return method(self, database, *args, **kwargs)
  File "/home/anon/miniconda2/lib/python2.7/site-packages/peewee.py", line 1593, in execute
    return self._execute(database)
  File "/home/anon/miniconda2/lib/python2.7/site-packages/peewee.py", line 1747, in _execute
    cursor = database.execute(self)
  File "/home/anon/miniconda2/lib/python2.7/site-packages/peewee.py", line 2590, in execute
    return self.execute_sql(sql, params, commit=commit)
  File "/home/anon/miniconda2/lib/python2.7/site-packages/peewee.py", line 2584, in execute_sql
    self.commit()
  File "/home/anon/miniconda2/lib/python2.7/site-packages/peewee.py", line 2377, in __exit__
    reraise(new_type, new_type(*exc_args), traceback)
  File "/home/anon/miniconda2/lib/python2.7/site-packages/peewee.py", line 2577, in execute_sql
    cursor.execute(sql, params or ())
InternalError: current transaction is aborted, commands ignored until end of transaction block

current transaction is aborted, commands ignored until end of transaction block

{'code': 'LR091816'}



Ok so the GET if I just do that fails!!  But why?  I can run this query by hand in psql ...
There are other fields in the table, here they are, but this record does exist, wht this value for code.


doing a select explicitly
('SELECT "t1"."code", "t1"."allstars", "t1"."average_resale_factor", "t1"."away_losses", "t1"."away_pitcher_rating", "t1"."away_points", "t1"."away_wins", "t1"."canceled", "t1"."count_resales", "t1"."count_scans", "t1"."count_tickets", "t1"."days_since_last_game", "t1"."days_until_next_game", "t1"."division", "t1"."family", "t1"."games_left", "t1"."home_losses", "t1"."home_pitcher_rating", "t1"."home_points", "t1"."home_win_percent", "t1"."home_win_record", "t1"."home_wins", "t1"."kd_playing", "t1"."national_holiday", "t1"."national_tv", "t1"."nearest_storm", "t1"."opponent_id", "t1"."precipitation", "t1"."precipitation_intensity", "t1"."prediction_override", "t1"."promotional", "t1"."religious_holiday", "t1"."rw_playing", "t1"."star_multiplier", "t1"."team_id", "t1"."team_score", "t1"."temperature", "t1"."time", "t1"."meta_sold_seats_number", "t1"."meta_available_seats_number", "t1"."meta_total_num_seats_stadium", "t1"."meta_available_seats_total_cost" FROM "game" AS "t1" WHERE ("t1"."code" = %s) LIMIT %s OFFSET %s', [u'LR091816', 1, 0])
were not able to get it
Traceback (most recent call last):
  File "/home/anon/Desktop/ra/precast-master/precast_ingest_util.py", line 139, in create_or_increment
    foo = model.get(model.code == 'LR091816')
  File "/home/anon/miniconda2/lib/python2.7/site-packages/peewee.py", line 5311, in get
    return sq.get()
  File "/home/anon/miniconda2/lib/python2.7/site-packages/peewee.py", line 5705, in get
    return clone.execute()[0]
  File "/home/anon/miniconda2/lib/python2.7/site-packages/peewee.py", line 1522, in inner
    return method(self, database, *args, **kwargs)
  File "/home/anon/miniconda2/lib/python2.7/site-packages/peewee.py", line 1593, in execute
    return self._execute(database)
  File "/home/anon/miniconda2/lib/python2.7/site-packages/peewee.py", line 1747, in _execute
    cursor = database.execute(self)
  File "/home/anon/miniconda2/lib/python2.7/site-packages/peewee.py", line 2590, in execute
    return self.execute_sql(sql, params, commit=commit)
  File "/home/anon/miniconda2/lib/python2.7/site-packages/peewee.py", line 2584, in execute_sql
    self.commit()
  File "/home/anon/miniconda2/lib/python2.7/site-packages/peewee.py", line 2377, in __exit__
    reraise(new_type, new_type(*exc_args), traceback)
  File "/home/anon/miniconda2/lib/python2.7/site-packages/peewee.py", line 2577, in execute_sql
    cursor.execute(sql, params or ())
InternalError: current transaction is aborted, commands ignored until end of transaction block



in psql on this db 
the following returns a record

 SELECT "t1"."code", "t1"."allstars", "t1"."average_resale_factor", "t1"."away_losses", "t1"."away_pitcher_rating", "t1"."away_points", "t1"."away_wins", "t1"."canceled", "t1"."count_resales", "t1"."count_scans", "t1"."count_tickets", "t1"."days_since_last_game", "t1"."days_until_next_game", "t1"."division", "t1"."family", "t1"."games_left", "t1"."home_losses", "t1"."home_pitcher_rating", "t1"."home_points", "t1"."home_win_percent", "t1"."home_win_record", "t1"."home_wins", "t1"."kd_playing", "t1"."national_holiday", "t1"."national_tv", "t1"."nearest_storm", "t1"."opponent_id", "t1"."precipitation", "t1"."precipitation_intensity", "t1"."prediction_override", "t1"."promotional", "t1"."religious_holiday", "t1"."rw_playing", "t1"."star_multiplier", "t1"."team_id", "t1"."team_score", "t1"."temperature", "t1"."time", "t1"."meta_sold_seats_number", "t1"."meta_available_seats_number", "t1"."meta_total_num_seats_stadium", "t1"."meta_available_seats_total_cost" FROM "game" AS "t1" WHERE ("t1"."code" = 'LR091816') LIMIT 1 OFFSET 0;


Ok so a pee wee sleet for this code works fine, but the get fails?

    try:
        print("doing a select explicitly  fails")
        Game.select()
        print("get one code explicit select") # this works
        foo = Game.select().where(Game.code == 'LR091816')
        print(foo)
        
        print("get one code explicit get") # ths fails !!
        foo = Game.get(Game.code == 'LR091816')
        print(foo)
        
        print("doing a get with passed in model explicitly  fails")
        foo = model.get(model.code == 'LR091816')
        print("\nlets try getting it with model.get and passing in dic")
        z = model.get(**kwargs)
        print(z)
    except:
        print("were not able to get it")
        traceback.print_exc()                
        exit()


Consider
get one code explicit select
<peewee.ModelSelect object at 0x7f3544100890>
get one code explicit get
('SELECT "t1"."code", "t1"."allstars", "t1"."average_resale_factor", "t1"."away_losses", "t1"."away_pitcher_rating", "t1"."away_points", "t1"."away_wins", "t1"."canceled", "t1"."count_resales", "t1"."count_scans", "t1"."count_tickets", "t1"."days_since_last_game", "t1"."days_until_next_game", "t1"."division", "t1"."family", "t1"."games_left", "t1"."home_losses", "t1"."home_pitcher_rating", "t1"."home_points", "t1"."home_win_percent", "t1"."home_win_record", "t1"."home_wins", "t1"."kd_playing", "t1"."national_holiday", "t1"."national_tv", "t1"."nearest_storm", "t1"."opponent_id", "t1"."precipitation", "t1"."precipitation_intensity", "t1"."prediction_override", "t1"."promotional", "t1"."religious_holiday", "t1"."rw_playing", "t1"."star_multiplier", "t1"."team_id", "t1"."team_score", "t1"."temperature", "t1"."time", "t1"."meta_sold_seats_number", "t1"."meta_available_seats_number", "t1"."meta_total_num_seats_stadium", "t1"."meta_available_seats_total_cost" FROM "game" AS "t1" WHERE ("t1"."code" = %s) LIMIT %s OFFSET %s', [u'LR091816', 1, 0])
were not able to get it


perhaps get is not an API I should use, was only doing it for testing
will go back to select and see if I can implement get_or_create with explicity calls

i pass in my model (table name) as a variable to a def
along with kwargs

I tried
model.select().where(**kwargs)

but that does not expand kwargs into
code == 'foo'

how do I convert my kwargs into a where expr?


if I do a simple .create then the records are created

so it seem that the .get is failing to detect if record is present ?

AHH 

so when I call get_or_create I am NOT passing in an exression A = B
instead I have a dictionary A:B, ...
that I pass in

Does the aPI no longer support this?  Perhaps it never did?

How do I convert from a dictoinary to the expression get_or_create requires?

I have been passing in a dictionary
using **dict to get_or_create

and that hsould have worked ...?


oh but the create wants a =  while the get wants a ==

I wonder how that works out internally to peewee

so the basic first problem is the get_or_create call

why it is finding some "other" id other than the id of my the record I want to look at it is just a puzzle (the error).  peewee fails on this dupcliate key violation, when it trys to create a new record using an existing id for a record that should never have been looked at/modified in the table!!

leaving me with the desire to try doing this all by hand to see if it would work ... but not knowing how to write the select (or update) passing in a dicdtionary of values as I don't know how to write the were expression in python from a dictionary/hash.

Can you clarify exactly what problem you'd like help solving? I'm having a hard time following your comments.

For what it's worth, `get_or_create` accepts keyword arguments and works on that basis. The `InternalError` you're seeing indicates that something like the following has occurred:

1. An insert/delete/update triggered an integrity error.
2. You did not explicitly rollback / commit
3. A select was issued subsequent to the integrity error and is failing now.

The simplest implementation for get-or-create looks like this:

```python

# First try to create it. Rely on an integrity error being raised to determine
# that the object exists.
try:
    with MyModel._meta.database.atomic():
        obj = MyModel.create(...)
except IntegrityError:
    obj = MyModel.get(...)
```

If you wish to try and do the select first:

```python
try:
    obj = MyModel.get(...)
except MyModel.DoesNotExist:
    try:
        with MyModel._meta.database.atomic():
            obj = MyModel.create(...)
    except IntegrityError:
        obj = MyModel.get(...)
```

Hello,

Sure.

Given a hash/dictionary in python like
x = {"a":1, "b":2}

How can i write a select query in python, given only x as input, where I want to just
get records that have a == 1 and b == 2
so the query
model.select().where(x)
which translates into
model.select().where(model.a == 1, model.b == 2)

If I can do that I can debug what is going on I think.

Thanks!
evan

Sent with [ProtonMail](https://protonmail.com) Secure Email.

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On May 23, 2018 9:59 AM, Charles Leifer <notifications@github.com> wrote:

> Can you clarify exactly what problem you'd like help solving? I'm having a hard time following your comments.
>
> —
> You are receiving this because you authored the thread.
> Reply to this email directly, [view it on GitHub](https://github.com/coleifer/peewee/issues/1611#issuecomment-391357055), or [mute the thread](https://github.com/notifications/unsubscribe-auth/AXFzJr7UYTwcNnd2VKW9qcpDDyTYy3Tvks5t1WskgaJpZM4UKOxE).

If I can do a select then I would also like to do an update and a create using the same paradigm.  Basically write get_or_create by hand.   And then see where it crashes in my code .

Thanks!
Evan

Sent with [ProtonMail](https://protonmail.com) Secure Email.

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On May 23, 2018 12:07 PM, evan <evan.charles.smith@protonmail.com> wrote:

> Hello,
>
> Sure.
>
> Given a hash/dictionary in python like
> x = {"a":1, "b":2}
>
> How can i write a select query in python, given only x as input, where I want to just
> get records that have a == 1 and b == 2
> so the query
> model.select().where(x)
> which translates into
> model.select().where(model.a == 1, model.b == 2)
>
> If I can do that I can debug what is going on I think.
>
> Thanks!
> evan
>
> Sent with [ProtonMail](https://protonmail.com) Secure Email.
>
> ‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
> On May 23, 2018 9:59 AM, Charles Leifer <notifications@github.com> wrote:
>
>> Can you clarify exactly what problem you'd like help solving? I'm having a hard time following your comments.
>>
>> —
>> You are receiving this because you authored the thread.
>> Reply to this email directly, [view it on GitHub](https://github.com/coleifer/peewee/issues/1611#issuecomment-391357055), or [mute the thread](https://github.com/notifications/unsubscribe-auth/AXFzJr7UYTwcNnd2VKW9qcpDDyTYy3Tvks5t1WskgaJpZM4UKOxE).

If you want to use keyword-arguments for your where clause (which is gross but OK) you can use [filter()](http://docs.peewee-orm.com/en/latest/peewee/api.html#Model.filter). For example:

```python
class User(Model):
    username = TextField()

user_obj = User.filter(username='charlie').get()

# Or, multiple objects:
users = User.filter(username__in=['charlie', 'huey', 'mickey'])
for user in users:
    print(user.username)
```

It's better to use expressions, though. You can very easily convert a dictionary into the appropriate expressions by using `Model._meta.fields`, which is a mapping of field-name -> field object.

I think your code is "crashing" because of an un-rolled-back integrity error that occurs. You mention you're using Postgres -- how are you instantiating your database?

I am using Postgres.  And have several apps hitting it.  I just do a create database x

There are lots of constraints between my tables.  Indeed if a table a was behind not available that would be a problem

But when I just alone try to get or create these records it fails to work.  The records do not get created.   However the raw create call will work creating the records.

A select will fail to return the record of done before create.

A get will fail but complain about some other record Id

Evan

You know that get_or_create and related apis are all covered by thorough tests. If there were something fundamentally broken these tests would fail. My point is that the code works. As to why you're having issues, I don't know but I suspect the problem is not peewee.

Hello,

Oh I am sure that pee wee is not the problem.

I am thinking that perhaps
I crash somewhere and then postgres is not responding.

Do I need to wrap calls in an atomic or commit/rollback in the event that a call fails?

The problem is more that I upgraded and have not figured out something that changed.  Or I am getting data in a bad order (my current thought).

So perhaps I do
a get_create or some other sql call - which fails.  But do not rollback?

And then later do a
get_or_create which fails be cause postgres is in a bad state?

Trying to come up with some theory ...

Evan

Sent with [ProtonMail](https://protonmail.com) Secure Email.

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On May 23, 2018 2:01 PM, Charles Leifer <notifications@github.com> wrote:

> You know that get_or_create and related apis are all covered by thorough tests. If there were something fundamentally broken these tests would fail. My point is that the code works. As to why you're having issues, I don't know but I suspect the problem is not peewee.
>
> —
> You are receiving this because you authored the thread.
> Reply to this email directly, [view it on GitHub](https://github.com/coleifer/peewee/issues/1611#issuecomment-391442617), or [mute the thread](https://github.com/notifications/unsubscribe-auth/AXFzJo4Wgd3LurhlXNS7ny1Ekiyr9Pjqks5t1aQAgaJpZM4UKOxE).

Hello,

I have about a dozen very large applications running peewee, and there is no problem.  They are all very similar.

It is just this new app (two of them actually) that is having some issue.  So the "data" coming in and then my code is the issue.  The integrity issue is certainly a great one for me to look at.  Just not sure how to find a example of where that happens.

My code loads lots of csv files, which are all interdependent but noisy - so constraints sometimes do not get satisfied.

If one of the inserts (or updates) "fails" with peewee.  do I need to do anything to get postgres (or peewee) to continue to work for the next unrelated insert/update?

Evan

Sent with [ProtonMail](https://protonmail.com) Secure Email.

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On May 23, 2018 2:22 PM, evan <evan.charles.smith@protonmail.com> wrote:

> Hello,
>
> Oh I am sure that pee wee is not the problem.
>
> I am thinking that perhaps
> I crash somewhere and then postgres is not responding.
>
> Do I need to wrap calls in an atomic or commit/rollback in the event that a call fails?
>
> The problem is more that I upgraded and have not figured out something that changed.  Or I am getting data in a bad order (my current thought).
>
> So perhaps I do
> a get_create or some other sql call - which fails.  But do not rollback?
>
> And then later do a
> get_or_create which fails be cause postgres is in a bad state?
>
> Trying to come up with some theory ...
>
> Evan
>
> Sent with [ProtonMail](https://protonmail.com) Secure Email.
>
> ‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
> On May 23, 2018 2:01 PM, Charles Leifer <notifications@github.com> wrote:
>
>> You know that get_or_create and related apis are all covered by thorough tests. If there were something fundamentally broken these tests would fail. My point is that the code works. As to why you're having issues, I don't know but I suspect the problem is not peewee.
>>
>> —
>> You are receiving this because you authored the thread.
>> Reply to this email directly, [view it on GitHub](https://github.com/coleifer/peewee/issues/1611#issuecomment-391442617), or [mute the thread](https://github.com/notifications/unsubscribe-auth/AXFzJo4Wgd3LurhlXNS7ny1Ekiyr9Pjqks5t1aQAgaJpZM4UKOxE).

> If one of the inserts (or updates) "fails" with peewee.  do I need to do anything to get postgres (or peewee) to continue to work for the next unrelated insert/update?

Yes, you'd need to rollback. e.g. `db.rollback()`

I'm going to close this... this issue is out-of-control. If you can create a reproducible *short* script that I can use to reproduce the failure, I'm happy to look into this again.

Ok