I have a simple array like this:
a=['a','b','c']
I need to insert all the "a" elements into Mysql table 'items' and column 'names' avoiding to insert the element if it's already present in 'names' column, avoiding iteration and multiple INSERT query.
Thanks
1) You can use MySQL specific INSERT ... ON DUPLICATE KEY UPDATE Syntax. (I assume there is PRIMARY KEY or UNIQUE KEY on column 'name')
(additionaly: a = list(set(a)) #to remove duplicates in a).
a = ['a', 'b', 'c']
c = conn.cursor()
c.executemany('INSERT INTO items (name) VALUES (%s) ON DUPLICATE KEY UPDATE name = name', a)
2) If there is no uniqueness constriaint on column 'name', you can check which names are already in database and remove them from your list to insert:
a = ['a', 'b', 'c']
c = conn.cursor()
c.execute('SELECT names from items')
existent_names = [name[0] for name in c]
a = list(set(a) - set(existent_names))
c.executemany('INSERT INTO items (name) VALUES (%s)', a)
Hi if you dont want to have duplicate 'names' into your table 'items' maybe that should be your primary key, and when ever you try to insert duplicate values, mysql simply wont allow it. However this code maybe can help you if you are using different primary key for your table:
import MySQLdb
conn = MySQLdb.connect(host= "localhost",
user="root",
passwd="yourpass",
db="yourdb")
x = conn.cursor()
a=['a','b','c']
for i in range (0,len(a))
try:
data = x.execute("""SELECT name FROM items WHERE name=%s """, a[i])
data = cursor.fetchone()
if data == None:
x.execute("""INSERT INTO items(name) VALUES(%s)""",a[i])
conn.commit()
except:
conn.rollback()
This is what I understand from your post. You want to add unique values from the list into the database. You can use python's set for it.
a = ['a', 'b', 'c']
set_a = list(set(a))
inset_into_db(set_a)
If holding all items.names won't be memory costly, you can run a query to select all items.names and keep these in a set.
cursor.execute('SELECT names from items')
names_set = set(name[0] for name in cursor)
Before you execute a query, filter out existing names in this set.
fresh_a = filter(lambda v: v not in names_set, a)
If you are concerned about duplicates names in a, you can apply cast it to a set.
