Создание и доступ к базе данных SQLite с помощью Python

Задачи

После этой прочтения статьи вы сможете:

• Создавать базу данных
• Создавать таблицу
• Вставлять данные в таблицу
• Запрашивать данные из таблицы
• Получать набор результатов в датафрейм pandas
• Закрыть соединение с базой данных

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

Задача 1: Создать базу данных с помощью

SQLite

#Install & load sqlite3
#!pip install sqlite3  ##Uncomment the code to install sqlite3
import sqlite3

# Connecting to sqlite
# connection object
conn = sqlite3.connect('INSTRUCTOR.db')

Класс Cursor – это экземпляр, с помощью которого можно вызывать методы, выполняющие операторы SQLite, извлекающие данные из наборов результатов запросов. Создать объект Cursor можно с помощью метода cursor() объекта/класса Connection.

# cursor object
cursor_obj = conn.cursor()

Задача 2: Создать таблицу в базе данных

На этом шаге мы создадим в базе данных таблицу со следующими данными:

Прежде чем создавать таблицу, необходимо выяснить, существует ли она уже или нет. Для удаления таблицы из базы данных используется запрос DROP. Cursor – это объект, который помогает выполнить запрос и получить записи из базы данных.

Не волнуйтесь, если вы получите эту ошибку:

# создание таблицы
table = """ create table IF NOT EXISTS INSTRUCTOR(ID INTEGER PRIMARY KEY NOT NULL, FNAME VARCHAR(20), LNAME VARCHAR(20), CITY VARCHAR(20), CCODE CHAR(2));"""
 
cursor_obj.execute(table)
 
print("Table is Ready")

Задача 3: Вставить данные в таблицу

На этом шаге мы вставим в таблицу несколько строк данных.

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

Начнем со вставки только первой строки данных, т.е. для преподавателя Rav Ahuja

cursor_obj.execute('''insert into INSTRUCTOR values (1, 'Rav', 'Ahuja', 'TORONTO', 'CA')''')

На выходе вы получите : sqlite3.Cursor at 0x27a1a491260, что означает, что в базе данных mySql есть объект sqlite3.Cursor по адресу 0x27a1a49126, выводимый в таблицу.

Теперь с помощью одного запроса вставьте оставшиеся две строки данных

cursor_obj.execute('''insert into INSTRUCTOR values (2, 'Raul', 'Chong', 'Markham', 'CA'), (3, 'Hima', 'Vasudevan', 'Chicago', 'US')''')

Задача 4: Запросить данные в таблице

На этом шаге мы получим данные, которые мы вставили в таблицу INSTRUCTOR.

statement = '''SELECT * FROM INSTRUCTOR'''
cursor_obj.execute(statement)

print("All the data")
output_all = cursor_obj.fetchall()
for row_all in output_all:
  print(row_all)

Получение нескольких строк из таблицы

statement = '''SELECT * FROM INSTRUCTOR'''
cursor_obj.execute(statement)
  
print("All the data")

Если необходимо получить несколько строк из таблицы, то используем fetchmany(numberofrows) и указываем число, сколько строк необходимо получить.

output_many = cursor_obj.fetchmany(2) 
for row_many in output_many:
  print(row_many)

Извлечь из таблицы только FNAME

statement = '''SELECT FNAME FROM INSTRUCTOR'''
cursor_obj.execute(statement)
  
print("All the data")
output_column = cursor_obj.fetchall()
for fetch in output_column:
  print(fetch)

Бонус: теперь напишите и выполните оператор обновления, который изменит значение CITY в Rav на MOOSETOWN

query_update='''update INSTRUCTOR set CITY='MOOSETOWN' where FNAME="Rav"'''
cursor_obj.execute(query_update)

statement = '''SELECT * FROM INSTRUCTOR'''
cursor_obj.execute(statement)
  
print("All the data")
output1 = cursor_obj.fetchmany(2)
for row in output1:
  print(row)

Задача 5: Получение данных в Pandas

На этом шаге мы извлечем содержимое таблицы INSTRUCTOR в датафрейм Pandas

import pandas as pd
#retrieve the query results into a pandas dataframe
df = pd.read_sql_query("select * from instructor;", conn)

#print the dataframe
df

#print just the LNAME for first row in the pandas data frame
df.LNAME[0]

После того как данные помещены во фрейм данных Pandas, с ними можно выполнять типичные для pandas операции.

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

df.shape

Задача 6: Закрыть соединение

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

# Close the connection
conn.close()

Резюме

В этом уроке вы создали базу данных и таблицу в блокноте Python, используя SQLite3. Затем создали таблицу и вставили в нее несколько строк данных. Затем выполнили запрос к этим данным. Вы также извлекли данные в кадр данных pandas.