Модуль sqlite

2.Введение

SQLite имеет более 225 API.Однако большинство API являются необязательными и узкоспециализированными и могут быть проигнорированы новичками.Ядро API маленькое,простое и легкое в изучении.В этой статье кратко описано ядро API.

В отдельном документе, Интерфейс SQLite C / C ++ , представлены подробные спецификации для всех API C / C ++ для SQLite. Как только читатель поймет основные принципы работы SQLite, этот документ следует использовать в качестве справочного руководства. Эта статья предназначена только для ознакомления и не является ни полным, ни авторитетным справочником по SQLite API.

1.0. Классы хранения и типы данных

Каждое значение, хранящееся в базе данных SQLite (или обрабатываемое движком), имеет один из следующих классов хранения:

• NULL. Пустое значение в таблице базы.
• INTEGER. Целочисленное значение, хранящееся в 1, 2, 3, 4, 6 или 8 байтах, в зависимости от величины самого значения.
• REAL. Числовое значение с плавающей точкой. Хранится в формате 8-байтного числа IEEE с плавающей точкой.
• TEXT. Значение строки текста. Хранится с использованием кодировки базы данных (UTF-8, UTF-16BE или UTF-16LE).
• BLOB. Значение бинарных данных, хранящихся точно в том же виде, в каком были введены.

Отметим, что класс хранения — более широкое понятие, чем тип данных. К примеру, класс хранения INTEGER включает 6 различных типов целочисленных данных различной длины. На диске это записывается по-разному. Но как только целочисленные значения считываются с диска и поступают для обработки в оперативную память, они преобразуются в наиболее общий тип данных (8-байтное целое число). Следовательно, хранение по системе класса данных в практическом плане неотличимо от хранения по типу данных, и они могут быть взаимозаменяемыми.

Любой столбец базы данных SQLite версии 3, за исключением столбцов , может быть использован для хранения значений любого класса.

Все значения в инструкциях SQL, являются ли они литералами или параметрами, встроенными в строку SQL-запроса в случае прекомпилируемых инструкций SQL, имеют неявный класс хранения. В условиях, описанных ниже, движок базы данных может конвертировать значения между числовыми классами хранения (INTEGER и REAL) и TEXT во время выполнения запроса.

1.1. Логические типы данных

SQLite не имеет отдельного логического класса хранения. Вместо этого, логические значения хранятся как целые числа 0 (false) и 1 (true).

1.2 Типы данных даты и времени

SQLite не имеют классов, предназначенных для хранения дат и/или времени. Вместо этого, встроенные функции даты и времени в SQLite способны работать с датами и временем, сохраненными в виде значений TEXT, REAL и INTEGER в следующих форматах:

• TEXT как строка формата ISO8601 («»).
• REAL как числа юлианского календаря. То есть число дней с полудня 24 ноября 4714 г. до н.э. по Гринвичу в соответствии с ранним григорианским календарём.
• INTEGER как время Unix, — количество секунд с 1970-01-01 00:00:00 UTC.

В приложениях следует выбирать, в каком из этих форматов хранить даты и время, а затем можно свободно конвертировать из одного формата в другой с помощью встроенных функций даты и времени.

Пример базового доступа к данным

Пример кода DataAccess_Basic для этого документа выглядит следующим образом при работе в Android. В коде показано, как выполнять простые операции SQLite.NET и выводить результаты в виде текста в главном окне приложения.

Android

В следующем примере кода показано взаимодействие базы данных с использованием библиотеки SQLite.NET для инкапсуляции базового доступа к базе данных.
Он показывает:

1. Создание файла базы данных

2. Вставка данных путем создания объектов и последующего их сохранения

3. Запрос данных

Необходимо включить следующие пространства имен:

Последний требует добавления SQLite в проект

Обратите внимание, что таблица базы данных SQLite определяется путем добавления атрибутов к классу ( классу), а не CREATE TABLE команде

Использование атрибута без указания параметра имени таблицы приведет к тому, что имя базовой таблицы базы данных будет совпадать с именем класса (в данном случае — «фондовой биржи»)

