Как вставить переменную в sql запрос на python

How to use variables in SQL statement in Python?

How can I write the variable names without Python including them as part of the query text?

5 Answers 5

Note that the parameters are passed as a tuple, (a, b, c) . If you’re passing a single parameter, the tuple needs to end with a comma, (a,) .

The database API does proper escaping and quoting of variables. Be careful not to use the string formatting operator ( % ), because

1. It does not do any escaping or quoting.
2. It is prone to uncontrolled string format attacks e.g. SQL injection.

Different implementations of the Python DB-API are allowed to use different placeholders, so you’ll need to find out which one you’re using — it could be (e.g. with MySQLdb):

or (e.g. with sqlite3 from the Python standard library):

or others yet (after VALUES you could have (:1, :2, :3) , or «named styles» (:fee, :fie, :fo) or (%(fee)s, %(fie)s, %(fo)s) where you pass a dict instead of a map as the second argument to execute ). Check the paramstyle string constant in the DB API module you’re using, and look for paramstyle at http://www.python.org/dev/peps/pep-0249/ to see what all the parameter-passing styles are!

Many ways. DON’T use the most obvious one ( %s with % ) in real code, it’s open to attacks.

Here copy-paste’d from pydoc of sqlite3:

More examples if you need:

Be careful when you simply append values of variables to your statements: Imagine a user naming himself ‘;DROP TABLE Users;’ — That’s why you need to use SQL escaping, which Python provides for you when you use cursor.execute in a decent manner. Example in the URL is:

The syntax for providing a single value can be confusing for inexperienced Python users.

Given the query

Generally*, the value passed to cursor.execute must wrapped in an ordered sequence such as a tuple or list even though the value itself is a singleton, so we must provide a single element tuple, like this: (value,) .

Passing a single string

will result in an error which varies by the DB-API connector, for example

TypeError: not all arguments converted during string formatting

sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 5 supplied

mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax;

* The pymysql connector handles a single string parameter without erroring. However it’s better to wrap the string in a tuple even if it’s a single because

Как использовать переменные в инструкции SQL в Python?

здесь var1 — целое число, var2 & var3 — Это строки.

как я могу написать имена переменных без python, включая их в текст запроса?

4 ответов:

1. он не делает никаких побегов или цитирования.
2. он склонен к неконтролируемым атакам формата строки, например SQL-инъекций.

различные реализации Python DB-API могут использовать разные заполнители, поэтому вам нужно будет узнать, какой из них вы используете — это может быть (например, с MySQLdb):

или (например, с sqlite3 из стандартной библиотеки Python):

или другие еще (после VALUES мог бы (:1, :2, :3) , или «именованные стили» (:fee, :fie, :fo) или (%(fee)s, %(fie)s, %(fo)s) где вы передаете dict вместо карты в качестве второго аргумента execute ). Проверьте paramstyle строка константа в модуле DB API, который вы используете, и ищите paramstyle в http://www.python.org/dev/peps/pep-0249/ чтобы увидеть, что все стили передачи параметров!

многими способами. НЕ используйте самый очевидный ( %s С % ) в реальном коде, он открыт для ударов.

здесь copy-paste’D от pydoc sqlite3:

больше примеров, Если вам нужно:

Python: Работа с базой данных, часть 1/2: Используем DB-API

В статье рассмотрены основные методы DB-API, позволяющие полноценно работать с базой данных. Полный список можете найти по ссылкам в конец статьи.

Требуемый уровень подготовки: базовое понимание синтаксиса SQL и Python.

Готовим инвентарь для дальнейшей комфортной работы

Python имеет встроенную поддержку SQLite базы данных, для этого вам не надо ничего дополнительно устанавливать, достаточно в скрипте указать импорт стандартной библиотеки

Примечание: внося изменения в базу не забудьте их применить, так как база с непримененными изменениями остается залоченной.

Вы можете использовать (последние два варианта кросс-платформенные и бесплатные):

• Привычную вам утилиту для работы с базой в составе вашей IDE;

Python DB-API модули в зависимости от базы данных

Соединение с базой, получение курсора

Для начала рассмотрим самый базовый шаблон DB-API, который будем использовать во всех дальнейших примерах:

