I have such piece of program:
if self.current_condition == 'comparison': comparison_command = '''SELECT * FROM {table} WHERE {pkey} < %s''' cur.execute(sql.SQL(comparison_command).format( table=sql.Identifier(self.current_table), pkey=sql.Identifier(self.current_columns[0].text()) ), (self.comp_value, ) )
What I want to do is write ‘<‘ in command in the same way as {table} and {pkey}, that means I want to pass operators into command from variable. Can I do it?
The face of the app looks like this A little bit more of code context. It’s an app, that should get data from database by sql-request, that creates from interface. As you can see, there’s a bit more operators than one to choose.
def run_func(self): conn = None try: conn = psycopg2.connect( host='localhost', database='1rl', user='postgres', password=passwor) cur = conn.cursor() if self.current_condition == 'comparison': comparison_command = '''SELECT * FROM {table} WHERE {pkey} < %s''' cur.execute(sql.SQL(comparison_command).format(table=sql.Identifier(self.current_table), pkey=sql.Identifier(self.current_columns[0].text())), (self.comp_value, )) print(cur.fetchall()) except (Exception, psycopg2.DatabaseError) as error: print(error) finally: if conn is not None: conn.close() def display(self, i): self.list_of_conditions.setCurrentIndex(i) self.current_condition = self.all_conditions[i] print(self.current_condition) def comp_value_changed(self): self.comp_value = self.value.text() def comp_on_selected(self): sender = self.sender() self.comp_selec = sender.text() def comparison_fun(self): layout = QFormLayout() compars = QHBoxLayout() for i in self.all_comparisons: temp = QRadioButton(i) temp.toggled.connect(self.comp_on_selected) compars.addWidget(temp) layout.addRow(QLabel('Operators'), compars) self.value = QLineEdit() self.value.textChanged.connect(self.comp_value_changed) layout.addRow("Value", self.value) rune = QPushButton('Run') rune.clicked.connect(self.run_func) layout.addRow(rune) self.comparison.setLayout(layout)
Advertisement
Answer
You can use string interpolation on comparison_command
, use f-string notation, and double the existing braces to escape them:
comparison_command = f'SELECT * FROM {{table}} WHERE {{pkey}} {self.comp_selec} %s' cur.execute(sql.SQL(comparison_command).format( table=sql.Identifier(self.current_table), pkey=sql.Identifier(self.current_columns[0].text()) ), (self.comp_value, ) )
This assumes that self.comp_selec
has the operator in the SQL syntax, i.e. it should be like “<“, “<=”, “=”, “>=”, “>”, and not “==”, “ge”, “greater”, …etc.
I need to add the disclaimer about the risk of SQL injection. As I understand all of this runs on a local machine, a smart user could potentially tamper with the executable and make the SQL execute something harmful for the database or its security.