Python: Working with a Database, Part 1/2: Using the DB-API

We prepare inventory for further comfortable work

• Python has built-in support for SQLite database, for this you do not need to install anything extra, just specify the import of the standard library in the script
  
  

  import sqlite3

  
  
• Download the test database, which we will work with. This article will use the “Chinook” open test database (MIT license). You can download it at the following links:
  
  chinookdatabase.codeplex.com
  github.com/lerocha/chinook-database
  
  We only need the binary file “Chinook_Sqlite.sqlite” to work.
  
  
• For the convenience of working with the database (viewing, editing), we need a database browser program that supports SQLite. The article does not discuss working with the browser, but it will help you visually see what is happening with the database during our experiments.
  
  Note : when making changes to the database, do not forget to apply them, since the database with unapplied changes remains locked.
  
  You can use (the last two options are cross-platform and free):
  
  
  • The familiar utility for working with the database as part of your IDE;
  • SQLite Database Browser
  • SQLiteStudio

Python DB-API modules depending on the database

Connection to the base, getting the cursor

# Импортируем библиотеку, соответствующую типу нашей базы данных 
import sqlite3
# Создаем соединение с нашей базой данных
# В нашем примере у нас это просто файл базы
conn = sqlite3.connect('Chinook_Sqlite.sqlite')
# Создаем курсор - это специальный объект который делает запросы и получает их результаты
cursor = conn.cursor()
# ТУТ БУДЕТ НАШ КОД РАБОТЫ С БАЗОЙ ДАННЫХ
# КОД ДАЛЬНЕЙШИХ ПРИМЕРОВ ВСТАВЛЯТЬ В ЭТО МЕСТО
# Не забываем закрыть соединение с базой данных
conn.close()

conn = psycopg2.connect( host=hostname, user=username, password=password, dbname=database)

Reading from the base

# Делаем SELECT запрос к базе данных, используя обычный SQL-синтаксис
cursor.execute("SELECT Name FROM Artist ORDER BY Name LIMIT 3")
# Получаем результат сделанного запроса
results = cursor.fetchall()
results2 =  cursor.fetchall()
print(results)   # [('A Cor Do Som',), ('Aaron Copland & London Symphony Orchestra',), ('Aaron Goldberg',)]
print(results2)  # []

Write to the database

# Делаем INSERT запрос к базе данных, используя обычный SQL-синтаксис
cursor.execute("insert into Artist values (Null, 'A Aagrh!') ")
# Если мы не просто читаем, но и вносим изменения в базу данных - необходимо сохранить транзакцию
conn.commit()
# Проверяем результат
cursor.execute("SELECT Name FROM Artist ORDER BY Name LIMIT 3")
results = cursor.fetchall()
print(results)  # [('A Aagrh!',), ('A Cor Do Som',), ('Aaron Copland & London Symphony Orchestra',)]

Break the query into multiple lines in triple quotation marks

cursor.execute("""
  SELECT name
  FROM Artist
  ORDER BY Name LIMIT 3
""")

Combining database queries in one method call

cursor.execute("""
	insert into Artist values (Null, 'A Aagrh!');
	insert into Artist values (Null, 'A Aagrh-2!');
""")
# sqlite3.Warning: You can only execute one statement at a time.

cursor.execute("""insert into Artist values (Null, 'A Aagrh!');""")
cursor.execute("""insert into Artist values (Null, 'A Aagrh-2!');""")

cursor.executescript("""
 insert into Artist values (Null, 'A Aagrh!');
 insert into Artist values (Null, 'A Aagrh-2!');
""")

We do a value substitution in the request

# C подставновкой по порядку на места знаков вопросов:
cursor.execute("SELECT Name FROM Artist ORDER BY Name LIMIT ?", ('2'))
# И с использованием именнованных замен:
cursor.execute("SELECT Name from Artist ORDER BY Name LIMIT :limit", {"limit": 3})

We do multiple insertion of rows passing through the collection using the cursor method. executemany ()

# Обратите внимание, даже передавая одно значение - его нужно передавать кортежем!
# Именно по этому тут используется запятая в скобках!
new_artists = [
    ('A Aagrh!',),
    ('A Aagrh!-2',),
    ('A Aagrh!-3',),
]
cursor.executemany("insert into Artist values (Null, ?);", new_artists)

We get the results one by one using the cursor method. fetchone ()

cursor.execute("SELECT Name FROM Artist ORDER BY Name LIMIT 3")
print(cursor.fetchone())    # ('A Cor Do Som',)
print(cursor.fetchone())    # ('Aaron Copland & London Symphony Orchestra',)
print(cursor.fetchone())    # ('Aaron Goldberg',)
print(cursor.fetchone())    # None

Cursor as an iterator

# Использование курсора как итератора
for row in cursor.execute('SELECT Name from Artist ORDER BY Name LIMIT 3'):
        print(row)
# ('A Cor Do Som',)
# ('Aaron Copland & London Symphony Orchestra',)
# ('Aaron Goldberg',)

UPD: Improving Code Stability

try:
    cursor.execute(sql_statement)
    result = cursor.fetchall()
except sqlite3.DatabaseError as err:       
    print("Error: ", err)
else:
    conn.commit()

UPD: Using with in psycopg2

with psycopg2.connect("dbname='habr'") as conn:
    with conn.cursor() as cur:

UPD: Using row_factory

import sqlite3
def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d
con = sqlite3.connect(":memory:")
con.row_factory = dict_factory
cur = con.cursor()
cur.execute("select 1 as a")
print(cur.fetchone()["a"])

Additional materials (in English)

• A short free online course - Udacity - Intro to Relational Databases - Explains the syntax and principles of SQL, Python DB-API - and theory and practice in one package. Highly recommend for beginners!
  
  
• Advanced SQLite Usage in Python
  
  
• SQLite Python Tutorial at tutorialspoint.com
  
  
• A thorough guide to SQLite database operations in Python
  
  
• UPD: The Novice's Guide to the Python 3 DB-API
  
• SQLite Online Guides:
  
  
  • www.tutorialspoint.com/sql/index.htm
  • www.tutorialspoint.com/sqlite
  • www.sqlitetutorial.net

I invite you to a discussion:

• If I made a mistake somewhere or didn’t take into account something important - write in the comments, important comments will be later added to the article indicating your authorship.
• If some points are not clear and clarification is required - write your questions in the comments - or I or other readers will give an answer, and practical questions with answers will be later added to the article.