(I answer your question, but the best ways to proceed are at the end)
Python has several - perhaps more than 10 - different ways of formatting strings by interpolating with values. Staying in the exercise "Quotation marks + variable + opens quotation marks + comma + Quotation marks + Variable") is only something done in lignations without any of these features.
So, as miguel mentioned, the format
of strings method is a way to do this for ordinary strings.
And as I mentioned in my very blunt comment there, both the format method and try to interpolate the string with +
has the horror of not taking any care with the sanitization of the data to be inserted in the bank. Even if the data does not come from an external interactive input - which opens all ports for a SQL Injection attack - this practice does not make any escape from the data: any value there that contains a single "" character or ";" will cause an error in your application.
Good - so what? So the various Python SQL drivers have a form of them - separate from the "format" or %
methods of strings to insert parameters into queries. These methods have the great advantage of automatically escaping appropriately the characters that allow you to "jump out" of the SQL command and start another - in addition to generating syntactically defined queries regardless of the data to be inserted. (For example, the '\ n' being inserted does not generate any problems.)
YOU do not quote which SQL driver you are using (that is, neither the package nor the database). There is a small difference why there are some distinct ways of marking 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:
link
So, assuming your specific bank has the paramstyle=='qmark'
variable (for example: import sqlite3; print(sqlite3.paramstyle)
-replace sqlite3 with its driver module). This indicates that this driver will change the ?
characters in the query string automatically by values passed in a sequence of parameters - which will be the second parameter of the call to .execute
.
The second example you gave, therefore, is the correct way to do this,
for 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 you'd like to cut down on - the time to insert external data into the database and a critical point of the application, and it's not good to invent a lot of fashion - it's where more security flaws happen.
In particular it is important to know what data is going on which column.
Now, if your aray always matches exactly all columns, in order, from 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 you do not know the number of parameters beforehand - you can use the Python capabilities of string manipulation to generate the required number of %s,
.
It is important to note that in this case, you use a string expression to assemble 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 still, 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 Python daods and strings manipulation to mount the query and parameters appropriately. Driversd and Python mount the parameters with the formatting characters - but not the name of the columns. These have to be built into the string.
So if you send a dictionary of type {"REG": xx, "COD_ITEM": yy, "DESCR_ITEM": zz, ...}
to insert, you can use the keys
and values
methods of 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 very best - and forget all that, and use a relational object mapping package in your application, such as SQLALchemy ,
It ensures that you only have to worry about your data within Python, and it does the right thing to communicate with the bank - and you can even change the bank without changing anything in your program.