tecnooreo.blogg.se - Python insert into mysql

Python insert into mysql how to#
Python insert into mysql code#

You will store your values in 1-dimensional numpy arrays arr_name and arr_email which are then converted in a list of concatenated values, row by row. There is a chance that this solution has the high speed of answer without risking injection attacks.

executemany(): just 2 times "%s" (= independent from the number of the rows).

execute(): 200 times "%s" (= dependent from the number of the rows).

It is a manually built execute() statement with multiple placeholders of "%s", which likely outperforms the executemany() statement.įor example, at 2 columns, inserting 100 rows: The solution builds a dynamic number of placeholders to be added to the sql statement. Here is a function that puts NathanVillaescusa's and answers in a single execute() approach. Only if this does not work properly, try the approach answer shows that executemany() has either a strong Python overhead or it uses multiple execute() statements where this is not needed, elsewise executemany() would not be so much slower than a single execute() statement. The difference to answer is that the values are passed as a second parameter to the execute() function, which should be injection attack safe because this is only evalutated during runtime (in contrast to ".format()").Ĭursor.execute("""INSERT INTO mailing_list (name,email) VALUES (%s)""", ",".join(str(i) for i in rows)) Taking up the idea of it should work to simply add one single placeholder for all values to be passed. I know I could probably use some online xls to mysql converter, but this is a learning exercise for me as well.

Python insert into mysql how to#

I looked at using LOAD DATA INFILE, but I really don't understand how to format the file or the query and my eyes bleed when I have to read MySQL docs. The list is huge and I definitely can't afford to put the insert into a loop. What am I doing wrong? Is this even possible? This is the error: _mysql_exceptions.OperationalError: (1241, 'Operand should contain 1 column(s)') I tried putting my query into a var initially, but then it just barfed up a message about passing a tuple to execute(). email is the 4th col."""įor n, e in sorted(mailing_eritems()):ĭb = nnect(host=host, user=user, db=dbname, passwd=pwd)Ĭursor.execute("""INSERT INTO mailing_list (name,email) VALUES (%s,%s)""", Obviously I don't want to do an insert statement for every list item. Now I want to put each name and email into a mysql database. I am using python with xlrd to retrieve the name and email from the xls file into two lists. Does anybody know why there is a type error now? TypeError: not all arguments converted during string formatting The error message reads: query = query % db.literal(args)

Python insert into mysql code#

After passing execute() a list of rows as per Nathan's suggestion, below, the code executes further but still gets stuck on the execute function.