SQLite3 Python

Введение

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

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

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

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

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

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

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

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

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

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

import csv, sqlite3 con = sqlite3.connect('db/my.db') cur = con.cursor() cur.execute("CREATE TABLE t (picture, price, number);") # picture, price, number - это названия столбцов with open('data.csv','r') as fin: # csv.DictReader по умолчанию использует первую строку под заголовки столбцов dr = csv.DictReader(fin, delimiter=";") to_db = [(i['picture'], i['price'], i['number']) for i in dr] cur.executemany("INSERT INTO t (picture, price, number) VALUES (?, ?, ?);", to_db) con.commit() con.close()

python3 from_csv.py

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

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

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

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

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

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

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

name;url;area URN.SU;https://eth1.ru;IT HeiHei.ru;https://heihei.ru;Travel TopBicycle.ru;https://topbicycle.ru;Bicycles AtlasVPN;atlasvpn.com;VPN TurboVPN;turbovpn.com;VPN Авиасейлз;https://aviasales.ru;Travel Booking.com;https://booking.com;Hotels Hotellook;https://Hotellook.com;Hotels Велодрайв;https://velodrive.ru;Bicycles Xiaomi;https://mi-shop.com;Android Samsung;https://www.samsungstore.ru;Android Book24;https://Book24.ru;Books GeekBrains;https://gb.ru;Education Нетология;https://netology.ru;Education SkillBox;https://SkillBox.ru;Education Pluralsight;https://Pluralsight.com;Education СовКомСтрахование;https://sovcomins.ru;Insurance Полис 812;https://polis812.ru;Insurance Vivo;https://ru.vivo.com/;Android Beget;https://beget.com;Hosting Reg.ru;https://Reg.ru.ru;Hosting OLDI;https://oldi.ru;Laptops

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

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

Также не забываем сгенерировать id

Новый, более близкий к реальной жизни, файл friends.py будет выглядеть так:

import csv, sqlite3 con = sqlite3.connect('db/partners.db') cur = con.cursor() cur.execute("""CREATE TABLE friends ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, url TEXT, area TEXT )""") with open('partners.csv','r', encoding="utf8") as f: dr = csv.DictReader(f, delimiter=";") to_db = [(i['name'], i['url'], i['area']) for i in dr] cur.executemany("INSERT INTO friends (name, url, area) VALUES (?, ?, ?);", to_db) con.commit() con.close()

python3 friends.py

Список всех таблиц и их столбцов

Список всех таблиц БД

import sqlite3 def print_tables(db) -> None: try: con = sqlite3.connect(db) cur = con.cursor() cur.execute("SELECT name FROM sqlite_master WHERE type='table';") print(cur.fetchall()) except Exception as e: print(f"print_tables() " f"Connection to {db} failed:\n{e}") if __name__ == "__main__": print_tables("my.db")

[('table_name8',), ('sqlite_sequence',)]

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

# Importing Sqlite3 Module import sqlite3 try: # Making a connection between sqlite3 # database and Python Program sqliteConnection = sqlite3.connect('testers.db') # If sqlite3 makes a connection with python # program then it will print "Connected to SQLite" # Otherwise it will show errors print("Connected to testers.db") # Getting all tables from sqlite_master sql_query = """SELECT name FROM sqlite_master WHERE type='table';""" # Creating cursor object using connection object cursor = sqliteConnection.cursor() # executing our sql query cursor.execute(sql_query) print("List of tables\n") tables = cursor.fetchall() # printing all tables list # print(tables) # [('all_testers',), ('tools',)] for table in tables: # print(table) table_name = table[0] print(f"\nTable: {table_name}\n") sql_query = f"SELECT * from {table_name}" cursor = sqliteConnection.execute(sql_query) names = list(map(lambda x: x[0], cursor.description)) for name in names: print(f"column: {name}") except sqlite3.Error as error: print("Failed to execute the above query", error) finally: # Inside Finally Block, If connection is # open, we need to close it if sqliteConnection: # using close() method, we will close # the connection sqliteConnection.close() # After closing connection object, we # will print "the sqlite connection is # closed" print("the sqlite connection is closed")

Если в базе данных testers.db есть две таблицы all_testers и tools результат будет примерно такой

Connected to testers.db List of tables Table: all_testers column: tester_id column: name column: tool Table: tools column: tool_id column: name column: users column: rating the sqlite connection is closed