(I answer your question, but the best ways to proceed are at the end)
Python has several - maybe more than 10 - different ways of formatting strings interpolating with values. Staying in the exercise "closes quotes + variable + opens quotes + comma + closes quotes + variable") is only something done in lignuage without any of these features.
So, as Miguel said, the method format of strings is a way to do this for ordinary strings.
And as I quoted in my remark quite bluntly there, both the format method when trying to interpolate the string with + has the horror not to take any care with the sanitization of the data to be entered in the bank. Even if the data does not come from an external interactive input - which opens all the doors to an SQL Injection attack - this practice does not make any escape from the data: any value there that contains a simple "'" or ";" character will cause an error in your application.
Good - and then? So that the various Python SQL drivers have a form of them - separate from the "format" methods or % strings to insert parameters into queries. These methods have the great advantage of automatically escaping in the appropriate way characters that allow you to "jump off" the SQL command and start another one - in addition to generating syntactically variable queries independent of the data to be inserted. (For example, the ' n' that be wants to insert does not generate any problem).
You don’t mention which SQL driver you are using (that is, neither the package nor which database). There is a small difference because there are some distinct ways to mark the data insertion points depending on the driver. This document has the documentation of the SQL drivers for Python, and this session has the possible parameter types:
https://www.python.org/dev/peps/pep-0249/#paramstyle
So, assuming your specific bank has the variable paramstyle=='qmark' (for example: import sqlite3; print(sqlite3.paramstyle) -change sqlite3 to its driver module) . This indicates that this driver will swap characters ? in the query string automatically for values passed in a parameter sequence - which will be the second parameter of the call to .execute.
The whole example you’ve set, therefore, is the right way to do it,
to a bank driver that has paramstyle== "format" or pyformat
def insertReg0200(reg0200):
cur.execute("Insert into reg_0200 (REG,COD_ITEM,DESCR_ITEM,COD_BARRA,COD_ANT_ITEM,UNID_INV,TIPO_ITEM,COD_NCM,EX_IPI,COD_GEN, COD_LST, ALIQ_ICMS) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",reg0200[1:13])
I do not know what and you would like to decrease from there - the time to enter in the external database and a critical point of the application, and it is not good to invent a lot of fashion - it is where more security failures happen.
In particular it is important to know which data is going in which column.
Now, if your Aray will always match exactly with all the columns, in order, of the table, the same SQL syntax allows you not to send the columns - just do
cur.execute("INSERT INTO reg_0200 VALUES (%s, %s, %s,...)", reg0200[1:13)
In this case, let’s assume that you do not know beforehand the number d and parameters - Voce can use Python’s string manipulation capabilities to generate the required number of %s,.
It is important to note that in this case, you use a string expression to mount the query template - this generates a string that is the first parameter for execute and the sgndo parameter is the sequence of values itself. This ensures that the SQL driver escapes each parameter to avoid SQL Injection:
parametros = reg0200[1:13]
query = "INSERT INTO reg_0200 VALUES ({})".format(", ".join('%s' for _ in len (parametros)))
cur.execute(query, parametros)
But better yet, instead of sending your parameters as lists, use dictionaries, where each data is directly associated with the column name. In this case, you can use the SQL syntax of SET col_name=expr, ... instead of VALUES. Of course, you will need to use the Python daods and strings to assemble the query and the appropriate parameters. The driversd and Python mount the parameters with the formatting characters - but not the column name. These have to be built fixed in the string.
So if you send a dictionary like {"REG": xx, "COD_ITEM": yy, "DESCR_ITEM": zz, ...} for insertion, you can use the methods keysand values from the dictionary to extract column names and data separately:
def insere(dict_data):
query = "INSERT INTO reg0200 SET " + ", ".join("{}=%s".format(key) for key in dict_data.keys())
cur.execute(query, dict_data.values())
MOST IMPORTANT
but now the best - and forget all that, and use in your application a relational object mapping package, such as the Sqlalchemy,
It ensures that you only have to worry about your data inside Python, and does the right thing to communicate with the bank - and you can even exchange the bank without changing anything in your program.
beware of identation. In Python it is not only "to look cute".
– jsbueno