Creating a sorted sqlite-database with python

Question

I am trying to create a datebase named "Counts" which keeps track of how many times a spesific email address has sent an email. The data is parsed from a textfile and the database is created using python.

My code looks like this:

 import sqlite3
 conn = sqlite3.connect('emaildb.sqlite')
 cur = conn.cursor()
 cur.execute('DROP TABLE IF EXISTS Counts')
 cur.execute('''CREATE TABLE Counts (email TEXT, count INTEGER)''')
 fname = input('Enter file name: ')
 fh = open(fname)
 for line in fh:
    if not line.startswith("From"): continue
    pieces = line.split()
    email = pieces[1]
    cur.execute('SELECT count FROM Counts WHERE email= ? ', (email,))
    row = cur.fetchone()
    if row is None:
        cur.execute('''INSERT INTO Counts (email, count)
            VALUES (?, 1)''', (email,))
    else:
        cur.execute('UPDATE Counts SET count = count + 1 WHERE email= ?',(email,))
cur.execute('SELECT * FROM Counts ORDER BY count DESC')
conn.commit()

The code produces perfectly working database, but it is not ordered. Using the above mentioned

`"SELECT * FROM Counts ORDER BY count DESC"`

command I can sort the database in the sqlitebrowser. Somehow that doesn't work when integrated in the python code even though I use commit after ordering command.

Thanks in advance.

John Stanesa · Accepted Answer · 2018-04-25 18:05:09Z

3

The command cur.execute('SELECT * FROM Counts ORDER BY count DESC') does not reorder the database itself, it just orders the results of the select statement.

More fundamentally, a SELECT statement does not modify anything. It just returns a list of the rows you ask for.

If you want to change the order of the rows in the database, you need to use UPDATE or INSERT commands to do so, but in your case, you probably don't actually want to do this. It is better to just order the results every time using ORDER BY.

Because of how sqlite works, it is usually not a good idea to rely on the order the results are stored in the database. More information about how rows are ordered in sqlite can be found in the docs: https://www.sqlite.org/rowidtable.html

sqlite tables use 'rowid' for order by default.

answered Apr 25, 2018 at 18:05

John Stanesa

1287 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Creating a sorted sqlite-database with python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related