При работе с другими базами данных, используются дополнительные параметры соединения, например для PostrgeSQL:

Чтение из базы

Обратите внимание: После получения результата из курсора, второй раз без повторения самого запроса его получить нельзя — вернется пустой результат!

Запись в базу

Примечание: Если к базе установлено несколько соединений и одно из них осуществляет модификацю базы, то база SQLite залочивается до завершения (метод соединения .commit()) или отмены (метод соединения .rollback()) транзакции.

Разбиваем запрос на несколько строк в тройных кавычках

Длинные запросы можно разбивать на несколько строк в произвольном порядке, если они заключены в тройные кавычки — одинарные (»’…»’) или двойные («»». «»»)

Конечно в таком простом примере разбивка не имеет смысла, но на сложных длинных запросах она может кардинально повышать читаемость кода.

Объединяем запросы к базе данных в один вызов метода

Метод курсора .execute() позволяет делать только один запрос за раз, при попытке сделать несколько через точку с запятой будет ошибка.

Для решения такой задачи можно либо несколько раз вызывать метод курсора .execute()

Либо использовать метод курсора .executescript()

Данный метод также удобен, когда у нас запросы сохранены в отдельной переменной или даже в файле и нам его надо применить такой запрос к базе.

Делаем подстановку значения в запрос

Важно! Никогда, ни при каких условиях, не используйте конкатенацию строк (+) или интерполяцию параметра в строке (%) для передачи переменных в SQL запрос. Такое формирование запроса, при возможности попадания в него пользовательских данных – это ворота для SQL-инъекций!

Правильный способ – использование второго аргумента метода .execute()

Возможны два варианта:

Примечание 1: В PostgreSQL (UPD: и в MySQL) вместо знака ‘?’ для подстановки используется: %s

Примечание 2: Таким способом не получится заменять имена таблиц, одно из возможных решений в таком случае рассматривается тут: stackoverflow.com/questions/3247183/variable-table-name-in-sqlite/3247553#3247553

UPD: Примечание 3: Благодарю Igelko за упоминание параметра paramstyle — он определяет какой именно стиль используется для подстановки переменных в данном модуле.
Вот ссылка с полезным приемом для работы с разными стилями подстановок.

Делаем множественную вставку строк проходя по коллекции с помощью метода курсора .executemany()

Получаем результаты по одному, используя метод курсора .fetchone()

Он всегда возвращает кортеж или None. если запрос пустой.

Важно! Стандартный курсор забирает все данные с сервера сразу, не зависимо от того, используем мы .fetchall() или .fetchone()

Курсор как итератор

UPD: Повышаем устойчивость кода

Благодарю paratagas за ценное дополнение:
Для большей устойчивости программы (особенно при операциях записи) можно оборачивать инструкции обращения к БД в блоки «try-except-else» и использовать встроенный в sqlite3 «родной» объект ошибок, например, так:

UPD: Использование with в psycopg2

Благодарю KurtRotzke за ценное дополнение:
Последние версии psycopg2 позволяют делать так:

Некоторые объекты в Python имеют __enter__ и __exit__ методы, что позволяет «чисто» взаимодействовать с ними, как в примере выше.

UPD: Ипользование row_factory

Благодарю remzalp за ценное дополнение:
Использование row_factory позволяет брать метаданные из запроса и обращаться в итоге к результату, например по имени столбца.
По сути — callback для обработки данных при возврате строки. Да еще и полезнейший cursor.description, где есть всё необходимое.

Пример из документации:

Дополнительные материалы (на английском)

Краткий бесплатный он-лайн курс — Udacity — Intro to Relational Databases — Рассматриваются синтаксис и принципы работы SQL, Python DB-API – и теория и практика в одном флаконе. Очень рекомендую для начинающих!

sqlite3 — DB-API 2.0 interface for SQLite databases¶

SQLite is a C library that provides a lightweight disk-based database that doesn’t require a separate server process and allows accessing the database using a nonstandard variant of the SQL query language. Some applications can use SQLite for internal data storage. It’s also possible to prototype an application using SQLite and then port the code to a larger database such as PostgreSQL or Oracle.

