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

Preparing inventory for further comfortable work

• Python has built-in support for SQLite database, for this you don’t need to install anything further, it’s enough to specify the import of the standard library in the script
  
  

  import sqlite3 

  
  
• Download the test database with which we will work. This article will use the open (MIT license) test database “Chinook”. You can download it at the following links:
  ')
  chinookdatabase.codeplex.com
  github.com/lerocha/chinook-database
  
  We need only 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 deal with the browser, but it will help you to see visually what is happening with the base during our experiments.
  
  Note : when making changes to the base, do not forget to apply them, as the base 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 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) # [] 

Record to base

 #  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',)] 

We split the query into multiple lines in triple quotes

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

Combining database queries into 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!'); """) 

Make the substitution of the value in the query

 # 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: Increase code stability

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

UPD: Using with psycopg2

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

UPD: row_factory usage

 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)

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

I invite you to discuss:

• If I somewhere made an inaccuracy or did not take into account something important - write in the comments, important comments will later be 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 efficient questions with answers will be later added to the article.