фактическое имя таблицы важно, если вы пишете SQL запросы непосредственно в базе данных, а не используете методы доступа к данным ORM. Аналогичным образом атрибут является необязательным, и при отсутствии столбца в таблицу будет добавлен столбец с тем же именем, что и у свойства в классе

6.Привязка параметров и повторное использование подготовленных заявлений

В предыдущем обсуждении предполагалось, что каждый оператор SQL готовится один раз, оценивается, а затем уничтожается. Однако SQLite позволяет выполнять многократную оценку одного и того же подготовленного оператора . Это достигается с помощью следующих процедур:

• sqlite3_reset()
• sqlite3_bind()

После того, как подготовленный оператор был оценен одним или несколькими вызовами sqlite3_step () , его можно сбросить для повторной оценки с помощью вызова sqlite3_reset () . Подумайте о sqlite3_reset () как о перемотке подготовленной программы операторов к началу. Использование sqlite3_reset () в существующем подготовленном операторе вместо создания нового подготовленного оператора позволяет избежать ненужных вызовов sqlite3_prepare () . Для многих операторов SQL время, необходимое для запуска sqlite3_prepare (), равно или превышает время, необходимое для sqlite3_step () . Так что избегая звонковsqlite3_prepare () может значительно улучшить производительность.

Это обычно не полезно оценить точно тот же оператор SQL более чем один раз. Чаще хочется оценить подобные утверждения. Например, вы можете захотеть несколько раз оценить оператор INSERT с разными значениями. Или вы можете захотеть оценить один и тот же запрос несколько раз, используя другой ключ в предложении WHERE. Чтобы приспособиться к этому, SQLite позволяет операторам SQL содержать которые «привязаны» к значениям до их оценки. Эти значения могут быть позже изменены, и тот же подготовленный оператор может быть оценен во второй раз с использованием новых значений.

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

• ?
• ?NNN
• AAA
• $AAA
• @AAA

В примерах выше,NNNявляется целым числом иAAAэто идентификатор. Первоначально параметр имеет значение NULL. Перед вызовом sqlite3_step () в первый раз или сразу после sqlite3_reset () приложение может вызывать интерфейсы sqlite3_bind () для присоединения значений к параметрам. Каждый вызов sqlite3_bind () отменяет предыдущие привязки одного и того же параметра.

Приложению разрешено заранее подготовить несколько операторов SQL и оценить их по мере необходимости. Нет произвольного ограничения на количество ожидающих подготовленных отчетов . Некоторые приложения вызывают sqlite3_prepare () несколько раз при запуске, чтобы создать все подготовленные операторы, которые им когда-либо понадобятся. Другие приложения хранят кэш последних использованных подготовленных операторов, а затем повторно используют подготовленные операторы из кеша, когда они доступны. Другой подход — повторно использовать подготовленные операторы только тогда, когда они находятся внутри цикла.

История SQLiteStudio

Проект стартовал в начале 2007 года. Разработчик искал какой-нибудь SQLite-менеджер, которым легко пользоваться. Просто нужно было быстро создать таблицу и поместить в нее данные. Так как найти ничего не удалось, именно так появилась SQLiteStudio.

В то время разработчик был очень увлечен Tcl, поэтому это был предпочтительный язык программирования.

SQLiteStudio версии 1.0.0 имела очень простой графический интерфейс с сеткой для ввода данных.

Версия 1.1.0 (Рождество 2008 г.) представила поддержку SQLite 2, Tcl / Tk 8.5 (со сглаживанием, которого не было в версии 1.0.0), а также много других функций. Это был большой шаг вперед.

Версия 2.0.0 (ноябрь 2010 г.) внедрила более умное завершение синтаксиса, подсветку синтаксических ошибок, красивый принтер для SQL-кода (форматировщик), поддержку перетаскивания и многое другое. Это была первая версия, в которой использовался настоящий парсер кода. Кроме того, он был первым, работающим на Mac OS X.

Выпущено 28 исправлений! Проект настолько вырос, что поддерживать его стало проблемой. Исходный код также был не самым красивым (в плане поддержки).

SQLiteStudio версии 2.1.0 (декабрь 2012 года, канун Нового года) не добавляла «огромных» возможностей, но расширила множество второстепенных функций, которые уже были в 2.0.0. Многое изменилось в исходном коде, чтобы его было легче поддерживать.

Было принято решение перейти на C++ и библиотеку Qt.

После выпуска 2.1.2 началась работа над версией 3.0.0 на C ++ и Qt. Это был март 2013 года.

SQLiteStudio 3.0.0В версии приобрела большую скорость и ощущение легкости. Кроме того, она был намного лучше спроектирована, поэтому было легче поддерживать и расширять. Порт на MacOS X стал намного стабильнее и стал выглядеть нативно.

Использование

Процедурный интерфейс к SQLite почти такой же, как у MySQL и других расширений БД. По большей части переход к SQLite потребует только изменить mysql/pq/etc… префикс функции на sqlite.

<?php 
// создаём новую базу (процедурный интерфейс) 
$db = sqlite_open("db.sqlite"); 

// создаём таблицу foo 
sqlite_query($db, "CREATE TABLE foo (id INTEGER PRIMARY KEY, name CHAR(255))"); 

// добавляем что-нибудь для примера 
sqlite_query($db, "INSERT INTO foo (name) VALUES ('Ilia')"); 
sqlite_query($db, "INSERT INTO foo (name) VALUES ('Ilia2')"); 
sqlite_query($db, "INSERT INTO foo (name) VALUES ('Ilia3')"); 

// выполняем запрос 
$result = sqlite_query($db, "SELECT * FROM foo"); 
// проходим в цикле выборкой по ячейкам 
while ($row = sqlite_fetch_array($result)) { 
    print_r($row); 
     /* каждый результат будет выглядеть примерно так 
    Array 
    ( 
         => 1 
         => 1 
         => Ilia 
         => Ilia 
    ) 
*/ 
} 

// закрываем соединение с базой 
sqlite_close($db); 

?>

Управление транзакциями SQLite3

Транзакции – это функция, которой известны реляционные базы данных. Модуль sqlite3 в Python полностью способен управлять внутренним состоянием транзакции, единственное, что нам нужно сделать, это сообщить ему о том, что транзакция произойдет.

Вот пример программы, которая описывает, как мы пишем транзакции в нашей программе, явно вызывая функцию commit():

import sqlite3

db_filename = 'journaldev.db'

def show_books(conn):
    cursor = conn.cursor()
    cursor.execute('select name, topic from book')
    for name, topic in cursor.fetchall():
        print('  ', name)


with sqlite3.connect(db_filename) as conn1:
    print('Before changes:')
    show_books(conn1)

    # Insert in one cursor
    cursor1 = conn1.cursor()
    cursor1.execute("""
    insert into book (name, topic, published)
    values ('Welcome Python', 'Python', '2013-01-01')
    """)

    print('\nAfter changes in conn1:')
    show_books(conn1)

    # Select from another connection, without committing first
    print('\nBefore commit:')
    with sqlite3.connect(db_filename) as conn2:
        show_books(conn2)

    # Commit then select from another connection
    conn1.commit()
    print('\nAfter commit:')
    with sqlite3.connect(db_filename) as conn3:
        show_books(conn3)

Посмотрим на результат этой программы:

Когда функция show_books(…) вызывается до фиксации conn1, результат зависит от того, какое соединение используется. Поскольку изменения были внесены из conn1, он видит внесенные изменения, а conn2 – нет. После того, как мы зафиксировали все изменения, все подключения смогли увидеть внесенные изменения, включая conn3.

SQLite3 Executemany (Bulk insert)

You can use the executemany statement to insert multiple rows at once.

Consider the following code:

import sqlite3

con = sqlite3.connect('mydatabase.db')

cursorObj = con.cursor()

cursorObj.execute('create table if not exists projects(id integer, name text)')

data = 

cursorObj.executemany("INSERT INTO projects VALUES(?, ?)", data)

con.commit()

Here we created a table with two columns, and “data” has four values for each column. We pass the variable to the executemany() method along with the query.

