python + sqlite: How to use Order by for column with special letters?
<p>I want to use <code>order by</code> to sort one of the column. This column, lets say its name is ColA, contains many <strong>special letters</strong>. Here is an example what ColA looks like:</p>

<pre><code>ColA
Abc
D6
(8)
-s
'st
9-57
6s
A&amp;C
5
Ãber
ÑÑÌÑÑÐºÐ¸Ð¹
W/WO
</code></pre>

<p>I tried with the following codes. </p>

<pre><code>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()
</code></pre>

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

<p>But it gives me no change to the database file.</p>

<p>Well, I adimit ColB and ColC contain also such kind of special letters. </p>

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

<p>A SELECT statement does not modify the database.</p>

<p>To reorder the table, you must sort the rows when you actually write the new table:</p>

<pre><code>INSERT INTO MyTable (ColA, ColB, ColC)
SELECT               ColA, ColB, ColC
FROM TempOldTable
ORDER BY ColA;
</code></pre>

python sqlite