Руководство по sqlite python

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

• Загрузка библиотеки
• Создание и соединение с базой данных
• Создание таблиц базы данных
• Добавление данных
• Запросы на получение данных
• Удаление данных
• И многое другое!

SQLite3 (часто говорят просто SQLite) — это часть стандартного пакета Python 3, поэтому ничего дополнительно устанавливать не придется.

Что будем создавать

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

Типы данных SQLite в Python

SQLite для Python предлагает меньше типов данных, чем есть в других реализациях SQL. С одной стороны, это накладывает ограничения, но, с другой стороны, в SQLite многое сделано проще. Вот основные типы:

• NULL — значение NULL
• INTEGER — целое число
• REAL — число с плавающей точкой
• TEXT — текст
• BLOB — бинарное представление крупных объектов, хранящееся в точности с тем, как его ввели

К сожалению, других привычных для SQL типов данных в SQLite нет.

Первые шаги с SQLite в Python

Начнем руководство с загрузки библиотеки. Для этого нужно использовать следующую команду:

import sqlite3

Следующий шаг — создание базы данных.

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

Есть несколько способов создания базы данных в Python с помощью SQLite. Для этого используется объект Connection, который и представляет собой базу. Он создается с помощью функции connect().

Создадим файл .db, поскольку это стандартный способ управления базой SQLite. Файл будет называться orders.db. За соединение будет отвечать переменная conn.

conn = sqlite3.connect('orders.db')

Эта строка создает объект connection, а также новый файл orders.db в рабочей директории. Если нужно использовать другую, ее нужно обозначить явно:

conn = sqlite3.connect(r'ПУТЬ-К-ПАПКИ/orders.db')

Если файл уже существует, то функция connect осуществит подключение к нему.

перед строкой с путем стоит символ «r». Это дает понять Python, что речь идет о «сырой» строке, где символы «/» не отвечают за экранирование.

Функция connect создает соединение с базой данных SQLite и возвращает объект, представляющий ее.

Резидентная база данных

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

conn = sqlite3.connect(:memory:)

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

Создание объекта cursor

После создания объекта соединения с базой данных нужно создать объект cursor. Он позволяет делать SQL-запросы к базе. Используем переменную cur для хранения объекта:

cur = conn.cursor()

Теперь выполнять запросы можно следующим образом:

cur.execute("ВАШ-SQL-ЗАПРОС-ЗДЕСЬ;")

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

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

Пришло время создать первую таблицу в базе данных. С объектами соединения (conn) и cursor (cur) это можно сделать. Будем следовать этой схеме.

Начнем с таблицы users.

cur.execute("""CREATE TABLE IF NOT EXISTS users(
   userid INT PRIMARY KEY,
   fname TEXT,
   lname TEXT,
   gender TEXT);
""")
conn.commit()

В коде выше выполняются следующие операции:

1. Функция execute отвечает за SQL-запрос
2. SQL генерирует таблицу users
3. IF NOT EXISTS поможет при попытке повторного подключения к базе данных. Запрос проверит, существует ли таблица. Если да — проверит, ничего ли не поменялось.
4. Создаем первые четыре колонки: userid, fname, lname и gender. Userid — это основной ключ.
5. Сохраняем изменения с помощью функции commit для объекта соединения.

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

cur.execute("""CREATE TABLE IF NOT EXISTS orders(
   orderid INT PRIMARY KEY,
   date TEXT,
   userid TEXT,
   total TEXT);
""")
conn.commit()

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

Добавление данных с SQLite в Python

По аналогии с запросом для создания таблиц для добавления данных также нужно использовать объект cursor.

cur.execute("""INSERT INTO users(userid, fname, lname, gender) 
   VALUES('00001', 'Alex', 'Smith', 'male');""")
conn.commit()

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

user = ('00002', 'Lois', 'Lane', 'Female')

Если его нужно загрузить в базу данных, тогда подойдет следующий формат:

cur.execute("INSERT INTO users VALUES(?, ?, ?, ?);", user)
conn.commit()

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

Важно заметить, что SQLite ожидает получить значения в формате кортежа. Однако в переменной может быть и список с набором кортежей. Таким образом можно добавить несколько пользователей:

more_users = [('00003', 'Peter', 'Parker', 'Male'), ('00004', 'Bruce', 'Wayne', 'male')]

Но нужно использовать функцию executemany вместо обычной execute:

cur.executemany("INSERT INTO users VALUES(?, ?, ?, ?);", more_users)
conn.commit()

Если применить execute, то функция подумает, то пользователь хочет передать в таблицу два объекта (два кортежа), а не два кортежа, каждый из которых содержит по 4 значения для каждого пользователя. Хотя в первую очередь вообще должна была возникнуть ошибка.

