CODEWITHSUNDEEP
pythonmysqlpython-3.xormpeewee
Shan-x
Shan-x

Reputation: 1176

Peewee, MySQL and INSERT IGNORE

I use Peewee as ORM in a little Python script with a MySQL DB.

#!/usr/bin/python3
#coding: utf-8

import peewee
from peewee import *

db = MySQLDatabase(**config)
class Foo(peewee.Model):
    bar = peewee.CharField(unique=True, null=False)
    class Meta:
        database = db

try:
    Foo.create_table()
except:
    pass

foo_data = [{'bar':'xyz'},{'bar':'xyz'}]
Foo.insert_many(foo_data).on_conflict(action='IGNORE').execute()

As you can see, I have the same key. I'd like to ignore it the second time using the on_conflict method (described in the API reference, but only for SQLite3), but I have this error when running the script (normal, because not implemented for MySQL):

peewee.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OR IGNORE INTO `foo` (`bar`) VA' at line 1")

If I remove the .on_conflict(action='IGNORE'), MySQL doesn't like it either (duplicate key). How can I make peewee insert a new key or ignore it if it's a duplicate key?

Upvotes: 2

Views: 2613

Answers (1)

gms
gms

Reputation: 335

use db.__str__(). It returns 

<peewee.MySQLDatabase object at 0x7f4d6a996198>

if the connection database is MySQL and 

<peewee.SqliteDatabase object at 0x7fd0f4524198>

if the connection database is Sqlite.

So you can use an if statement like:

if 'SqliteDatabase' in db.__str__():
    Foo.insert_many(foo_data).on_conflict(action='IGNORE').execute()
elif 'MySQLDatabase' in db.__str__():
    try:
        Foo.insert_many(foo_data).execute() # or whatever you want with MySQL
    except:
        pass

I think for the MySQL database you can do something like this:

for data in foo_data:
    for k,v in data.items():
        if (Foo.select(Foo.bar).where(Foo.bar == v).count()) == 0:
            Foo.insert(bar=v).execute()

So this can be:

if 'SqliteDatabase' in db.__str__():
    Foo.insert_many(foo_data).on_conflict(action='IGNORE').execute()
elif 'MySQLDatabase' in db.__str__():
    with db.atomic():
        for data in foo_data:
            for k, v in data.items():
                if (Foo.select(Foo.bar).where(Foo.bar == v).count()) == 0:
                    Foo.insert(bar=v).execute()

Upvotes: 1

Related Questions