Python SQLITE3 Inserting Backwards
<p>I have a small piece of code which inserts some data into a database. However, the data is being inserting in a reverse order.<br>
If i "commit" after the for loop has run through, it inserts backwards, if i "commit" as part of the for loop, it inserts in the correct order, however it is much slower.<br>
How can i commit after the for loop but still retain the correct order?  </p>

<pre><code>import subprocess, sqlite3
output4 = subprocess.Popen(['laZagne.exe', 'all'], stdout=subprocess.PIPE).communicate()[0]
lines4 = output4.splitlines()

conn = sqlite3.connect('DBNAME')
cur = conn.cursor()


for j in lines4:
    print j
    cur.execute('insert into Passwords (PassString) VALUES (?)',(j,))
conn.commit()
conn.close()
</code></pre>

<p>You can't rely on <em>any</em> ordering in SQL database tables. Insertion takes place in an implementation-dependent manner, and where rows end up depends entirely on the storage implementation used and the data that is already there.</p>

<p>As such, no reversing takes place; if you are selecting data from the table again and these rows come back in a reverse order, then that's a coincidence and not a choice the database made.</p>

<p>If rows must come back in a specific order, use <code>ORDER BY</code> when selecting. You could order by <code>ROWID</code> for example, which <em>may</em> be increasing monotonically for new rows and thus give you an approximation for insertion order. See <a href="http://sqlite.org/lang_createtable.html#rowid" rel="nofollow"><em>ROWIDs and the INTEGER PRIMARY KEY</em></a>.</p>

python sqlite3