doglas doglas - 2 months ago 7
Python Question

python + sqlite: How to use Order by for column with special letters?

I want to use

order by
to sort one of the column. This column, lets say its name is ColA, contains many special letters. Here is an example what ColA looks like:

ColA
Abc
D6
(8)
-s
'st
9-57
6s
A&C
5
Über
ру́сский
W/WO


I tried with the following codes.

import sqlite3

# Connect
WorkingFile = "C:\\test.db"
con = sqlite3.connect(WorkingFile)
cur = con.cursor()

# Sort data by ColA
cur.execute("SELECT * FROM MyTable ORDER BY ColA ASC;")
print "sorted"
con.commit()

#rename the table:
cur.execute("ALTER TABLE MyTable RENAME TO TempOldTable;")

#Then create the new table with the missing column:
cur.execute('''CREATE TABLE MyTable
(WorkingID INTEGER PRIMARY KEY AUTOINCREMENT,
ColA TEXT,
ColB TEXT,
ColC TEXT);
''')

#And populate it with the old data:
cur.execute('''INSERT INTO MyTable (ColA, ColB, ColC)
SELECT ColA, ColB, ColC
FROM TempOldTable;''')

#Then delete the old table:
cur.execute("DROP TABLE TempOldTable;")


# Finish
con.commit()
cur.close()


Here I was tring to make a new table, to define ColA to
TEXT
datatype, and copy the sorted data into it.

But it gives me no change to the database file.

Well, I adimit ColB and ColC contain also such kind of special letters.

But I think the programming idea is not wrong. So what am I doing wrong? Please help me out. Thanks.

CL. CL.
Answer

A SELECT statement does not modify the database.

To reorder the table, you must sort the rows when you actually write the new table:

INSERT INTO MyTable (ColA, ColB, ColC)
SELECT               ColA, ColB, ColC
FROM TempOldTable
ORDER BY ColA;