Python preliminaries

%pwd

'/Users/gjanee-local/work/meds213/bren-meds213-class-data/week7'

%ls

Untitled.ipynb  database.db

There are lots of these % Jupyter “magic” commands, a few are Bash equivalents.

Mega quotes

Allow you to create a string that spans lines, useful for long queries

"this is a string"

'this is also a string'

"""
SELECT * FROM table
WHERE whatever
sdfsd
sdf"""

'\nSELECT * FROM table\nWHERE whatever\nsdfsd\nsdf'

String interpolation

"Hello, %s!" % "Greg"

'Hello, Greg!'

"%s, %s says hi" % ("Renata", "Greg")

'Renata, Greg says hi'

Tuples

Tuples are like lists, but are immutable

[2, 3, 5] # list with 3 elements
[2, 3]    # list with 2 elements
[2]       # list with 1 element

[2]

(2, 3, 5) # tuple with 3 elements
(2, 3)    # tuple with 2 elements
(2,)      # tuple with 1 element

(2,)

1-tuple has weird syntax to avoid interpretation as parenthesized expression

(2) # parenthesized expression

my_list = [2, 3, 5]
my_list[0]

my_tuple = (2, 3, 5)
my_tuple[0]

my_list[2] = 47
my_list

[2, 3, 47]

my_tuple[2] = 47

TypeError: 'tuple' object does not support item assignment

List comprehensions

Much more concise than writing a loop

my_list = [2, 3, 5, 7]
list_squares = []
for n in my_list:
    list_squares.append(n*n)
list_squares

[4, 9, 25, 49]

[n*n for n in my_list]

[4, 9, 25, 49]

query_result = [(2,), (3,), (5,), (7,)]
[t[0] for t in query_result]

[2, 3, 5, 7]

Database programming

Each database has its own module, or “driver”. Here we use the sqlite3 module. But all drivers provide the same DB-API interface.

Most RDBMSs: specify network location of database. SQLite: specify filename. If file doesn’t exist, a new database is created.

import sqlite3
conn = sqlite3.connect("database.db")

conn

<sqlite3.Connection at 0x111d9fd40>

Got a connection, now need a cursor. Can use same cursor for multiple sequential queries. Can have multiple cursors open at once.

c = conn.cursor()
c

<sqlite3.Cursor at 0x112680d40>

c.execute("""
    SELECT Nest_ID, floatAge, ageMethod
    FROM Bird_nests
    LIMIT 10""")

<sqlite3.Cursor at 0x112680d40>

Get all results. Get back list of tuples. Tuple element order corresponds to columns. Notice database types carry over to Python. NULL maps to None.

c.fetchall()

[('14HPE1', None, None),
 ('11eaba', None, None),
 ('11eabaagc01', 6.0, 'float'),
 ('11eabaagv01', 3.0, 'float'),
 ('11eababbc02', 4.0, 'float'),
 ('11eababsv01', 2.0, 'float'),
 ('11eabaduh01', 2.0, 'float'),
 ('11eabaduv01', 5.0, 'float'),
 ('11eabarpc01', 4.0, 'float'),
 ('11eabarpc02', 4.0, 'float')]

The cursor doesn’t hold on to results, it’s just a means of retrieving them one time.

c.fetchall()

[]

If just one column, still get tuples back.

c.execute("SELECT Nest_ID FROM Bird_nests LIMIT 10")
c.fetchall()

[('04coatapg01',),
 ('04coatapg02',),
 ('04coatapj01',),
 ('04coatapj02',),
 ('04coatapp01',),
 ('04coatapp02',),
 ('04coatapp03',),
 ('04coatbbg01',),
 ('04coatbbg02',),
 ('04coatbbg03',)]

c.execute("SELECT Nest_ID FROM Bird_nests LIMIT 10")
[t[0] for t in c.fetchall()]

['04coatapg01',
 '04coatapg02',
 '04coatapj01',
 '04coatapj02',
 '04coatapp01',
 '04coatapp02',
 '04coatapp03',
 '04coatbbg01',
 '04coatbbg02',
 '04coatbbg03']

c.execute("SELECT COUNT(*) FROM Bird_nests")
c.fetchall()

[(1547,)]

If only one row coming back, convenient to use fetchone()

c.execute("SELECT COUNT(*) FROM Bird_nests")
c.fetchone()

(1547,)

BTW, if you want to get help on things in Python, ask for help on a variable, will get back help on that variable’s type.

help(c)

Can use cursor as iterator. Technique for large returns, more efficient than fetchall().

c.execute("SELECT Nest_ID FROM Bird_nests LIMIT 10")
for row in c:
    print("got nest ID %s" % row[0])

got nest ID 04coatapg01
got nest ID 04coatapg02
got nest ID 04coatapj01
got nest ID 04coatapj02
got nest ID 04coatapp01
got nest ID 04coatapp02
got nest ID 04coatapp03
got nest ID 04coatbbg01
got nest ID 04coatbbg02
got nest ID 04coatbbg03

Same, using new-style interpolation using f"..." strings.

c.execute("SELECT Nest_ID FROM Bird_nests LIMIT 10")
for row in c:
    print(f"got nest ID {row[0]}")

got nest ID 04coatapg01
got nest ID 04coatapg02
got nest ID 04coatapj01
got nest ID 04coatapj02
got nest ID 04coatapp01
got nest ID 04coatapp02
got nest ID 04coatapp03
got nest ID 04coatbbg01
got nest ID 04coatbbg02
got nest ID 04coatbbg03

Do things other than querying

c.execute("""
    CREATE TEMP TABLE mytemp AS
    SELECT * FROM Bird_nests LIMIT 10
""")

<sqlite3.Cursor at 0x112680d40>

No return in this case. Note that temp tables are local to the session, not globally visible.

c.fetchall()

[]

c.execute("SELECT * FROM mytemp")
c.fetchall()

c.execute("""
    CREATE TABLE mytemp AS
    SELECT * FROM Bird_nests LIMIT 10
""")

<sqlite3.Cursor at 0x112680d40>

This table is now visible.