Create a text file in python using values from a sqlite database

Question

Hello,

I am trying to build a text file which is a combination of predefined strings and variable values which I would like to take from a pre-existing sqlite database. The general format of each line of the text file is as such:

constraint n: value i < value j

Where n is an integer which will increase by one every line. Value i is the value found at row x, column y of table i and value j is that found at row x, column y of table j. I will need to iterate over each value of these tables presently in my database. My goal is to use nested while loops in order to iterate through each value in the tables, along the lines of:

>>> while x < 30:
         y = 1
         while y < 30:
              #code to populate text file goes here
              n = n + 1
              y = y + 1
         x = x + 1

I have some limited experience in python but am wide open to any suggestions. Any input that anyone has would be very much appreciated.

Thank you very much and Happy New Year!

Paul

Answer 1

The simplest, unoptimized approach would be something like:

import sqlite3
conn = sqlite3.connect('whatever.file')
c = conn.cursor

out = open('results.txt', 'w')
n = 1

for x in range(1, 30):
  for y in range(1, 30):
    c.execute('select value from i where row=? and column=?', (x, y))
    i = c.fetchone()[0]
    c.execute('select value from j where row=? and column=?', (x, y))
    j = c.fetchone()[0]
    out.write('constraint %d: %s < %s\n' % (n, i, j))
    n += 1

out.close()
conn.close()

The main issue with this approach is that it uses 1682 separate queries to sqlite and thereby might be a bit slow. The main optimization would be to reduce the number of queries by fetching (e.g.) all i and j values for each given x with just two queries (by having no where condition on y and select value, column as the select subclause) -- whether you need it or not depends on whether the performance of this dirt-simple approach is already satisfactory for your purposes.

Answer 2

I believe you will be much happier if you use an ORM to access the data, rather than direct SQL as shown in Alex Martelli's answer. It seems like your database is very simple and an ORM will work well.

I suggest you try SQLAlchemy or Autumn. SQLAlchemy is powerful and flexible, and has been around for a long time. Autumn is very simple. Either one should help you.

http://www.sqlalchemy.org/

http://autumn-orm.org/

On the other hand, if Alex Martelli's example code has already solved your problem and this is a simple one-off problem, then hey, grab the solution and go.