Вы сбиваете с толку заполнителями SQLite Python? Пришло время для лучшего понимания! Узнайте, как в этом руководстве.
Вступление
SQLite является встроенным двигателем реляционного баз данных с открытым исходным кодом. Его разработчики, от SQLite.org, называют его автономным, безреслунным, нулевым, быстрым, надежным и транзакционным двигателем базы данных SQL. Это держит вещи простыми. Sqlite “просто работает”.
Библиотека SQLite также интегрирована в несколько популярных языков сценариев, таких как Python. Встроенный модуль SQLite3 с Python был написан Gerhard Häring. Он предоставляет интерфейс SQL, совместимый с спецификацией DB-API 2.0, описываемый PEP 249.
Согласно «Справочке библиотеки Python», операторы SQL могут быть параметризованы (I.E. заполнители вместо литералов SQL). Модуль SQLite3 поддерживает два вида заполнителей: знаки вопросительных знаков (стиль QMark) и именованные заполнители (названный стиль). Другие два метода, использующие традиционные% s заполнителя и операции на строке, также доступны для операторов SQL.
Примеры
## Dependencies
import sqlite3
## Create Groundwater Assay Table
def create_db():
sql_ = "CREATE TABLE IF NOT EXISTS gw_assay \
( \
point_id TEXT NOT NULL, \
analyte TEXT NOT NULL, \
content REAL NOT NULL DEFAULT -999, \
sampling_date, \
PRIMARY KEY (point_id, analyte, sampling_date) \
);"
cnn = sqlite3.connect("groundwater.db")
cur = cnn.cursor()
cur.execute(sql_)
cnn.commit()
cnn.close()
## Add a batch of records into the db - using "?" placeholders, or question marks (qmark style)
def add_records():
sql_ = "INSERT OR IGNORE INTO gw_assay VALUES (?, ?, ?, ?)"
# Simplified assay data extracted from real project
records = [("M09", "1,2-Dichloroethane", 0.0014, "2019-04-03"),
("M09", "1,2-Dichloroethane", 11.4, "2019-05-28"),
("M09", "1,2-Dichloroethane", 21.5, "2019-07-11"),
("M09", "1,2-Dichloroethane", 28.7, "2019-07-25"),
("M09", "1,2-Dichloroethane", 0.184, "2019-09-16"),
("M09", "1,2-Dichloroethane", 1.64, "2019-11-08"),
("M09", "1,2-Dichloroethane", 2.05, "2019-12-09"),
("M09", "1,2-Dichloroethane", 0.365, "2019-12-31"),
("M09", "1,2-Dichloroethane", 0.38, "2020-01-14"),
("M09", "1,2-Dichloroethane", 0.453, "2020-02-13"),
("M09", "COD", 687, "2019-04-03"),
("M09", "COD", 836, "2019-05-28"),
("M09", "COD", 57.4, "2019-07-10"),
("M09", "COD", 608, "2019-07-11"),
("M09", "COD", 520, "2019-07-25"),
("M09", "COD", 576, "2019-07-26"),
("M09", "COD", 63.2, "2019-09-16"),
("M09", "COD", 219, "2019-12-09"),
("M09", "COD", 131, "2019-12-31"),
("M09", "COD", 111, "2020-01-14"),
("M09", "COD", 118, "2020-02-13"),
("M09", "COD", 103, "2020-02-27"),
("M09", "pH", 7.57, "2019-04-03"),
("M09", "pH", 6.32, "2019-05-28"),
("M09", "pH", 6.3, "2019-07-11"),
("M09", "pH", 6.11, "2019-07-25"),
("M09", "pH", 5.64, "2019-09-16"),
("M09", "pH", 6.31, "2019-11-08"),
("M09", "pH", 6.89, "2019-12-09"),
("M09", "pH", 6.67, "2019-12-31"),
("M09", "pH", 6.72, "2020-01-14"),
("M09", "pH", 6.63, "2020-02-13"),
("M09", "pH", 6.76, "2020-02-27"),
("SMD13", "1,2-Dichloroethane", 0.176, "2019-05-13"),
("SMD13", "1,2-Dichloroethane", 1.52, "2019-05-28"),
("SMD13", "1,2-Dichloroethane", 6.27, "2019-06-04"),
("SMD13", "1,2-Dichloroethane", 1.11, "2019-09-15"),
("SMD13", "1,2-Dichloroethane", 0.829, "2019-12-10"),
("SMD13", "1,2-Dichloroethane", 1.32, "2019-12-31"),
("SMD13", "1,2-Dichloroethane", 1.72, "2020-01-14"),
("SMD13", "1,2-Dichloroethane", 0.0137, "2020-02-13"),
("SMD13", "COD", 34.7, "2019-05-13"),
("SMD13", "COD", 28.9, "2019-05-28"),
("SMD13", "COD", 14.7, "2019-06-04"),
("SMD13", "COD", 219, "2019-09-15"),
("SMD13", "COD", 37, "2019-12-10"),
("SMD13", "COD", 395, "2019-12-31"),
("SMD13", "COD", 334, "2020-01-14"),
("SMD13", "COD", 18.8, "2020-02-13"),
("SMD13", "COD", 4.52, "2020-02-26"),
("SMD13", "pH", 3.81, "2019-05-13"),
("SMD13", "pH", 3.73, "2019-05-28"),
("SMD13", "pH", 3.71, "2019-06-04"),
("SMD13", "pH", 3.48, "2019-09-15"),
("SMD13", "pH", 3.99, "2019-12-10"),
("SMD13", "pH", 3.63, "2019-12-31"),
("SMD13", "pH", 3.68, "2020-01-14"),
("SMD13", "pH", 4.38, "2020-02-13"),
("SMD13", "pH", 6.49, "2020-02-26")]
cnn = sqlite3.connect("groundwater.db")
cur = cnn.cursor()
cur.executemany(sql_, records)
cnn.commit()
cnn.close()
## Select data - using "?" placeholders, or question marks (qmark style)
# Put ? as a placeholder wherever you want to use a value,
# and then provide a tuple of values as the second argument to the cursor's execute() method
def query_qmark(point_id="", analyte="", sampling_date=""):
sql_ = "SELECT * FROM gw_assay WHERE point_id = ? AND analyte = ? AND sampling_date = ?"
par_ = (point_id, analyte, sampling_date)
cnn = sqlite3.connect("groundwater.db")
cur = cnn.cursor()
cur.execute(sql_, par_)
records = cur.fetchall()
cnn.close()
return records
## Select data - using named placeholders (named style)
def query_named(point_id="", analyte="", sampling_date=""):
sql_ = "SELECT * FROM gw_assay WHERE point_id = :id AND analyte = :a AND sampling_date = :d"
par_ = {"id": point_id, "a": analyte, "d": sampling_date}
cnn = sqlite3.connect("groundwater.db")
cur = cnn.cursor()
cur.execute(sql_, par_)
records = cur.fetchall()
cnn.close()
return records
## Select data - using traditional "%s" placeholder
def query_format_s(point_id="", analyte="", sampling_date=""):
sql_ = 'SELECT * FROM gw_assay WHERE point_id = "%s" AND analyte = "%s" ' \
'AND sampling_date = "%s"' %(point_id, analyte, sampling_date)
cnn = sqlite3.connect("groundwater.db")
cur = cnn.cursor()
cur.execute(sql_)
records = cur.fetchall()
cnn.close()
return records
## Select data - using string concatenation
# You shouldn't assemble your query using Python's string operations because doing so is insecure;
# it makes your program vulnerable to an SQL injection attack
def query_string_concatenation(point_id="", analyte="", sampling_date=""):
sql_ = "SELECT * FROM gw_assay WHERE point_id = " + '"' + point_id + '"' \
+ " AND " + "analyte = " + '"' + analyte + '"' \
+ " AND " + "sampling_date = " + '"' + sampling_date + '"'
cnn = sqlite3.connect("groundwater.db")
cur = cnn.cursor()
cur.execute(sql_)
records = cur.fetchall()
cnn.close()
return records
#========main=============
if __name__ == "__main__":
create_db()
add_records()
print(query_qmark("M09", "COD", "2019-07-11"))
print(query_named("M09", "COD", "2019-07-11"))
print(query_format_s("M09", "COD", "2019-07-11"))
print(query_string_concatenation("M09", "COD", "2019-07-11"))Метод qmark – официально рекомендуется
## Select data - using "?" placeholders, or question marks (qmark style)
# Put ? as a placeholder wherever you want to use a value,
# and then provide a tuple of values as the second argument to the cursor's execute() method
def query_qmark(point_id="", analyte="", sampling_date=""):
sql_ = "SELECT * FROM gw_assay WHERE point_id = ? AND analyte = ? AND sampling_date = ?"
par_ = (point_id, analyte, sampling_date)
cnn = sqlite3.connect("groundwater.db")
cur = cnn.cursor()
cur.execute(sql_, par_)
records = cur.fetchall()
cnn.close()
return recordsМетод стиля qmark (метод “qmark”) – это краткий способ для фиксированного количества условий SQL (параметры запроса). Рекомендуется «ставить? В качестве заполнителя, где бы вы ни захотите использовать значение, а затем предоставить кортеж значений в качестве второго аргумента к методу Cursor ()».
Названный метод – официально рекомендуется
## Select data - using named placeholders (named style)
def query_named(point_id="", analyte="", sampling_date=""):
sql_ = "SELECT * FROM gw_assay WHERE point_id = :id AND analyte = :a AND sampling_date = :d"
par_ = {"id": point_id, "a": analyte, "d": sampling_date}
cnn = sqlite3.connect("groundwater.db")
cur = cnn.cursor()
cur.execute(sql_, par_)
records = cur.fetchall()
cnn.close()
return recordsИменованный метод стиля («названный метод») также является довольно пифитонным способом для фиксированных условий SQL.
Способ% s – традиционная, небезопасная
## Select data - using traditional "%s" placeholder
def query_format_s(point_id="", analyte="", sampling_date=""):
sql_ = 'SELECT * FROM gw_assay WHERE point_id = "%s" AND analyte = "%s" ' \
'AND sampling_date = "%s"' %(point_id, analyte, sampling_date)
cnn = sqlite3.connect("groundwater.db")
cur = cnn.cursor()
cur.execute(sql_)
records = cur.fetchall()
cnn.close()
return recordsСпособ форматирования% S (метод «% s») является традиционным методом в кодах базами Python Novers и программистов, которые переключаются в Python, исходящую с других языков программирования, такие как Java или C ++. Это читается, но «ссылка библиотеки Python» рекомендует «никогда не делать этого – небезопасно!»
Строковый метод – небезопасный, иногда полезный
## Select data - using string concatenation
# You shouldn't assemble your query using Python's string operations because doing so is insecure;
# it makes your program vulnerable to an SQL injection attack
def query_string_concatenation(point_id="", analyte="", sampling_date=""):
sql_ = "SELECT * FROM gw_assay WHERE point_id = " + '"' + point_id + '"' \
+ " AND " + "analyte = " + '"' + analyte + '"' \
+ " AND " + "sampling_date = " + '"' + sampling_date + '"'
cnn = sqlite3.connect("groundwater.db")
cur = cnn.cursor()
cur.execute(sql_)
records = cur.fetchall()
cnn.close()
return recordsСтрунные конкатенации Метод («Строковый метод») выглядит не так, как кратко, как другие три (3) методах. Хотя «Справочник библиотеки Python» состояния «Вы не должны собирать ваш запрос, используя строковые операции Python, потому что это небезопасно» , как показано в и внизу ниже рисунка ( https://xkcd.com/327/ ) Это очень полезно и гибко, если ваш запрос имеет произвольную комбинацию условий SQL и несколько таблиц. Вы можете использовать множество «если» положения для объединения команд запросов. Просто будьте осторожны для ваших заявлений SQL.
Резюме
Метод qmark и именованный метод рекомендуется “Справочник библиотеки Python”, % s Метод метода и строки могут быть небезопасены, однако полезные для некоторых конкретных экземпляров.
использованная литература
- Guido Van Rossum и команда разработки Python, Ссылка библиотеки Python (Release 3.8.2) 18 марта 2020
- Джей А. Крейбич, Использование SQLite – небольшой, быстрый, надежный, выберите любые три , Август 2010 года
Куда пойти отсюда?
Достаточно теории, давайте познакомимся!
Чтобы стать успешным в кодировке, вам нужно выйти туда и решать реальные проблемы для реальных людей. Вот как вы можете легко стать шестифункциональным тренером. И вот как вы польские навыки, которые вам действительно нужны на практике. В конце концов, что такое использование теории обучения, что никто никогда не нуждается?
Практические проекты – это то, как вы обостряете вашу пилу в кодировке!
Вы хотите стать мастером кода, сосредоточившись на практических кодовых проектах, которые фактически зарабатывают вам деньги и решают проблемы для людей?
Затем станьте питоном независимым разработчиком! Это лучший способ приближения к задаче улучшения ваших навыков Python – даже если вы являетесь полным новичком.
Присоединяйтесь к моему бесплатным вебинаре «Как создать свой навык высокого дохода Python» и посмотреть, как я вырос на моем кодированном бизнесе в Интернете и как вы можете, слишком от комфорта вашего собственного дома.
Присоединяйтесь к свободному вебинару сейчас!