SQLite и предотвращение SQL-инъекций

Использование способа с вопросительными знаками (?, ?, …) также помогает противостоять SQL-инъекциям. Поэтому рекомендуется использовать его, а не упомянутый до этого.

Скрипты для загрузки данных

Следующие скрипты можно скопировать и вставить для добавления данных в обе таблицы:

customers = [
  ('00005', 'Stephanie', 'Stewart', 'female'), ('00006', 'Sincere', 'Sherman', 'female'), ('00007', 'Sidney', 'Horn', 'male'), 
  ('00008', 'Litzy', 'Yates', 'female'), ('00009', 'Jaxon', 'Mills', 'male'), ('00010', 'Paul', 'Richard', 'male'), 
  ('00011', 'Kamari', 'Holden', 'female'), ('00012', 'Gaige', 'Summers', 'female'), ('00013', 'Andrea', 'Snow', 'female'), 
  ('00014', 'Angelica', 'Barnes', 'female'), ('00015', 'Leah', 'Pitts', 'female'), ('00016', 'Dillan', 'Olsen', 'male'), 
  ('00017', 'Joe', 'Walsh', 'male'), ('00018', 'Reagan', 'Cooper', 'male'), ('00019', 'Aubree', 'Hogan', 'female'), 
  ('00020', 'Avery', 'Floyd', 'male'), ('00021', 'Elianna', 'Simmons', 'female'), ('00022', 'Rodney', 'Stout', 'male'), 
  ('00023', 'Elaine', 'Mcintosh', 'female'), ('00024', 'Myla', 'Mckenzie', 'female'), ('00025', 'Alijah', 'Horn', 'female'),
  ('00026', 'Rohan', 'Peterson', 'male'), ('00027', 'Irene', 'Walters', 'female'), ('00028', 'Lilia', 'Sellers', 'female'), 
  ('00029', 'Perla', 'Jefferson', 'female'), ('00030', 'Ashley', 'Klein', 'female')
]
orders = [
  ('00001', '2020-01-01', '00025', '178'), ('00002', '2020-01-03', '00025', '39'), ('00003', '2020-01-07', '00016', '153'), 
  ('00004', '2020-01-10', '00015', '110'), ('00005', '2020-01-11', '00024', '219'), ('00006', '2020-01-12', '00029', '37'), 
  ('00007', '2020-01-14', '00028', '227'), ('00008', '2020-01-18', '00010', '232'), ('00009', '2020-01-22', '00016', '236'), 
  ('00010', '2020-01-26', '00017', '116'), ('00011', '2020-01-28', '00028', '221'), ('00012', '2020-01-31', '00021', '238'), 
  ('00013', '2020-02-02', '00015', '177'), ('00014', '2020-02-05', '00025', '76'), ('00015', '2020-02-08', '00022', '245'), 
  ('00016', '2020-02-12', '00008', '180'), ('00017', '2020-02-14', '00020', '190'), ('00018', '2020-02-18', '00030', '166'),
  ('00019', '2020-02-22', '00002', '168'), ('00020', '2020-02-26', '00021', '174'), ('00021', '2020-02-29', '00017', '126'), 
  ('00022', '2020-03-02', '00019', '211'), ('00023', '2020-03-05', '00030', '144'), ('00024', '2020-03-09', '00012', '112'), 
  ('00025', '2020-03-10', '00006', '45'), ('00026', '2020-03-11', '00004', '200'), ('00027', '2020-03-14', '00015', '226'), 
  ('00028', '2020-03-17', '00030', '189'), ('00029', '2020-03-20', '00004', '152'), ('00030', '2020-03-22', '00026', '239'), 
  ('00031', '2020-03-23', '00012', '135'), ('00032', '2020-03-24', '00013', '211'), ('00033', '2020-03-27', '00030', '226'), 
  ('00034', '2020-03-28', '00007', '173'), ('00035', '2020-03-30', '00010', '144'), ('00036', '2020-04-01', '00017', '185'), 
  ('00037', '2020-04-03', '00009', '95'), ('00038', '2020-04-06', '00009', '138'), ('00039', '2020-04-10', '00025', '223'), 
  ('00040', '2020-04-12', '00019', '118'), ('00041', '2020-04-15', '00024', '132'), ('00042', '2020-04-18', '00008', '238'), 
  ('00043', '2020-04-21', '00003', '50'), ('00044', '2020-04-25', '00019', '98'), ('00045', '2020-04-26', '00017', '167'), 
  ('00046', '2020-04-28', '00009', '215'), ('00047', '2020-05-01', '00014', '142'), ('00048', '2020-05-05', '00022', '173'),
  ('00049', '2020-05-06', '00015', '80'), ('00050', '2020-05-07', '00017', '37'), ('00051', '2020-05-08', '00002', '36'), 
  ('00052', '2020-05-10', '00022', '65'), ('00053', '2020-05-14', '00019', '110'), ('00054', '2020-05-18', '00017', '36'), 
  ('00055', '2020-05-21', '00008', '163'), ('00056', '2020-05-24', '00024', '91'), ('00057', '2020-05-26', '00028', '154'), 
  ('00058', '2020-05-30', '00022', '130'), ('00059', '2020-05-31', '00017', '119'), ('00060', '2020-06-01', '00024', '137'), 
  ('00061', '2020-06-03', '00017', '206'), ('00062', '2020-06-04', '00013', '100'), ('00063', '2020-06-05', '00021', '187'), 
  ('00064', '2020-06-09', '00025', '170'), ('00065', '2020-06-11', '00011', '149'), ('00066', '2020-06-12', '00007', '195'), 
  ('00067', '2020-06-14', '00015', '30'), ('00068', '2020-06-16', '00002', '246'), ('00069', '2020-06-20', '00028', '163'),
  ('00070', '2020-06-22', '00005', '184'), ('00071', '2020-06-23', '00022', '68'), ('00072', '2020-06-27', '00013', '92'), 
  ('00073', '2020-06-30', '00022', '149'), ('00074', '2020-07-04', '00002', '65'), ('00075', '2020-07-05', '00017', '88'), 
  ('00076', '2020-07-09', '00007', '156'), ('00077', '2020-07-13', '00010', '26'), ('00078', '2020-07-16', '00008', '55'), 
  ('00079', '2020-07-20', '00019', '81'), ('00080', '2020-07-22', '00011', '78'), ('00081', '2020-07-23', '00026', '166'), 
  ('00082', '2020-07-27', '00014', '65'), ('00083', '2020-07-30', '00021', '205'), ('00084', '2020-08-01', '00026', '140'), 
  ('00085', '2020-08-05', '00006', '236'), ('00086', '2020-08-06', '00021', '208'), ('00087', '2020-08-07', '00021', '169'), 
  ('00088', '2020-08-08', '00004', '157'), ('00089', '2020-08-11', '00017', '71'), ('00090', '2020-08-13', '00025', '89'), 
  ('00091', '2020-08-16', '00014', '249'), ('00092', '2020-08-18', '00012', '59'), ('00093', '2020-08-19', '00013', '121'),
  ('00094', '2020-08-20', '00025', '179'), ('00095', '2020-08-22', '00017', '208'), ('00096', '2020-08-26', '00024', '217'), 
  ('00097', '2020-08-28', '00004', '206'), ('00098', '2020-08-30', '00017', '114'), ('00099', '2020-08-31', '00017', '169'), 
  ('00100', '2020-09-02', '00022', '226')
]

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

cur.executemany("INSERT INTO users VALUES(?, ?, ?, ?);", customers)
cur.executemany("INSERT INTO orders VALUES(?, ?, ?, ?);", orders)
conn.commit()

Получение данных с SQLite в Python

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

Использование fetchone() в SQLite в Python

Начнем с использования функции fetchone(). Создадим переменную one_result для получения только одного результата:

cur.execute("SELECT * FROM users;")
one_result = cur.fetchone()
print(one_result)

Она вернет следующее:

[(1, 'Alex', 'Smith', 'male')]

Использование fetchmany() в SQLite в Python

Если же нужно получить много данных, то используется функция fetchmany(). Выполним другой скрипт для генерации 3 результатов:

cur.execute("SELECT * FROM users;")
three_results = cur.fetchmany(3)
print(three_results)

Он вернет следующее:

[(1, 'Alex', 'Smith', 'male'), (2, 'Lois', 'Lane', 'Female'), (3, 'Peter', 'Parker', 'Male')]

Использование fetchall() в SQLite в Python

Функцию fetchall() можно использовать для получения всех результатов. Вот что будет, если запустить скрипт:

cur.execute("SELECT * FROM users;")
all_results = cur.fetchall()
print(all_results)

Удаление данных в SQLite в Python

Теперь рассмотрим процесс удаления данных с SQLite в Python. Здесь та же структура. Предположим, нужно удалить любого пользователя с фамилией «Parker». Напишем следующее:

cur.execute("DELETE FROM users WHERE lname='Parker';")
conn.commit()

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

cur.execute("select * from users where lname='Parker'")
print(cur.fetchall())

Будет выведен пустой список, подтверждающий, что запись удалена.

Объединение таблиц в SQLite в Python

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

Для этого напишем следующее:

cur.execute("""SELECT *, users.fname, users.lname FROM orders
    LEFT JOIN users ON users.userid=orders.userid;""")
print(cur.fetchall())

Тот же подход работает с другими SQL-операциями.

Выводы

В этом материале вы узнали все, что требуется для работы с SQLite в Python: загрузка библиотеки, создание баз и таблиц, добавление, запрос и удаление данных.

Source code: Lib/sqlite3/

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 a SQL interface
compliant with the DB-API 2.0 specification described by PEP 249.

To use the module, you must first create a Connection object that
represents the database. Here the data will be stored in the
example.db file:

import sqlite3
conn = sqlite3.connect('example.db')

You can also supply the special name :memory: to create a database in RAM.

Once you have a Connection, you can create a Cursor object
and call its execute() method to perform SQL commands:

c = conn.cursor()

# Create table
c.execute('''CREATE TABLE stocks
             (date text, trans text, symbol text, qty real, price real)''')

# Insert a row of data
c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")

# Save (commit) the changes
conn.commit()

# We can also close the connection if we are done with it.
# Just be sure any changes have been committed or they will be lost.
conn.close()

The data you’ve saved is persistent and is available in subsequent sessions:

import sqlite3
conn = sqlite3.connect('example.db')
c = conn.cursor()

Usually your SQL operations will need to use values from Python variables. 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
(see https://xkcd.com/327/ for humorous example of what can go wrong).

Instead, use the DB-API’s parameter substitution. 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. (Other database
modules may use a different placeholder, such as %s or :1.) For
example:

# Never do this -- insecure!
symbol = 'RHAT'
c.execute("SELECT * FROM stocks WHERE symbol = '%s'" % symbol)

# Do this instead
t = ('RHAT',)
c.execute('SELECT * FROM stocks WHERE symbol=?', t)
print(c.fetchone())

# Larger example that inserts many records at a time
purchases = [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
             ('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
             ('2006-04-06', 'SELL', 'IBM', 500, 53.00),
            ]
c.executemany('INSERT INTO stocks VALUES (?,?,?,?,?)', purchases)

To retrieve data after executing a SELECT statement, you can either treat the
cursor as an iterator, call the cursor’s fetchone() method to
retrieve a single matching row, or call fetchall() to get a list of the
matching rows.

This example uses the iterator form:

>>> for row in c.execute('SELECT * FROM stocks ORDER BY price'):
        print(row)

('2006-01-05', 'BUY', 'RHAT', 100, 35.14)
('2006-03-28', 'BUY', 'IBM', 1000, 45.0)
('2006-04-06', 'SELL', 'IBM', 500, 53.0)
('2006-04-05', 'BUY', 'MSFT', 1000, 72.0)

conn = sqlite3.connect(":memory:")
c = conn.cursor()
c.execute('''create table stocks
(date text, trans text, symbol text,
 qty real, price real)''')
c.execute("""insert into stocks
          values ('2006-01-05','BUY','RHAT',100,35.14)""")
conn.commit()
c.close()

>>> conn.row_factory = sqlite3.Row
>>> c = conn.cursor()
>>> c.execute('select * from stocks')
<sqlite3.Cursor object at 0x7f4e7dd8fa80>
>>> r = c.fetchone()
>>> type(r)
<class 'sqlite3.Row'>
>>> tuple(r)
('2006-01-05', 'BUY', 'RHAT', 100.0, 35.14)
>>> len(r)
5
>>> r[2]
'RHAT'
>>> r.keys()
['date', 'trans', 'symbol', 'qty', 'price']
>>> r['qty']
100.0
>>> for member in r:
...     print(member)
...
2006-01-05
BUY
RHAT
100.0
35.14

class Point:
    def __init__(self, x, y):
        self.x, self.y = x, y

import sqlite3

class Point:
    def __init__(self, x, y):
        self.x, self.y = x, y

    def __conform__(self, protocol):
        if protocol is sqlite3.PrepareProtocol:
            return "%f;%f" % (self.x, self.y)

con = sqlite3.connect(":memory:")
cur = con.cursor()

p = Point(4.0, -3.2)
cur.execute("select ?", (p,))
print(cur.fetchone()[0])

import sqlite3

class Point:
    def __init__(self, x, y):
        self.x, self.y = x, y

def adapt_point(point):
    return "%f;%f" % (point.x, point.y)

sqlite3.register_adapter(Point, adapt_point)

con = sqlite3.connect(":memory:")
cur = con.cursor()

p = Point(4.0, -3.2)
cur.execute("select ?", (p,))
print(cur.fetchone()[0])

import sqlite3
import datetime
import time

def adapt_datetime(ts):
    return time.mktime(ts.timetuple())

sqlite3.register_adapter(datetime.datetime, adapt_datetime)

con = sqlite3.connect(":memory:")
cur = con.cursor()

now = datetime.datetime.now()
cur.execute("select ?", (now,))
print(cur.fetchone()[0])

def convert_point(s):
    x, y = map(float, s.split(b";"))
    return Point(x, y)

import sqlite3

class Point:
    def __init__(self, x, y):
        self.x, self.y = x, y

    def __repr__(self):
        return "(%f;%f)" % (self.x, self.y)

def adapt_point(point):
    return ("%f;%f" % (point.x, point.y)).encode('ascii')

def convert_point(s):
    x, y = list(map(float, s.split(b";")))
    return Point(x, y)

# Register the adapter
sqlite3.register_adapter(Point, adapt_point)

# Register the converter
sqlite3.register_converter("point", convert_point)

p = Point(4.0, -3.2)

#########################
# 1) Using declared types
con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
cur = con.cursor()
cur.execute("create table test(p point)")

cur.execute("insert into test(p) values (?)", (p,))
cur.execute("select p from test")
print("with declared types:", cur.fetchone()[0])
cur.close()
con.close()

#######################
# 1) Using column names
con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_COLNAMES)
cur = con.cursor()
cur.execute("create table test(p)")

cur.execute("insert into test(p) values (?)", (p,))
cur.execute('select p as "p [point]" from test')
print("with column names:", cur.fetchone()[0])
cur.close()
con.close()

import sqlite3
import datetime

con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
cur = con.cursor()
cur.execute("create table test(d date, ts timestamp)")

today = datetime.date.today()
now = datetime.datetime.now()

cur.execute("insert into test(d, ts) values (?, ?)", (today, now))
cur.execute("select d, ts from test")
row = cur.fetchone()
print(today, "=>", row[0], type(row[0]))
print(now, "=>", row[1], type(row[1]))

cur.execute('select current_date as "d [date]", current_timestamp as "ts [timestamp]"')
row = cur.fetchone()
print("current_date", row[0], type(row[0]))
print("current_timestamp", row[1], type(row[1]))

import sqlite3

persons = [
    ("Hugo", "Boss"),
    ("Calvin", "Klein")
    ]

con = sqlite3.connect(":memory:")

# Create the table
con.execute("create table person(firstname, lastname)")

# Fill the table
con.executemany("insert into person(firstname, lastname) values (?, ?)", persons)

# Print the table contents
for row in con.execute("select firstname, lastname from person"):
    print(row)

print("I just deleted", con.execute("delete from person").rowcount, "rows")

import sqlite3

con = sqlite3.connect(":memory:")
con.row_factory = sqlite3.Row

cur = con.cursor()
cur.execute("select 'John' as name, 42 as age")
for row in cur:
    assert row[0] == row["name"]
    assert row["name"] == row["nAmE"]
    assert row[1] == row["age"]
    assert row[1] == row["AgE"]

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("create table person (id integer primary key, firstname varchar unique)")

# Successful, con.commit() is called automatically afterwards
with con:
    con.execute("insert into person(firstname) values (?)", ("Joe",))

# con.rollback() is called after the with block finishes with an exception, the
# exception is still raised and must be caught
try:
    with con:
        con.execute("insert into person(firstname) values (?)", ("Joe",))
except sqlite3.IntegrityError:
    print("couldn't add Joe twice")

В прошлой статье мы рассказали про SQLite — простую базу данных, которая может работать почти на любой платформе. Теперь проверим теорию на практике: напишем простой код на Python, который сделает нам простую базу и наполнит её данными и связями.

Предыстория

Если это первая статья про базы данных, которую вы читаете, то лучше сделать так, а потом вернуться сюда:

Что будем делать

Сегодня мы сделаем то же самое, что и в SQL-запросах, но на Python, используя стандартную библиотеку sqlite3:

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

Подключаем и создаём базу данных

За работу с SQLite в Python отвечает стандартная библиотека sqlite3:

# подключаем SQLite
import sqlite3 as sl

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

Получается, нам неважно, есть файл с базой или нет — мы в любом случае после запуска получим то, что нам нужно. Для этого пишем команду:

# открываем файл с базой данных
con = sl.connect('thecode.db')

Мы указали, что файл называется thecode.db, без указания папок и дисков. Это значит, что файл с базой появится в той же папке, что и наш скрипт — можно в этом убедиться после запуска программы.

Создаём таблицу с товарами

У нас есть база, в которой можно создавать таблицы для хранения данных. Создадим первую таблицу для товаров:

with con:
    con.execute("""
        CREATE TABLE goods (
           product VARCHAR(20) PRIMARY KEY,
           count INTEGER,
           price INTEGER

);
    """)

Если посмотреть внимательно на код, можно заметить, что текст внутри кавычек полностью повторяет обычный SQL-запрос, который мы уже использовали в прошлой статье. Единственное отличие — в SQLite используется INTEGER вместо INT:

CREATE TABLE goods (
    product VARCHAR(20) PRIMARY KEY,
    count INT,
    price INT
);

Теперь соберём код вместе и запустим его ещё раз:

# подключаем SQLite
import sqlite3 as sl

# открываем файл с базой данных
con = sl.connect('thecode.db')

# создаём таблицу для товаров
with con:
    con.execute("""
        CREATE TABLE goods (
            product VARCHAR(20) PRIMARY KEY,
            count INTEGER,
            price INTEGER

);
    """)

Но после второго запуска компьютер почему-то выдаёт ошибку:

❌ sqlite3.OperationalError: table goods already exists

Дело в том, что при повторном запуске программа пытается создать таблицу с товарами, которая уже есть в базе. Так как имена таблиц совпадают, а двух одинаковых имён быть не может, отсюда и возникает ошибка.

Чтобы не попадать в такую ситуацию, добавим проверку: посмотрим, есть ли в базе нужная нам таблица или нет. Если нет — создаём, если есть — двигаемся дальше:

# открываем базу
with con:
    # получаем количество таблиц с нужным нам именем
    data = con.execute("select count(*) from sqlite_master where type='table' and name='goods'")
    for row in data:
        # если таких таблиц нет
        if row[0] == 0:
            
            # создаём таблицу для товаров
            with con:
                con.execute("""
                    CREATE TABLE goods (
                        product VARCHAR(20) PRIMARY KEY,
                        count INTEGER,
                        price INTEGER
                    );
                """)

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

Теперь наполняем нашу таблицу товарами, используя стандартный SQL-запрос. Например, можно добавить два стола, которые стоят по 3000 ₽:

INSERT INTO goods SET
product = 'стол',
count = 2,
price = 3000;

Но добавлять записи по одному товару за раз — это долго и неэффективно. Проще сразу в одном запросе добавить все нужные товары: стол, стул и табурет:

# подготавливаем множественный запрос
sql = 'INSERT INTO goods (product, count, price) values(?, ?, ?)'
# указываем данные для запроса
data = [
    ('стол', 2, 3000),
    ('стул', 5, 1000),
    ('табурет', 1, 500)
]

# добавляем с помощью множественного запроса все данные сразу
with con:
    con.executemany(sql, data)

# выводим содержимое таблицы на экран
with con:
    data = con.execute("SELECT * FROM goods")
    for row in data:
        print(row)

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

Создаём и заполняем таблицу с товарами

Заведём таблицу clients для клиентов и заполним её точно так же, как мы это сделали с клиентской таблицей. Для этого просто копируем предыдущий код, меняем название таблицы и указываем правильные названия полей.Ещё посмотрите на отличие от обычного SQL в последней строке объявления полей таблицы: вместо id INT AUTO_INCREMENT PRIMARY KEY надо указать id INTEGER PRIMARY KEY. Без этого не будет работать автоувеличение счётчика.

# --- создаём таблицу с клиентами ---
# открываем базу
with con:
    # получаем количество таблиц с нужным нам именем — clients
    data = con.execute("select count(*) from sqlite_master where type='table' and name='clients'")
    for row in data:
        # если таких таблиц нет
        if row[0] == 0:

            # создаём таблицу для клиентов
            with con:
                con.execute("""
                    CREATE TABLE clients (
                        name VARCHAR(40),
                        phone VARCHAR(10) UNIQUE,
                       id INTEGER PRIMARY KEY
                    );
                """)
# подготавливаем множественный запрос
sql = 'INSERT INTO clients (name, phone) values(?, ?)'
# указываем данные для запроса
data = [
    ('Миша', 9208381096),
    ('Наташа', 9307265198),
    ('Саша', 9307281096)
]

# добавляем с помощью множественного запроса все данные сразу
with con:
    con.executemany(sql, data)

# выводим содержимое таблицы с клиентами на экран
with con:
    data = con.execute("SELECT * FROM clients")
    for row in data:
        print(row)

Cоздаём таблицу с покупками и связываем всё вместе

У нас всё готово для того, чтобы на основе первых двух таблиц создать третью — в ней будут данные сразу и о покупках, и о том, кто это купил. Если интересно, как это работает в деталях, — почитайте статью про связи в базе данных.

# --- создаём таблицу с покупками ---
# открываем базу
with con:
    # получаем количество таблиц с нужным нам именем — orders
    data = con.execute("select count(*) from sqlite_master where type='table' and name='orders'")
    for row in data:
        # если таких таблиц нет
        if row[0] == 0:

            # создаём таблицу для покупок
            with con:
                con.execute("""
                    CREATE TABLE orders (
                        order_id INTEGER PRIMARY KEY,
                        product VARCHAR,
                        amount INTEGER,
                        client_id INTEGER,
                        FOREIGN KEY (product) REFERENCES goods(product),
                        FOREIGN KEY (client_id) REFERENCES clients(id)
                    );
                """)

Проверим, что связь работает: добавим в таблицу с заказами запись о том, что Миша купил 2 табурета:

# подготавливаем  запрос
sql = 'INSERT INTO orders (product, amount, client_id) values(?, ?, ?)'
# указываем данные для запроса
data = [
    ('табурет', 2, 1)
]
# добавляем запись в таблицу
with con:
    con.executemany(sql, data)

# выводим содержимое таблицы с покупками на экран
with con:
    data = con.execute("SELECT * FROM orders")
    for row in data:
        print(row)

Компьютер выдал строку (1, ‘табурет’, 2, 1), значит, таблицы связались правильно.

Что дальше

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

Подпишитесь, чтобы не пропустить продолжение про SQLite. А если вам интересна аналитика и работа с данными, приходите на курс «SQL для работы с данными и аналитики».

Все, что вам нужно для начала работы!

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

• Загрузка библиотеки
• Создание и подключение к вашей базе данных
• Создание таблиц базы данных
• Добавление данных
• Запрос данных
• Удаление данных
• И многое другое!

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

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

Что вы создадите

Следуя этому руководству, вы создадите базу данных в SQLite с помощью Python. В частности, этот пост проведет вас через все шаги по созданию базы данных, которая охватывает следующую таблицу, включая все отношения:

Типы данных, доступные в SQLite для Python

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

• NULL — включает значение NULL.
• INTEGER — включает целое число.
• REAL — включает значение с плавающей точкой (десятичное).
• ТЕКСТ. — Включает текст
• BLOB. — Включает в себя большой двоичный объект, который хранится точно как вход

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

Начало работы с SQLite в Python

Начнем с загрузки учебника в библиотеке. Мы можем сделать это с помощью следующей команды:

import sqlite3

Давайте перейдем к созданию нашей базы данных.

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

В этом разделе руководства Python SQLite мы рассмотрим различные способы создания базы данных на Python с помощью SQLite. Для этого мы создадим объект Connection, который будет представлять базу данных. Этот объект создается с помощью функции SQLite connect ().

Давайте сначала создадим файл .db, так как это очень стандартный способ поддержки базы данных SQLite. Мы представим соединение с помощью переменной с именем conn. Мы создадим файл с именем orders.db.

conn = sqlite3.connect('orders.db')

С помощью этой строки кода мы создали новый объект connection, а также новый файл с именем orders.db в каталоге, в котором вы работаете. Если вы хотите указать конкретный каталог, вы можете написать:

conn = sqlite3.connect(r'PATH-TO-YOUR-DIRECTORY/orders.db')

Если файл уже существует, функция connect просто подключится к этому файлу.

Примечание: обратите внимание, что мы включили букву r перед строкой, содержащей этот путь. Это позволяет Python знать, что мы работаем с необработанной строкой, а это означает, что / не будет использоваться для экранирования символов. Вы можете узнать больше о необработанных строках, просмотрев эту ссылку.

Функция connect создает соединение с базой данных SQLite и возвращает объект, представляющий его.

Базы данных в памяти

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

conn = sqlite3.connect(:memory:)

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

Создание объекта курсора

Теперь, когда мы создали объект подключения к базе данных, наша следующая задача — создать объект курсор. Проще говоря, объект курсора позволяет нам выполнять SQL-запросы к базе данных. Мы создадим переменную cur для хранения объекта курсора:

cur = conn.cursor()

Теперь, когда у нас есть объект курсора, мы можем использовать его для выполнения SQL-запросов в следующем стиле:

cur.execute("YOUR-SQL-QUERY-HERE;")

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

Создание наших таблиц в SQLite для Python

На этом этапе руководства Python SQLite давайте создадим нашу первую таблицу с использованием SQLite в Python! Теперь, когда у нас есть объект соединения (conn) и объект курсора (cur), мы можем создать нашу первую таблицу. Следуя схеме базы данных, которую мы показали ранее:

Начнем с таблицы users.

cur.execute("""CREATE TABLE IF NOT EXISTS users(
   userid INT PRIMARY KEY,
   fname TEXT,
   lname TEXT,
   gender TEXT);
""")
conn.commit()

В приведенном выше коде мы делаем несколько вещей:

1. Использование функции execute в объекте курсора для выполнения SQL-запроса
2. Использование SQL для создания таблицы с именем users
3. ЕСЛИ НЕ СУЩЕСТВУЕТ поможет нам при повторном подключении к базе данных. Запрос позволит нам проверить, существует ли таблица, и если это так, ничего не изменится.
4. Мы создаем четыре столбца: ИД пользователя, fname, lname и пол. ИД пользователя назначается первичным ключом.
5. Мы зафиксировали изменения, используя функцию commit для объекта подключения.

Чтобы создать другую нашу таблицу, мы можем следовать аналогичному шаблону и написать следующие команды:

cur.execute("""CREATE TABLE IF NOT EXISTS orders(
   orderid INT PRIMARY KEY,
   date TEXT,
   userid TEXT,
   total TEXT);
""")
conn.commit()

После выполнения этих двух сценариев ваша база данных будет иметь две таблицы. Теперь мы готовы начать добавлять данные!

Добавление данных с помощью SQLite в Python

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

cur.execute("""INSERT INTO users(userid, fname, lname, gender) 
   VALUES('00001', 'Nik', 'Piepenbreier', 'male');""")
conn.commit()

Часто, когда мы работаем в Python, у нас есть переменные, которые содержат для нас значения. Например, у нас может быть кортеж, содержащий информацию о пользователе, которая может выглядеть так:

user = ('00002', 'Lois', 'Lane', 'Female')

Если бы мы хотели загрузить эти данные в нашу базу данных, мы бы использовали другое соглашение:

cur.execute("INSERT INTO users VALUES(?, ?, ?, ?);", user)
conn.commit()

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

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

more_users = [('00003', 'Peter', 'Parker', 'Male'), ('00004', 'Bruce', 'Wayne', 'male')]

В этом случае вместо функции execute нам нужно использовать функцию executemany:

cur.executemany("INSERT INTO users VALUES(?, ?, ?, ?);", more_users)
conn.commit()

Если бы мы использовали здесь функцию execute для объекта курсора, функция предполагала бы, что мы передаем два элемента в таблицу напрямую (два кортежа), а не два набора по четыре элемента в каждом! К счастью, в этом случае функция не сработала бы, но будьте осторожны с тем, какую функцию вы используете!

SQLite и предотвращение инъекционных атак

Между прочим, использование метода (?,?,…), О котором мы говорили выше, также помогает защитить от атак SQL-инъекций. По этой причине рекомендуется использовать этот метод вместо ранее упомянутого. Кроме того, печатать легче, так что это беспроигрышный вариант!

Некоторые сценарии для загрузки дополнительных данных

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

Вы можете загрузить эти данные, используя следующие запросы:

cur.executemany("INSERT INTO users VALUES(?, ?, ?, ?);", customers)
cur.executemany("INSERT INTO orders VALUES(?, ?, ?, ?);", orders)
conn.commit()

Выбор данных в SQLite с помощью Python

Далее в этом руководстве по Python SQLite мы рассмотрим, как выбирать данные с помощью SQLite в Python! Мы будем следовать той же структуре, что и для выполнения запросов выше, но мы также добавим к ней еще один элемент.

Использование fetchone () в SQLite с Python

Начнем с использования функции fetchone (). Мы создаем переменную one_result, чтобы вытащить только результат

cur.execute("SELECT * FROM users;")
one_result = cur.fetchone()
print(one_result)

Это возвращает:

[(1, 'Nik', 'Piepenbreier', 'male')]

Использование fetchmany () в SQLite с Python

Допустим, мы хотим вернуть более одного результата, мы могли бы использовать функцию fetchmany (). Давайте запустим другой скрипт, чтобы получить 3 результата:

cur.execute("SELECT * FROM users;")
three_results = cur.fetchmany(3)
print(three_results)

Это вернет следующее:

[(1, 'Nik', 'Piepenbreier', 'male'), (2, 'Lois', 'Lane', 'Female'), (3, 'Peter', 'Parker', 'Male')]

Использование fetchall () в SQLite с Python

Точно так же мы могли бы использовать функцию fetchall () для возврата всех результатов. Если бы мы выполнили следующее, все результаты были бы возвращены:

cur.execute("SELECT * FROM users;")
all_results = cur.fetchall()
print(all_results)

Удаление данных в SQLite с помощью Python

Теперь мы рассмотрим, как удалять данные с помощью SQLite в Python. Мы можем сделать это, используя структуру, аналогичную описанной выше. Допустим, мы хотим удалить любого пользователя с фамилией «Паркер», мы могли бы написать:

cur.execute("DELETE FROM users WHERE lname='Parker';")
conn.commit()

Когда мы затем запустим запрос ниже:

cur.execute("select * from users where lname='Parker'")
print(cur.fetchall())

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

Объединение таблиц с SQLite в Python

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

Для этого напишем следующее:

cur.execute("""SELECT *, users.fname, users.lname FROM orders
    LEFT JOIN users ON users.userid=orders.userid;""")
print(cur.fetchall())

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

Заключение: Учебное пособие по Python SQLite

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