Note that we have used the placeholder to pass the values.

The above code will generate the following result:

Establish database connection to an SQLite database

First, create a new file  inside the folder and add a new class named as follows:

The constant is used to store the path to the sqlite database file which resides in the folder.

Second,  create a new file and add the class as follows:

To establish a database connection to an SQLite database, you need to create a new instance of the class and pass a connection string to the constructor of the object.

Suppose the SQLite database file is in the folder, you use the following connection string:

Because you store the path to the sqlite database file in the class, you just simply use it to construct the connection string.

Notice that when PHP connects to an SQLite database that does not exist, PHP will create a new SQLite database file.

The is used to store the instance of the object. In the method, you check if a database connection has been established. If not, you create a new instance of the PDO object otherwise, you return the PDO object.

After having all classes in places, you use the following command to generate the autoload file:

To establish a connection to the SQLite database, you use the following code in the file:

If you check the folder, you will see a file with the name created.

When you create a new instance of the PDO, it will always throw a PDOException if the connection fails.

To handle the exception, you can use the block as follows:

In this tutorial, you have learned how to set up the PHP project structure and establish a database connection to the SQLite database.

Update SQLite Table Data in PHP

Now we will try to update quantity of all products by 20% using following statements in PHP.

<?

class DBphp extends SQLite3

{

function __construct()

{

$this->open(‘DBUsingPHP.db’);

}

}

$db = new DBphp();

$query=»UPDATE Product SET quantity=quantity + (quantity * 0.2)»;

$db->exec($query);

$query1=»SELECT * FROM Product»;

$result=$db->query($query1);

echo «p_id\tp_name\tprice\tquantity\n»;

while($row= $result->fetchArray()){

echo $row . «\t».

$row . «\t».

$row. «\t».

$row.»\n»;

}

$db->close();

?>

If you observe above program we are updating and displaying SQLite table records using the PHP program. If we execute above code we will get result like as shown below.

p_id  p_name      price quantity

—-  ——-     —-  —-

1     pencil      10    60

2     Eraser      5     72

Общее

SQLite – это встраиваемая библиотека в которой реализовано многое из стандарта SQL 92. Её притязанием на известность является как собственно сам движок базы, так и её интерфейс (точнее его движок) в пределах одной библиотеки, а также возможность хранить все данные в одном файле. Позиция функциональности SQLite где-то между MySQL и PostgreSQL. Однако, на практике, SQLite нередко оказывается в 2-3 раза (и даже больше) быстрее. Такое возможно благодаря высокоупорядоченной внутренней архитектуре и устранению необходимости в соединениях типа «сервер-клиент» и «клиент-сервер».

Всё это, собранное в один пакет, лишь немногим больше по размеру клиентской части библиотеки MySQL, является впечатляющим достижением для полноценной базы данных. Используя высоко эффективную инфраструктуру, SQLite может работать в крошечном объёме выделяемой для неё памяти, гораздо меньшем, чем в любых других системах БД. Это делает SQLite очень удобным инструментом с возможностью использования практически в любых задачах возлагаемых на базу данных.

Недостатки

1. Нет хранимых процедур.
2. Нет встроенной поддержки UNICODE
3. Не подходит для приложений, которые часто обращаются в базу

SQLite поддерживает динамическое типизирование данных.

Delete SQLite Table Data using PHP

Now we will delete data from a table using PHP programming language for that write the code as shown follow.

<?

class DBphp extends SQLite3

{

function __construct()

{

$this->open(‘DBUsingPHP.db’);

}

}

$db = new DBphp();

$query=»DELETE FROM Product WHERE p_id>1;»;

$db->exec($query);

$query1=»SELECT * FROM Product»;

$result=$db->query($query1);

echo «p_id\tp_name\tprice\tquantity\n»;

while($row= $result->fetchArray()){

echo $row . «\t».

$row . «\t».

$row. «\t».

$row.»\n»;

}

$db->close();

?>

If you observe above PHP program we are deleting data from the “Product” table where p_id > 1. Once we execute the above PHP program we will get a result like as shown below.