The sqlite3 module was written by Gerhard Häring. It provides an SQL interface compliant with the DB-API 2.0 specification described by PEP 249, and requires SQLite 3.7.15 or newer.

This document includes four main sections:

Tutorial teaches how to use the sqlite3 module.

Reference describes the classes and functions this module defines.

How-to guides details how to handle specific tasks.

Explanation provides in-depth background on transaction control.

The SQLite web page; the documentation describes the syntax and the available data types for the supported SQL dialect.

Tutorial, reference and examples for learning SQL syntax.

PEP 249 — Database API Specification 2.0

PEP written by Marc-André Lemburg.

Tutorial¶

In this tutorial, you will create a database of Monty Python movies using basic sqlite3 functionality. It assumes a fundamental understanding of database concepts, including cursors and transactions.

First, we need to create a new database and open a database connection to allow sqlite3 to work with it. Call sqlite3.connect() to create a connection to the database tutorial.db in the current working directory, implicitly creating it if it does not exist:

The returned Connection object con represents the connection to the on-disk database.

In order to execute SQL statements and fetch results from SQL queries, we will need to use a database cursor. Call con.cursor() to create the Cursor :

Now that we’ve got a database connection and a cursor, we can create a database table movie with columns for title, release year, and review score. For simplicity, we can just use column names in the table declaration – thanks to the flexible typing feature of SQLite, specifying the data types is optional. Execute the CREATE TABLE statement by calling cur.execute(. ) :

We can verify that the new table has been created by querying the sqlite_master table built-in to SQLite, which should now contain an entry for the movie table definition (see The Schema Table for details). Execute that query by calling cur.execute(. ) , assign the result to res , and call res.fetchone() to fetch the resulting row:

We can see that the table has been created, as the query returns a tuple containing the table’s name. If we query sqlite_master for a non-existent table spam , res.fetchone() will return None :

Now, add two rows of data supplied as SQL literals by executing an INSERT statement, once again by calling cur.execute(. ) :

The INSERT statement implicitly opens a transaction, which needs to be committed before changes are saved in the database (see Transaction control for details). Call con.commit() on the connection object to commit the transaction:

We can verify that the data was inserted correctly by executing a SELECT query. Use the now-familiar cur.execute(. ) to assign the result to res , and call res.fetchall() to return all resulting rows:

The result is a list of two tuple s, one per row, each containing that row’s score value.

Now, insert three more rows by calling cur.executemany(. ) :

Notice that ? placeholders are used to bind data to the query. Always use placeholders instead of string formatting to bind Python values to SQL statements, to avoid SQL injection attacks (see How to use placeholders to bind values in SQL queries for more details).

We can verify that the new rows were inserted by executing a SELECT query, this time iterating over the results of the query:

Each row is a two-item tuple of (year, title) , matching the columns selected in the query.

Finally, verify that the database has been written to disk by calling con.close() to close the existing connection, opening a new one, creating a new cursor, then querying the database:

You’ve now created an SQLite database using the sqlite3 module, inserted data and retrieved values from it in multiple ways.

How-to guides for further reading:

• How to use placeholders to bind values in SQL queries

• How to adapt custom Python types to SQLite values

• How to convert SQLite values to custom Python types

• How to use the connection context manager

• How to create and use row factories

Explanation for in-depth background on transaction control.

Reference¶

Module functions¶

Open a connection to an SQLite database.

database ( path-like object ) – The path to the database file to be opened. Pass ":memory:" to open a connection to a database that is in RAM instead of on disk.

timeout (float) – How many seconds the connection should wait before raising an exception, if the database is locked by another connection. If another connection opens a transaction to modify the database, it will be locked until that transaction is committed. Default five seconds.

detect_types (int) – Control whether and how data types not natively supported by SQLite are looked up to be converted to Python types, using the converters registered with register_converter() . Set it to any combination (using | , bitwise or) of PARSE_DECLTYPES and PARSE_COLNAMES to enable this. Column names takes precedence over declared types if both flags are set. Types cannot be detected for generated fields (for example max(data) ), even when the detect_types parameter is set; str will be returned instead. By default ( 0 ), type detection is disabled.

