Как сделать таблицу sqlite3 python

Обновлено: 02.07.2024

В этой главе вы узнаете, как использовать SQLite в программах на Python.

Монтаж

SQLite3 может быть интегрирован с Python с использованием модуля sqlite3, который был написан Герхардом Харингом. Он обеспечивает интерфейс SQL, совместимый со спецификацией DB-API 2.0, описанной в PEP 249. Вам не нужно устанавливать этот модуль отдельно, поскольку он поставляется по умолчанию вместе с Python версии 2.5.x и выше.

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

API модуля Python sqlite3

Ниже приведены важные подпрограммы модуля sqlite3, которые могут удовлетворить ваши требования по работе с базой данных SQLite из вашей программы Python. Если вы ищете более сложное приложение, вы можете посмотреть официальную документацию модуля Python sqlite3.

sqlite3.connect (база данных [, время ожидания, другие необязательные аргументы])

Когда доступ к базе данных осуществляется несколькими подключениями, и один из процессов изменяет базу данных, база данных SQLite блокируется, пока эта транзакция не будет зафиксирована. Параметр timeout указывает, как долго соединение должно ожидать, пока блокировка не исчезнет, до возникновения исключения. Значение по умолчанию для параметра времени ожидания — 5,0 (пять секунд).

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

connection.cursor ([cursorClass])

Эта подпрограмма создает курсор, который будет использоваться при программировании вашей базы данных на Python. Этот метод принимает один необязательный параметр cursorClass. Если указано, это должен быть пользовательский класс курсора, который расширяет sqlite3.Cursor.

cursor.execute (sql [, необязательные параметры])

Эта подпрограмма выполняет инструкцию SQL. Оператор SQL может быть параметризован (т. Е. Заполнители вместо литералов SQL). Модуль sqlite3 поддерживает два вида заполнителей: вопросительные знаки и именованные заполнители (именованный стиль).

connection.execute (sql [, необязательные параметры])

Эта подпрограмма является ярлыком вышеупомянутого метода execute, предоставленного объектом курсора, и она создает промежуточный объект курсора путем вызова метода курсора, а затем вызывает метод execute курсора с заданными параметрами.

cursor.executemany (sql, seq_of_parameters)

Эта подпрограмма выполняет команду SQL для всех последовательностей параметров или отображений, найденных в последовательности sql.

connection.executemany (sql [, параметры])

Эта подпрограмма представляет собой ярлык, который создает промежуточный объект курсора путем вызова метода курсора, а затем вызывает метод cursor.s executemany с заданными параметрами.

cursor.executescript (sql_script)

Эта подпрограмма выполняет несколько операторов SQL одновременно, предоставленных в форме сценария. Сначала он выдает оператор COMMIT, а затем выполняет сценарий SQL, полученный в качестве параметра. Все операторы SQL должны быть разделены точкой с запятой (;).

connection.executescript (sql_script)

Эта подпрограмма представляет собой ярлык, который создает промежуточный объект курсора путем вызова метода курсора, а затем вызывает метод исполняемого скрипта курсора с заданными параметрами.

connection.total_changes ()

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

Этот метод фиксирует текущую транзакцию. Если вы не вызываете этот метод, все, что вы сделали со времени последнего вызова commit (), не будет видно из других соединений с базой данных.

connection.rollback ()

Этот метод откатывает любые изменения в базе данных с момента последнего вызова commit ().

connection.close ()

Этот метод закрывает соединение с базой данных. Обратите внимание, что это не вызывает автоматически commit (). Если вы просто закроете соединение с базой данных без предварительного вызова commit (), ваши изменения будут потеряны!

cursor.fetchone ()

Этот метод извлекает следующую строку из набора результатов запроса, возвращая одну последовательность или None, если больше нет данных.

cursor.fetchmany ([size = cursor.arraysize])

Эта процедура извлекает следующий набор строк результата запроса, возвращая список. Пустой список возвращается, когда больше нет доступных строк. Метод пытается извлечь столько строк, сколько указано параметром size.

cursor.fetchall ()

Эта процедура извлекает все (оставшиеся) строки результата запроса, возвращая список. Пустой список возвращается, когда нет доступных строк.

sqlite3.connect (база данных [, время ожидания, другие необязательные аргументы])

connection.cursor ([cursorClass])

cursor.execute (sql [, необязательные параметры])

connection.execute (sql [, необязательные параметры])

cursor.executemany (sql, seq_of_parameters)

connection.executemany (sql [, параметры])

cursor.executescript (sql_script)

connection.executescript (sql_script)

connection.total_changes ()

connection.rollback ()

Этот метод откатывает любые изменения в базе данных с момента последнего вызова commit ().

connection.close ()

cursor.fetchone ()

cursor.fetchmany ([size = cursor.arraysize])

cursor.fetchall ()

Подключиться к базе данных

Следующий код Python показывает, как подключиться к существующей базе данных. Если база данных не существует, она будет создана и, наконец, будет возвращен объект базы данных.

Создать таблицу

Следующая программа Python будет использоваться для создания таблицы в ранее созданной базе данных.

ВСТАВИТЬ Операция

Следующая программа Python показывает, как создавать записи в таблице COMPANY, созданной в приведенном выше примере.

Когда вышеуказанная программа будет выполнена, она создаст указанные записи в таблице COMPANY и отобразит следующие две строки:

ВЫБЕРИТЕ Операцию

Следующая программа на Python показывает, как извлекать и отображать записи из таблицы COMPANY, созданной в приведенном выше примере.

Когда вышеуказанная программа будет выполнена, она даст следующий результат.

ОБНОВЛЕНИЕ Операция

Следующий код Python показывает, как использовать инструкцию UPDATE для обновления любой записи, а затем извлекать и отображать обновленные записи из таблицы COMPANY.

Когда вышеуказанная программа будет выполнена, она даст следующий результат.

УДАЛЕНИЕ Операция

Следующий код Python показывает, как использовать инструкцию DELETE для удаления любой записи, а затем извлечь и отобразить оставшиеся записи из таблицы COMPANY.

Когда вышеуказанная программа будет выполнена, она даст следующий результат.

В этой большой статьей, мы с вами сегодня познакомимся с одним из самых популярных встроенных модулей, для работы с базой данных python sqlite . SQLite предназначен для хранения информации, и для его удобного чтения. На этом уроке, мы разберем, все основные моменты по работе с ним.

Создание новой базы данных SQLite

Как и в примере с csv файлами, при попытке подключения к несуществующей базе данных, она автоматически создастся. И так, что бы начать работу с базой данных, нам ее надо сначала создать, как обычно, рассмотрим сначала код, затем разберем как это работает.

Импортируем непосредственно сам модуль SQLite
Создаем функцию подключения create_connection , в качестве аргумента передаем нашу БД
Вызываем функцию connect() модуля SQLite
Используем блок try…except , что бы в случае ошибке вывести ее на экран, в случае успеха, мы увидим версию SQLite
Затем после всех наших операций, незабываем закрыть соединение

После запуска, вы увидите, что по указанному пути, у вас появился файл pythonsqlite.db .

Создание базы данных в оперативной памяти

Базу данных можно создать и внутри вашей оперативной памяти. Для этого вам достаточно передать имя файла как :memory: методу connect() , после чего, у вас создастся новая БД внутри вашего ОЗУ. Ниже приведен пример кода.

Создание таблиц в SQLite python

Для создания новой таблицы в SQLite из программы Python, нам необходимо выполнить следующие действия:

Создаем объект Connection используя метод connect() модуля sqlite3
Создаем объект cursor , вызвав метод cursor объекта Connection
Передаем инструкцию CREATE TABLE методу execute() объекта Cursor

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

Сначала приведу полный листинг кода, а потом поэтапно разберем, как это все работает.

Создаем функцию create_connection() для соединения с базой данных, которая возвращает объект conn . Объект conn представляет базу SQLite .
Создаем функцию create_table() , которая принимает объект conn , и инструкцию SQL create_table_sql . Внутри нашей функции вызываем метод execute() объекта Cursor() , что бы выполнить запрос в БД.
Создаем функцию main() , для создания таблиц projects и tasks
Запускаем функцию main()

После запуска нашей программы, вы увидите, что в вашей базе данных, создались две таблицы project и tasks .

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

В этом руководстве мы поговорим о модуле sqlite3 в Python 3. Чтобы потренироваться, мы создадим соединение с базой данных SQLite, добавим в эту БД таблицу, вставим данные, а также извлечем и отредактируем их.

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