p_id  p_name      price quantity

—-  ——      —-  ———

1     pencil      10    60

This is how we can use SQLite databases in PHP programming language to connect, create, database, tables and insert, update, delete, and display data based on our requirements. 

SQLite vs MySQL

SQLite, as well as MySQL, are open source RDBMS. Let’s deep dive into their differences.

Architectural Difference – SQLite vs MySQL

• SQLite is an open source project available in the public domain
• MySQL is an open source project which is owned by Oracle

SQLite is a server-less database and is self-contained. This is also referred to as an embedded database which means the DB engine runs as a part of the app.

On the other hand, MySQL requires a server to run. MySQL will require a client and server architecture to interact over a network.

Data Type Support – SQLite vs MySQL

SQLite supports these datatypes: Blob, Integer, Null, Text, Real.

MySQL supports the below-mentioned datatypes:

Tinyint, Smallint, Mediumint, Int, Bigint, Double, Float, Real, Decimal, Double precision, Numeric, Timestamp, Date, Datetime, Char, Varchar, Year, Tinytext, Tinyblob, Blob, Text, MediumBlob, MediumText, Enum, Set, Longblob, Longtext.

As you can tell, MySQL is a lot more flexible when it comes to data types.

Storage and Portability – SQLite vs MySQL

The SQLite library is about 250 KB in size, while the MySQL server is about 600 MB. The SQLite directly stores info in a single file, making it easy to copy. No configurations are required, and the process ca be done using minimal support.

Before copying or exporting MySQL you need to condense it into a single file. For larger databases, this will be a time-consuming activity.

Multiple Access and Scalability – SQLite vs MySQL

SQLite does not have any specific user management functionality and hence is not suitable for multiple user access. MySQL has a well-constructed user management system which can handle multiple users and grant various levels of permission.

SQLite is suitable for smaller databases. As the database grows the memory requirement also gets larger while using SQLite. Performance optimization is harder when using SQLite. This has a few write constraints.

On the contrary, MySQL is easily scalable and can handle a bigger database with less effort.

Security and Ease of Setup – SQLite vs MySQL

SQLite does not have an inbuilt authentication mechanism. The database files can be accessed by anyone.  However, MySQL comes with a lot of inbuilt security features. This includes authentication with a username, password, and SSH.

SQLite does not require too many configurations and is easy to set up. MySQL requires more configurations as compared to SQLite. MySQL also has more setup guides available.

Плюсы, минусы — SQLite и MySQL

Давайте быстро суммируем существенные различия между двумя вариантами:

Преимущества SQLite:

• На основе файлов и прост в настройке и использовании
• Подходит для базовой разработки и тестирования
• Легко переносимый
• Использует стандартный синтаксис SQL с небольшими изменениями
• Легко использовать

Недостатки SQLite:

• Отсутствует управление пользователями и функции безопасности
• Трудно масштабируется
• Не подходит для больших баз данных

Преимущества MySQL:

• Легко использовать
• Предоставляет большой функционал
• Хорошие функции безопасности
• Легко масштабируется и подходит для больших баз данных.
• Обеспечивает хорошую скорость и производительность
• Обеспечивает хорошее управление пользователями и множественный контроль доступа

Недостатки MySQL:

• Требуются некоторые технические знания для настройки
• Немного другой синтаксис по сравнению с обычным SQL

About SQLite compiled to javascript (sql.js)

sql.js is a port of SQLite to JavaScript by compiling the SQLite C code with Emscripten, it is MIT licensed. It can be used like any javascript library. And it can be used both in a browser and in Node.js! Thanks to this, this tool is only executed on the browser side.

sql.js allows you to easily import and export SQLite databases.

Example

	 <script src="lib/sql.js"> </script>
	 <script>
		// Create a database
		var myDatabase = new SQL.Database();
		// Create a table, and insert one row
		myDatabase.run("CREATE TABLE mytable (mycolumn1, mycolumn2, mycolumn3);");
		myDatabase.run("INSERT INTO mytable VALUES (?,?,?)", );
		// load data
		this.db.each("SELECT mycolumn1, mycolumn2, mycolumn3 FROM mytable", {}, function(row){ 
			// row is now {mycolumn1:1, mycolumn2:2, mycolumn3:3}
		});	
	 </script>
		

A few words on Emscripten: It is a source-to-source compiler, this allows applications and libraries designed to run as standard executables to be integrated into client side web applications. Emscripten has been used to port (in addition to SQLite) Unreal Engine 3, Qt application framework, …

A is available.

Архитектура

Движок БД представляет библиотеку, с которой программа компонуется и SQLite становится составной частью программы. Вся БД хранится в единственном стандартном файле на машине, на которой исполняется программа.

Несколько процессов или потоков могут одновременно без каких-либо проблем читать данные из одной базы. Запись в базу можно осуществить только в том случае, если никаких других запросов в данный момент не обслуживается; в противном случае попытка записи оканчивается неудачей, и в программу возвращается код ошибки. Другим вариантом развития событий является автоматическое повторение попыток записи в течение заданного интервала времени.

Транзакции в SQLite

Управление транзакциями — одна из функций баз данных SQL, и SQLite также обрабатывает их. Транзакция — это последовательность изменений, в которой вы можете безопасно изменить базу данных, выполнив запрос и затем разместив .

Если по какой-то причине непосредственно перед фиксацией вы не хотите завершать транзакцию, вы можете вернуться в предыдущее состояние перед фиксацией, используя .

Точно так же мы также можем просматривать состояние базы данных через эти типы изменений.

import sqlite3

db_filename = 'database.db'

def display_table(conn):
    cursor = conn.cursor()
    cursor.execute('select name, size, date from images;')
    for name, size, date in cursor.fetchall():
        print(name, size, date)


with sqlite3.connect(db_filename) as conn1:
    print('Before changes:')
    display_table(conn1)

    cursor1 = conn1.cursor()
    cursor1.execute("""
    insert into images (name, size, date)
    values ('JournalDev.png', 2000, '2020-02-20');
    """)

    print('\nAfter changes in conn1:')
    display_table(conn1)

    print('\nBefore commit:')
    with sqlite3.connect(db_filename) as conn2:
        display_table(conn2)

    # Commit from the first connection
    conn1.commit()
    print('\nAfter commit:')
    with sqlite3.connect(db_filename) as conn3:
        display_table(conn3)

    cursor1.execute("""
    insert into images (name, size, date)
    values ('Hello.png', 200, '2020-01-18');
    """)

    print('\nBefore commit:')
    with sqlite3.connect(db_filename) as conn2:
        display_table(conn2)

    # Revert to changes before conn1's commit
    conn1.rollback()
    print('\nAfter connection 1 rollback:')
    with sqlite3.connect(db_filename) as conn4:
        display_table(conn4)

Выход

Before changes:
sample.png 100 2019-10-10
ask_python.png 450 2019-05-02
class_room.jpeg 1200 2018-04-07

After changes in conn1:
sample.png 100 2019-10-10
ask_python.png 450 2019-05-02
class_room.jpeg 1200 2018-04-07
JournalDev.png 2000 2020-02-20

Before commit:
sample.png 100 2019-10-10
ask_python.png 450 2019-05-02
class_room.jpeg 1200 2018-04-07

After commit:
sample.png 100 2019-10-10
ask_python.png 450 2019-05-02
class_room.jpeg 1200 2018-04-07
JournalDev.png 2000 2020-02-20

Before commit:
sample.png 100 2019-10-10
ask_python.png 450 2019-05-02
class_room.jpeg 1200 2018-04-07
JournalDev.png 2000 2020-02-20

After connection 1 rollback:
sample.png 100 2019-10-10
ask_python.png 450 2019-05-02
class_room.jpeg 1200 2018-04-07
JournalDev.png 2000 2020-02-20

Здесь, как видите, таблица изменяется только после того, как мы явно завершим транзакцию. Любые изменения до этого фактически не изменяют таблицу. Наконец, мы откатываем запись , чтобы она не вставлялась в таблицу.

Check if a table exists or not

When creating a table, we should make sure that the table is not already existed. Similarly, when removing/ deleting a table, the table should exist.

To check if the table doesn’t already exist, we use “if not exists” with the CREATE TABLE statement as follows:

create table if not exists table_name (column1, column2, …, columnN)

For example:

import sqlite3

con = sqlite3.connect('mydatabase.db')

def sql_fetch(con):

    cursorObj = con.cursor()

    cursorObj.execute('create table if not exists projects(id integer, name text)')

    con.commit()

sql_fetch(con)

Similarly, to check if the table exists when deleting, we use “if exists” with the DROP TABLE statement as follows:

drop table if exists table_name

For example,

cursorObj.execute('drop table if exists projects')

We can also check if the table we want to access exists or not by executing the following query:

cursorObj.execute('SELECT name from sqlite_master WHERE type = "table" AND name = "employees"')

print(cursorObj.fetchall())

If the employees’ table exists, it will return its name as follows:

If the table name we specified doesn’t exist, an empty array will be returned:

Select statement

You can use the select statement to select data from a particular table. If you want to select all the columns of the data from a table, you can use the asterisk (*). The syntax for this will be as follows:

select * from table_name

In SQLite3, the SELECT statement is executed in the execute method of the cursor object. For example, select all the columns of the employees’ table, run the following code:

cursorObj.execute('SELECT * FROM employees ')

If you want to select a few columns from a table, then specify the columns like the following:

select column1, column2 from tables_name

For example,

cursorObj.execute('SELECT id, name FROM employees')

The select statement selects the required data from the database table, and if you want to fetch the selected data, the fetchall() method of the cursor object is used. We will demonstrate this in the next section.

Выполнение запросов

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

name	size	date
pic1.jpg	50KB	2021-12-19
pic2.jpg	60KB	2021-12-20

Создание такой таблицы выполняется в несколько шагов. 

Создание таблицы и вставка значений с CREATE и INSERT

Любая таблица состоит из колонок. Эти колонки определяют типы данных. В нашем случае, чистый SQL запрос, который создаст таблицу, будет следующим:

Еще один запрос, который вставит эти данные в таблицу:

Что бы эти запросы были выполнены нужно использовать курсор одним из методов, которые были описаны выше. Это может выглядеть так:

Узнать сколько строк было изменено через запрос можно следующим образом:

Еще один метод total_changes(), который показывает сколько строк было изменено во всей базе с момента подключения:

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

Обновление данных с UPDATE

В языке SQL, обновить одно из значений в базе, можно с инструкцией UPDATE. Так мы, за счет чистого SQL, изменим название одной из строк созданных раннее:

С помощью sqlite и python этот запрос выполняется так же как и с INSERT:

Если commit не будет выполнен, то результат не будет сохранен в базе.

Чтение данных через SELECT

Чтение данных из базы выполняется через перемещение курсора. Этот процесс выполняется через методы fetchone или fetchall, которые возвращают кортеж:

fetchone() будет возвращать по одной строке после каждого вызова:

SQL запрос, который удалит строку используя колонку ‘name’, выглядит так:

Через код Python это будет выглядеть так:

Параметризация запроса

У вас может быть необходимость передать в SQL дополнительные параметры. Вы можете использовать форматирования строк Python, но это будет ошибкой:

Проблема этого способа в том, что пользователь может передать вместо ‘value’ другой SQL запрос. Такой действие называется SQL инъекцией. Каждый передаваемый параметр в SQL нуждается в дополнительных проверках и они уже реализованы в методе execute:

То есть вместо знаков ‘?’ у нас будут подставляться значения из кортежа.

Мы можем создать несколько запросов используя .executemany(). В примере ниже будет выполнено 2 разных ‘INSERT’ для каждого из кортежей (с точки зрения SQL такой подход считается плохой практикой):

Стандартные команды

Теперь давайте пройдёмся по списку стандартных команд sqlite3, которые предназначены для взаимодействия с базой данных. Стандартные команды могут быть классифицированы по трём группам:

