skip to Main Content

I have a table with articles like this:

Table: artPerBrand

Colums: ID (auto increment), BrandID, ArtCat, ArtNrShort, ArtNrLong, Active

I want to insert new articles (using a python script) but only if the same ArtNrLong does not already exist.

I cannot do this by making the ArtNrLong unique beacause I have multiple scenarios where, sometimes, I can be possible to have the same ArtNrLong in the table.

So I created this sql statement based on this input (https://www.geeksforgeeks.org/python-mysql-insert-record-if-not-exists-in-table/).

  cur.execute ("INSERT INTO artPerBrand (BrandID, ArtCat, ArtNrShort, ArtNrLong, Active)
select * from (Select" + varBrandID, varArtCat, varArtNrShort, varArtNrLong,1 +") as temp 
where not exists (Select ArtNrLong from artPerBrand where ArtNrLong="+varArtNrLong+") LIMIT 1")

I also tried this option:

  sql = "INSERT INTO artPerBrand(BrandID, ArtCat, ArtNrShort, ArtNrLong, Active) VALUES (%s, %s, %s, %s, %s) WHERE NOT EXISTS (SELECT * FROM artPerBrand WHERE ArtNrLong = %s)" 
val = (varBrandID, varArtCat, varArtNrShort, varArtNrLong 1, varArtNrLong)
cur.execute(sql, val)

I get a general error, telling me the query is wrong.
Am I mistaking some quotes here, or something?

Combining the help in the comments brought me the solution:

      sql = "INSERT INTO artPerBrand(BrandID, ArtCat, ArtNrShort, ArtNrLong, Active) SELECT %s, %s, %s, %s, %s FROM DUAL WHERE NOT EXISTS (SELECT * FROM artPerBrand WHERE ArtNrLong = %s)" 
val = (varBrandID, varArtCat, varArtNrShort, varArtNrLong 1, varArtNrLong)
cur.execute(sql, val)

2

Answers


  1. Chosen as BEST ANSWER
          sql = "INSERT INTO artPerBrand(BrandID, ArtCat, ArtNrShort, ArtNrLong, Active) SELECT %s, %s, %s, %s, %s FROM DUAL WHERE NOT EXISTS (SELECT * FROM artPerBrand WHERE ArtNrLong = %s)" 
    val = (varBrandID, varArtCat, varArtNrShort, varArtNrLong 1, varArtNrLong)
    cur.execute(sql, val)
    

    This works for me!


  2. Yes the query is incorrect.

    Select" + varBrandID, varArtCat, varArtNrShort, varArtNrLong,1 +
    

    What it should be

    Select" + varBrandID+", "+ varArtCat", "+ varArtNrShort", "+ varArtNrLong", "+ 1 +
    

    Secondly, if they are integer values you can’t add them, you have to use

    str(varBrandID)
    

    If you want to use them as integers, Don’t concatenate them, assign them

    "select %d,%d,%d,%d from x"%(val1,val2,val3,val4)
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search