sort names in alphabitical order in mysql using python

Question

I have a list of names in Mysql Database and i want to sort them according to the first alphabet.

When i run this code i get the sentence in the Quotes printed out

import sqlite3
import csv
conn = sqlite3.connect('RefinedDatabase.db')
cursor = conn.cursor()
selectRow = cursor.execute( "SELECT Lecturer FROM staffDataBase")
fetchrow = cursor.fetchone()
while fetchrow != None:
    fetchrow =  str(fetchrow).replace(',)','').replace('(','').replace('u\'','').replace("'","").replace('u"', '').replace('"','')
    fetchrow = 'SELECT lecturer  FROM staffDataBase ORDER BY lecturer'
    print fetchrow
    fetchrow = cursor.fetchone()
conn.close() 
print ("All Done!!")

_sentence in the quotes printed out_ . Are you only getting `All Done!` ? — letsc, Apr 21 '15 at 17:20
No im getting ' SELECT lecturer FROM staffDataBAse Order By lecturer' printed 50 times as i have 50 names to sort. — Hima Bhamidipati, Apr 21 '15 at 17:22
Because you are printing your `fetchrow` which is nothing but a string. That print statement is executed for every matching record in your database. — letsc, Apr 21 '15 at 17:23
If i change the code to: while fetchrow != None: fetchrow = str(fetchrow).replace(',)','').replace('(','').replace('u\'','').replace("'","").replace('u"', '').replace('"','') fetchrow = cursor.execute("SELECT lecturer FROM staffDataBase ORDER BY lecturer") print fetchrow fetchrow = cursor.fetchone() ... This just prints the ongoing memory address as : — Hima Bhamidipati, Apr 21 '15 at 17:27
Why are you modifying your query with `.replace`? Did you look at the [docs](https://docs.python.org/2/library/sqlite3.html) ? — letsc, Apr 21 '15 at 17:30
when i extract information from MySQL it prints out with extra parenthesis around each field. i want to replace all of them with nothing. — Hima Bhamidipati, Apr 21 '15 at 17:34
Could you paste that here? I think I know what your problem is — letsc, Apr 21 '15 at 17:34
It prints out something like (u'2006-01-05', u'BUY', u'RHAT', 100, 35.14) (u'2006-03-28', u'BUY', u'IBM', 1000, 45.0) (u'2006-04-06', u'SELL', u'IBM', 500, 53.0) (u'2006-04-05', u'BUY', u'MSFT', 1000, 72.0) .... from the docs you attached .... I cant post any pictures caz im new to this and the browser wont let me:/ — Hima Bhamidipati, Apr 21 '15 at 17:43

score 1 · Accepted Answer · edited May 23 '17 at 12:14

1

Following the docs

for row in c.execute('SELECT * FROM stocks ORDER BY price'):
        print row

will give you

(u'2006-01-05', u'BUY', u'RHAT', 100, 35.14)
(u'2006-03-28', u'BUY', u'IBM', 1000, 45.0)
(u'2006-04-06', u'SELL', u'IBM', 500, 53.0)
(u'2006-04-05', u'BUY', u'MSFT', 1000, 72.0)

Here row is nothing but a tuple and python allows you to unpack a tuple.

You can do this:

numbers = []
for row in c.execute('SELECT * FROM stocks ORDER BY price'):
            date, process,company,number,rate = row
            numbers.append(number)

numbers.sort()
print numbers

Output:

[100, 500, 1000, 1000]

If you want to update the table with the sorted tuples you can do this instead:

from operator import itemgetter

tup = ((u'2006-01-05', u'BUY', u'RHAT', 100, 35.14),(u'2006-03-28', u'BUY', u'IBM', 1000, 45.0), (u'2006-04-06', u'SELL', u'IBM', 500, 53.0), (u'2006-04-05', u'BUY', u'MSFT', 1000, 72.0))
sorted_tup = tuple(sorted(tup, key=itemgetter(3)))

print sorted_tup

And then follow the steps listed in this answer

edited May 23 '17 at 12:14

Community

1
1

answered Apr 21 '15 at 17:50

letsc

2,515
5
35
54

Thank You :) .. its actually working with a print statement but not updating the database. – Hima Bhamidipati Apr 21 '15 at 17:55
1

If you are doing a `SELECT` on your database it will only return the tuples that match your query. I dont exactly know what you mean by updating here – letsc Apr 21 '15 at 17:56
i want to update this sorted information back into MySql database. – Hima Bhamidipati Apr 21 '15 at 18:42
Rows in relational databases have no inherent order. A table is a *bag* of rows, not an array. So updating the table "in sorted order" isn't a thing. – O. Jones Feb 28 '21 at 16:33

sort names in alphabitical order in mysql using python

1 Answers1