A few days ago, I was assigned to a new task that I need to use Python to get data from database with SQlite and parse them to BibTeX format. None of those is what I familiar with. I'm a frontend developer with one year experience. It has been about two years since I learned Python for 3 days at a bootcamp. So basically, I don't remember anything about Python.

Start

I didn't like the task and I just wanted to finish it as quickly as possible. So I started right away googling, I searched python sqlite and found the official document which is pretty straightforward with examples and explanations. That's a good start!

Try it out

First thing is how to connect to the database.

import sqlite3
con = sqlite3.connect('example.db')

The code itself is easy to understand, but I tried to connect it with my JavaScript knowledge. Maybe it can help me establish a mental model in Python and feel less stressful when I saw different syntax. I noticed that importing the sqlite module is similar to import a module in node.js. And I don't need a keyword like const or let to declare a variable.

Next, I saw how to perform SQL commands.

cur = con.cursor()

# Create table
cur.execute('''CREATE TABLE stocks
               (date text, trans text, symbol text, qty real, price real)''')

# Insert a row of data
cur.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")

# Save (commit) the changes
con.commit()

# We can also close the connection if we are done with it.
# Just be sure any changes have been committed or they will be lost.
con.close()

Make it work

I needed to make a dynamic query, so I tried to google something like python sqlite variable, python sqlite query parameter, etc. I found two ways to do this. One is using ? as placeholder in the query string and pass values as second argument to cur.execute(). Even there is only one value, it still needs a trailing comma like (qty,). It's because that's a special type called tuple, which I don't know anything about it. I prefer the second way which is also more clearer. We write : + name as placeholder in query string, then pass the value as object key value pair.

qty = 100
cur.execute('SELECT * FROM stocks where qty = (?)', (qty,))
cur.execute('SELECT * FROM stocks where qty = :qty', { "qty": qty })

One thing I noticed is that we can't use JavaScript object shorthand here like

const qty = 100
o1 = { "qty": qty }
o2 = { qty }

After a little bit research, it's because { qty } is creating set in Python.

Next, I wanted to know what the data look like. There is a great example that shows me how to do it.

>>> for row in cur.execute('SELECT * FROM stocks ORDER BY price'):
        print(row)

('2006-01-05', 'BUY', 'RHAT', 100, 35.14)
('2006-03-28', 'BUY', 'IBM', 1000, 45.0)
('2006-04-06', 'SELL', 'IBM', 500, 53.0)
('2006-04-05', 'BUY', 'MSFT', 1000, 72.0)

I noticed how to made a for loop in Python, and I also noticed that the data of each row doesn't have column names!

How can we handle data without even knowing their respective column names? So I googled python sqlite column name. I chose the answer on stackoverflow that links to a document.

We need a row_factory property to be configured on the connection object before getting the data. Then we can call .keys() on row to get the column names.

import sqlite3
connection = sqlite3.connect('example.db')
connection.row_factory = sqlite3.Row
cur = connection.cursor()
cur.execute('select * from stocks')
row = cur.fetchone()
columnNames = row.keys()
# ['date', 'trans', 'symbol', 'qty', 'price']

Now I can made an object of columnName: rowValue.

obj = {}
  for name in columnNames: 
    obj[name] = row[name]

Make it better

That's great! I got it work, but I didn't feel right about this. I don't want to make nested for loop like this, one for rows and one for columns. There gotta be an easier way. so I went on googling about it and I got so much extra information along the way. I found that [] is called list in Python and {} is called dictionary. lambda is something similar to JavaScript arrow function. Accessing property of an object in JavaScript like obj.propertyName doesn't work on Python dictionary, it has to be accessed with brackets [] like obj["propertyName"].

Luckily, I googled with the new keyword I just learned python sqlite dictionary and I found a great tutorial on this topic. It shows how to use dictionary comprehension and list comprehension to handle the data.

Dictionary comprehension looks pretty weird to me, so I try to understand this with my JavaScript mental model.

original = {k: row[k] for k in row.keys()}

I already know that for in can loop through things. I am making a dictionary, so that's what outer {} for. Then each key value pair would be k: row[k] which is columnName: rowValue.

Next, the tutorial shows how to make a list of dictionaries.

list_accumulator = []
  for row in rows:
      list_accumulator.append({k: row[k] for k in row.keys()})

And an example of list comprehension. Again, this is pretty new and weird syntax to me. But it is easier to understand this time, I already knew what {k: row[k] for k in row.keys()} does and how it works.

list_comp_version = [{k: row[k] for k in row.keys()} for row in rows]

[ ... for row in rows] is making a list of items, each item will be ... which is the dictionary we just saw.

It seems that there is no build-in function that can handle this for us. I think that's pretty much what I need for getting data from the database. now I can move on to the next step.

Next step

I was instructed to get the data, parse them, store them to a .bib file. Parsing them is the hardest part, so storing data into a file was my next step.

I googled python write file. Again, there are new syntax, so I googled them each to learn more about them. with as can automatically close the file for us. Passing arguments like the third argument encoding='utf-8' to open() is called keyword arguments. file.write() to write content to the file.

content = ''
with open('test.txt', 'w', encoding='utf-8') as f:
  f.write(content)

I made an stupid mistake yesterday that I forgot to include encoding='utf-8' to one of my open() file tests. The error shows it's a encoding problem, I asked my supervisor for help and I kept trying to fix that error on the "correct" one of open() file tests. In the end, he tested writing the symbol © which I had problem with directly to a file. I tested that as well on my end and successfully wrote the symbol into a file, but the error is still there. That's the moment when I realized I looked into the wrong place.

Now I know how to get data and write them into a file, my next step will be focusing on how to parse and format the data.

Wrap up

Although I don't like this task, it is a great opportunity to test my skill. As a developer, we often need to solve problems that we are not familiar with. Sometimes we can still use our experience to break down the problems. The best part of this task is that I found another interesting tutorial from the tutorial I linked above. It has the content that I'm interested in. It teaches in a way I was looking for. And most importantly, it is something that I can read when I rest between sets in gym.🤣