isolation_level (str | None) – The isolation_level of the connection, controlling whether and how transactions are implicitly opened. Can be "DEFERRED" (default), "EXCLUSIVE" or "IMMEDIATE" ; or None to disable opening transactions implicitly. See Transaction control for more.

check_same_thread (bool) – If True (default), ProgrammingError will be raised if the database connection is used by a thread other than the one that created it. If False , the connection may be accessed in multiple threads; write operations may need to be serialized by the user to avoid data corruption. See threadsafety for more information.

factory (Connection) – A custom subclass of Connection to create the connection with, if not the default Connection class.

cached_statements (int) – The number of statements that sqlite3 should internally cache for this connection, to avoid parsing overhead. By default, 128 statements.

Raises an auditing event sqlite3.connect with argument database .

Raises an auditing event sqlite3.connect/handle with argument connection_handle .

New in version 3.4: The uri parameter.

Changed in version 3.7: database can now also be a path-like object , not only a string.

New in version 3.10: The sqlite3.connect/handle auditing event.

Return True if the string statement appears to contain one or more complete SQL statements. No syntactic verification or parsing of any kind is performed, other than checking that there are no unclosed string literals and the statement is terminated by a semicolon.

This function may be useful during command-line input to determine if the entered text seems to form a complete SQL statement, or if additional input is needed before calling execute() .

sqlite3. enable_callback_tracebacks ( flag , / ) ¶

Enable or disable callback tracebacks. By default you will not get any tracebacks in user-defined functions, aggregates, converters, authorizer callbacks etc. If you want to debug them, you can call this function with flag set to True . Afterwards, you will get tracebacks from callbacks on sys.stderr . Use False to disable the feature again.

Register an unraisable hook handler for an improved debug experience:

Register an adapter callable to adapt the Python type type into an SQLite type. The adapter is called with a Python object of type type as its sole argument, and must return a value of a type that SQLite natively understands .

sqlite3. register_converter ( typename , converter , / ) ¶

Register the converter callable to convert SQLite objects of type typename into a Python object of a specific type. The converter is invoked for all SQLite values of type typename; it is passed a bytes object and should return an object of the desired Python type. Consult the parameter detect_types of connect() for information regarding how type detection works.

Note: typename and the name of the type in your query are matched case-insensitively.

Module constants¶

Pass this flag value to the detect_types parameter of connect() to look up a converter function by using the type name, parsed from the query column name, as the converter dictionary key. The type name must be wrapped in square brackets ( [] ).

This flag may be combined with PARSE_DECLTYPES using the | (bitwise or) operator.

Pass this flag value to the detect_types parameter of connect() to look up a converter function using the declared types for each column. The types are declared when the database table is created. sqlite3 will look up a converter function using the first word of the declared type as the converter dictionary key. For example:

This flag may be combined with PARSE_COLNAMES using the | (bitwise or) operator.

sqlite3. SQLITE_OK ¶ sqlite3. SQLITE_DENY ¶ sqlite3. SQLITE_IGNORE ¶

Flags that should be returned by the authorizer_callback callable passed to Connection.set_authorizer() , to indicate whether:

Access is allowed ( SQLITE_OK ),

The SQL statement should be aborted with an error ( SQLITE_DENY )

The column should be treated as a NULL value ( SQLITE_IGNORE )

String constant stating the supported DB-API level. Required by the DB-API. Hard-coded to "2.0" .

String constant stating the type of parameter marker formatting expected by the sqlite3 module. Required by the DB-API. Hard-coded to "qmark" .

The named DB-API parameter style is also supported.

Version number of the runtime SQLite library as a string .

Version number of the runtime SQLite library as a tuple of integers .

Integer constant required by the DB-API 2.0, stating the level of thread safety the sqlite3 module supports. This attribute is set based on the default threading mode the underlying SQLite library is compiled with. The SQLite threading modes are:

1. Single-thread: In this mode, all mutexes are disabled and SQLite is unsafe to use in more than a single thread at once.

2. Multi-thread: In this mode, SQLite can be safely used by multiple threads provided that no single database connection is used simultaneously in two or more threads.

3. Serialized: In serialized mode, SQLite can be safely used by multiple threads with no restriction.

The mappings from SQLite threading modes to DB-API 2.0 threadsafety levels are as follows: