Spiegazione veloce del seguente programma:
- creo un file con estensione .db sul desktop
- mi collego al file .db
- creo la tabella students
- faccio 4 insert
- stampo con il comando "show" la select
- ordino per nome il database
import sqlite3, os
def connect(database):
connessione = sqlite3.connect(database)
cursore = connessione.cursor()
return connessione, cursore
database = "students.db"
file = os.path.join(os.getcwd(), database)
if os.path.exists(file):
os.remove(file)
db = open(file, "w")
db.close()
conn, c = connect(database)
c.execute("CREATE TABLE IF NOT EXISTS students(id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR, surname VARCHAR, years INT)")
c.execute('INSERT INTO students(id, name, surname, years) VALUES(10, "fabio", "raimondo", 45)')
c.execute('INSERT INTO students(id, name, surname, years) VALUES(12, "luca", "vinali", 23)')
c.execute('INSERT INTO students(id, name, surname, years) VALUES(5, "tiziano", "rovigo", 31)')
c.execute('INSERT INTO students(id, name, surname, years) VALUES(3, "daniel", "icaro", 23)')
print("\nstudent registration completed.")
while True:
operation = input("> ")
operation = operation.lower()
if operation == "show":
for student in c.execute("SELECT * FROM students"):
print(student)
elif operation == "order":
registro = c.execute("SELECT * FROM students ORDER BY name ASC")
c.execute("DELETE FROM students WHERE id > 0")
for studente in registro:
print(studente)
c.execute("INSERT INTO students(id, name, surname, years) VALUES({}, '{}', '{}', {})".format(*studente))
print("alphabetical ordering carried out.")
else:
print("error.")
il problema sorge quando cerco di ordinare il database per nome e poi a stampare la select
OUTPUT :
student registration completed.
> show
(3, 'daniel', 'icaro', 23)
(5, 'tiziano', 'rovigo', 31)
(10, 'fabio', 'raimondo', 45)
(12, 'luca', 'vinali', 23)
> order
(3, 'daniel', 'icaro', 23)
(10, 'fabio', 'raimondo', 45)
(12, 'luca', 'vinali', 23)
(5, 'tiziano', 'rovigo', 31)
alphabetical ordering carried out.
> show
(3, 'daniel', 'icaro', 23)
(5, 'tiziano', 'rovigo', 31)
(10, 'fabio', 'raimondo', 45)
(12, 'luca', 'vinali', 23)
> _
purtroppo a quanto pare non mi ha riordinato il database come volevo sapreste dirmi il perchè? grazie mille