Требования

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

Для получения необходимой справочной информации вы можете просмотреть эти ресурсы:

1: Создание подключения к базе данных SQLite

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

Подключиться к базе данных SQLite можно с помощью модуля sqlite3:

import sqlite3
connection = sqlite3.connect("aquarium.db")

Строка import sqlite3 дает программе Python доступ к модулю sqlite3. Функция sqlite3.connect () возвращает объект Connection, который мы будем использовать для взаимодействия с базой данных SQLite, хранящейся в файле aquarium.db. Файл aquarium.db создается функцией sqlite3.connect() автоматически (если такой файл еще не существует на компьютере).

Чтобы убедиться, что объект connection создан успешно, запустите:

Если мы запустим этот код Python, мы увидим такой результат:

connection.total_changes в команде выше – это общее количество строк базы данных, которые были изменены объектом connection. Поскольку мы еще не выполнили ни одной команды SQL, мы ожидаем получить 0.

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

Примечание: Также можно подключиться к базе данных SQLite, которая находится строго в памяти (а не в файле), передав функции sqlite3.connect()специальную строку “:memory:”. Например:

Помните: такая база данных SQLite исчезнет, как только Python завершит работу. Это может быть удобно, если вам нужна временная песочница, в которой вы могли бы опробовать какие-то функции, и вы не хотите сохранять данные после выхода из программы.

2: Добавление данных в БД SQLite

После того как мы подключились к базе данных aquarium.db, мы можем попробовать вставить в нее данные и извлечь их.

В базах SQL данные хранятся в таблицах. Таблицы определяют набор столбцов и содержат 0 или более строк с данными для каждого столбца.

Давайте создадим таблицу fish, в которой будут такие данные:

name	species	tank_number
Willy	shark	1
Jamie	cuttlefish	7

Итак, таблица fish будет содержать имя (столбец name), вид (species) и номер резервуара (tank_number) для каждой рыбы в аквариуме. Согласно записям, пока что в аквариуме есть два жителя: акула Вилли и каракатица по имени Джейми.

Создать эту таблицу рыб в SQLite можно при помощи соединения, которое мы установили в разделе 1:

cursor = connection.cursor()
cursor.execute("CREATE TABLE fish (name TEXT, species TEXT, tank_number INTEGER)")

connection.cursor() возвращает объект Cursor. Такие объекты позволяют отправлять SQL-операторы в базу данных SQLite с помощью cursor.execute(). Строка “CREATE TABLE fish …” – это SQL-оператор, который создает таблицу fish с тремя описанными столбцами: name (с типом данных TEXT), species (также с типом TEXT) и tank_number (с типом INTEGER).

Теперь, когда мы создали таблицу, мы можем вставить в нее строки данных:

cursor.execute("INSERT INTO fish VALUES ('Willy', 'shark', 1)")
cursor.execute("INSERT INTO fish VALUES ('Jamie', 'cuttlefish', 7)")

Мы вызываем cursor.execute() дважды: один раз, чтобы вставить строку об акуле Вилли из резервуара 1, и еще раз, чтобы вставить строку о каракатице Джейми из резервуара 7. “INSERT INTO fish VALUES …” – это SQL-оператор, который позволяет добавлять строки в таблицу.

В следующем разделе мы познакомимся с SQL-оператором SELECT:он поможет нам проверить строки, которые мы только что вставили в нашу таблицу.

3: Извлечение данных из БД SQLite

В разделе 2 мы добавили в таблицу по имени fish две строки. Извлечь эти строки можно с помощью оператора SELECT:

rows = cursor.execute("SELECT name, species, tank_number FROM fish").fetchall()
print(rows)

Если запустить этот код, мы получим такой результат:

[('Willy', 'shark', 1), ('Jamie', 'cuttlefish', 7)]

Функция cursor.execute() запускает оператор SELECT для получения значений столбцов name, species и tank_number в таблице fish. Функция fetchall() извлекает все результаты оператора SELECT. Когда мы с помощью print(rows) выводим строки на экран, мы видим список из двух кортежей. Каждый кортеж состоит из трех записей – по одной записи для каждого столбца из таблицы. Оба кортежа содержат данные, которые мы вставили в таблицу в предыдущем разделе.