• Язык описания данных DDL: команды для создания таблицы, изменения и удаления баз данных, таблиц и прочего.
• • CREATE
  • ALTER
  • DROP
• Язык управления данными DML: позволяют пользователю манипулировать данными (добавлять/изменять/удалять).
• • INSERT
  • UPDATE
  • DELETE
• Язык запросов DQL: позволяет осуществлять выборку данных.

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

Файлы баз данных SQLite являются кроссплатформенными. Они могут располагаться на различного рода устройствах.

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

• Имя
• Email
• Сайт
• Комментарий

Из всех этих полей только адрес сайта может быть пустым. Так же можем ввести колонку для нумерации комментриев. Назовём её .

Теперь давайте определимся с типами данных для каждой из колонок:

Атрибут	Тип данных
post_id	INTEGER
name	TEXT
email	TEXT
website_url	TEXT
comment	TEXT

Тут вы сможете найти все типы данных, поддерживаемые в SQLite3.

Так же следует отметить, в SQLite3 данные, вставляемые в колонку могут отличаться от указанного типа. В MySQL такое не пройдёт.

Теперь давайте создадим базу данных. Если вы ещё находитесь в интерфейсе sqlite3, то наберите команду для выхода. Теперь вводим:

В результате, в текущем каталоге у нас появится файл comment_section.db.

Заметка: если не указать название файла, sqlite3 создаст временную базу данных.

Создание таблицы

Для хранения комментариев нам необходимо создать таблицу. Назовём её . Выполняем команду:

CREATE TABLE comments (
    post_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT NOT NULL,
    website_url TEXT NULL,
    comment TEXT NOT NULL );

обеспечит уверенность, что ячейка не будет содержать пустое значение. и расширяют возможности поля post_id.

Чтобы убедиться в том, что таблица была создана, выполняем мета команду . В результате видим нашу таблицу .

Заметка: Для получения структуры таблицы наберите

Теперь можем внести данные в таблицу.

ВСТАВКА СТРОК

Предположим, что нам необходим внести следующую запись:

Name    : Shivam Mamgain
Email   : [email protected]
Website : shivammg.blogspot.com
Comment : Great tutorial for beginners.

Для вставки воспользуемся командой INSERT.

INSERT INTO comments ( name, email, website_url, comment )
VALUES ( 'Shivam Mamgain', '[email protected]',
'shivammg.blogspot.com', 'Great tutorial for beginners.' );

Указывать значение для не нужно т.к. оно сформируется автоматически благодаря настройке .

Чтобы набить руку можете вставить ещё несколько строк.

ВЫБОРКА

Для выборки данных воспользуемся командой SELECT.

SELECT post_id, name, email, website_url, comment
FROM comments;

Этот же запрос может выглядеть так:

SELECT *
FROM comments;

В результате из таблицы будут извлечены все строки. Результат может выглядеть без разграничения по колонкам и без заголовка. Чтобы это исправить выполняем:

Для отображения шапки введите .

Для отображения колонок выполните команду .

Выполняем SELECT запрос ещё раз.

Заметка: вид отображения можно изменить, воспользовавшись мета командой .

UPDATE comments
SET email = '[email protected]'
WHERE name = 'Shivam Mamgain';

В результате запись будет изменена.

Для выполнения команды DELETE нужно так же указать условие.

К примеру нам необходимо удалить комментарий с post_id = 9. Выполняем команду:

DELETE FROM comments
WHERE post_id = 9;

Для удаления комментариев пользователей ‘Bart Simpson’ и ‘Homer Simpson’ выполним:

DELETE FROM comments
WHERE name = 'Bart Simpson' OR name = 'Homer Simpson';

ИЗМЕНЕНИ СТРУКТУРЫ

Для добавления новой колонки следует использовать команду ALTER. К примеру введём поле username. Выполняем команду:

ALTER TABLE comments
ADD COLUMN username TEXT;

Данная команда создаст новое текстовое поле в таблице . Для всех сток в качестве значения будет выставлено NULL.

Так же мы можем использовать команду ALTER для переименования таблицы на .

ALTER TABLE comments
RENAME TO Coms;