target_fish_name = "Jamie"
rows = cursor.execute(
"SELECT name, species, tank_number FROM fish WHERE name = ?",
(target_fish_name,),
).fetchall()
print(rows)
Это даст следующий результат:
[('Jamie', 'cuttlefish', 7)]

4: Изменение данных в БД SQLite

Строки в базе данных SQLite можно изменять с помощью SQL-операторов UPDATE и DELETE.

Предположим, что акула Вилли переехала в резервуар №2. Следовательно, нам нужно изменить строку в таблице, чтобы отразить это изменение:

new_tank_number = 2
moved_fish_name = "Willy"
cursor.execute(
"UPDATE fish SET tank_number = ? WHERE name = ?",
(new_tank_number, moved_fish_name)
)

SQL-оператор UPDATE изменит значение tank_number для записи Willy на 2. Оператор WHERE внутри UPDATE отфильтрует все остальные записи: значение tank_number изменится только в том случае, если name = “Willy”.

Давайте запустим следующий оператор SELECT, чтобы подтвердить, что данные обновлены правильно:

rows = cursor.execute("SELECT name, species, tank_number FROM fish").fetchall()
print(rows)

Мы увидим следующий результат:

[('Willy', 'shark', 2), ('Jamie', 'cuttlefish', 7)]

Обратите внимание, в строке для Willy столбец tank_number теперь имеет значение 2.

Допустим, акула Вилли была выпущена в дикую природу и больше не живет в нашем аквариуме. Значит, имеет смысл удалить эту строку из таблицы.

Выполните SQL-оператор DELETE, чтобы удалить строку:

released_fish_name = "Willy"
cursor.execute(
"DELETE FROM fish WHERE name = ?",
(released_fish_name,)
)

SQL-оператор DELETE удалит строку, а оператор WHERE поможет ему найти необходимую: строка будет удалена только в том случае, если name = “Willy”.

Следующий оператор SELECT подтвердит, что удаление выполнено правильно:

rows = cursor.execute("SELECT name, species, tank_number FROM fish").fetchall()
print(rows)

Мы получим следующий результат:

[('Jamie', 'cuttlefish', 7)]

Обратите внимание, строки про акулу Вилли больше нет в таблице, остается только каракатица Джейми.

5: Оператор with

В течение работы с этим руководством мы использовали два основных объекта для взаимодействия с базой данных aquarium.db: объект Connection по имени connection и объект Cursor по имени cursor.

Когда файлы Python больше не используются, их нужно закрывать – точно так же следует закрыть и объекты Connection и Cursor, когда они больше не нужны.

Автоматически закрывать объекты Connection и Cursor можно при помощи оператора with.

from contextlib import closing
with closing(sqlite3.connect("aquarium.db")) as connection:
with closing(connection.cursor()) as cursor:
rows = cursor.execute("SELECT 1").fetchall()
print(rows)

closing – это удобная функция, предоставляемая модулем contextlib. Когда оператор with завершает работу, closing вызывает функцию close()для любого переданного ему объекта. В этом примере closing используется дважды: один раз для объекта Connection, второй раз – для объекта Cursor.

Этот код даст нам следующий результат:

Поскольку оператор SELECT 1 всегда возвращает одну строку с одним столбцом со значением 1, в результате мы получаем кортеж с единственным значением 1.

Заключение

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

SQLite — компактная встраиваемая СУБД с открытым кодом.

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

В этой статье вы узнаете о том как работать с SQLite3 в Python 3.

Импорт из .csv файла

Допустим, у вас есть файл data.csv значения в котором разделены точкой с запятой.

Нужно импортировать содержимое этого файла в базу данных SQLite3

Вы можете найти в этой статье несколько способов сделать такой импорт.

Простейший пример

Создайте файл from_csv.py и копируйте туда код из примеров.

Запустить файл можно командой python3 from_csv.py

В директории db должен появиться файл my.db

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

Более реальный пример

Теперь рассмотрим более близкий к реальности пример.

В таблице должен быть столбец id. Желательно генерировать его автоматически.

Также удобнее читать код, оформленный с помощью docstring

Рассмотрим файл partners.csv со списком сайтов

В файле по три значения на строку: name, url, area. Добавим эти столбцы в базу данных.

В названиях присутствуют кириллические символы, поэтому при открытии укажем кодировку encoding="utf8"

Читайте также: