So I have decided to go back to an older project, the historical database I used to demonstrate the basics of SQLite.
I have decided the next version should have images associated with each record. At the moment the database does not have this, so how do we modify the columns of the database?
There is the SQLite command ALTER. Because I tend to leave the data structure (table columns and how tables relate to each other) alone in Python, this is going to be a one-off, done on the SQLite command line not as part of a Python script.
Microsoft Windows [Version 10.0.17134.407]
(c) 2018 Microsoft Corporation. All rights reserved.
C:\Users\pc>cd C:\sqlite
C:\sqlite>sqlite3
SQLite version 3.20.1 2017-08-24 16:21:36
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open db/history1.db
sqlite> .tables
historicalfigures roles weblinks
sqlite> ALTER TABLE historicalfigures ADD COLUMN imagelink text;
SQLite>
sqlite> .mode columns
sqlite> .headers on
sqlite> SELECT * FROM historicalfigures;
Name YearBirth YearDeath Role Nation imagelink
----------------- ---------- ---------- ---------- ---------- ----------
Leonardo da Vinci 1452 1519 Artist Italy
Niccolo Machiavel 1469 1527 Politician Italy (Flo
Cardinal Thomas W 1473 1530 Churchman England
William Caxton 1422 1491 Publisher England
William Shakespea 1564 1616 Author & P England
Francis Drake 1540 1596 Explorer & England
Henry VIII 1491 1547 Monarch England
Elizabeth I 1533 1603 Monarch England
Christopher Colum 1451 1509 Explorer Spain & It
Ferdinand Magella 1480 1521 Explorer Portugal
Dante Alighieri 1265 1321 Author & P Italy (Flo
Michelangelo di L 1475 1564 Artist Italy (Flo
Henry VII 1457 1509 Monarch England
Hans Holbein 1497 1543 Artist Germany &
Anne Boleyn 1501 1536 Queen Cons England
Geoffrey Chaucer 1343 1400 Poet & Aut England
Marco Polo 1254 1324 Explorer & Italy (Ven
Catherine of Arag 1485 1536 Queen Cons Spain & En
Edward VI 1537 1553 Monarch England
Galileo Galilei 1564 1642 Italy (Tus
Johannes Kepler 1571 1630 Germany
Jane Grey 1536 1554 England
Martin Luther 1483 1546 Germany
Nicolaus Copernic 1473 1543 Poland
Tomas de Torquema 1420 1498 Spain
Donald Duck 1910 2000 USA
sqlite>
This shows that the new column imagelink has been created (clearly no data yet). But it also reminds me that we have a redundant column - Role column has been superseded by the roles table.
How do we delete this column? Firstly I need to be sure that deleting it is the right thing to do. Deleting columns containing data should not be undertaken lightly.
I have discovered a way to create copies of tables in SQLite3, so I can use these to create backup tables. It involves the CREATE command, but rather than stating all the columns and their data types (which would create an empty table) I tell SQLite3 to create using an existing table (which both uses the columns with names and data types, and also uses the data contained within the table) using the format:
CREATE TABLE newtablename AS SELECT requiredcolumns from existingtablename (parameters);
sqlite> CREATE TABLE old_historicalfigures AS SELECT * from historicalfigures;
sqlite> .tables
historicalfigures roles
old_historicalfigures weblinks
SQLite>
Just for reference I also found a way to rename tables.
sqlite> ALTER TABLE historicalfigures RENAME TO old_historicalfigures;
But I digress. We now have backed up our table, so can mess around with it with less trepidation.
I have now found out there is no easy way to actually delete or drop an unwanted column in SQLite. Other forms of SQL will allow this. The best workaround is to recreate the table without the unwanted column. Actually this is not too bad - we've already discovered how to do this.
To delete a whole table
sqlite> DROP TABLE historicalfigures;
sqlite> .tables
old_historicalfigures roles weblinks
SQLite>
Now we recreate the table we want with the columns we want.
sqlite> CREATE TABLE historicalfigures AS SELECT Name, YearBirth, YearDeath, Nation, Imagelink FROM old_historicalfigures;
sqlite> SELECT * FROM historicalfigures;
Name YearBirth YearDeath Nation imagelink
----------------- ---------- ---------- ---------- ----------
Leonardo da Vinci 1452 1519 Italy
Niccolo Machiavel 1469 1527 Italy (Flo
Cardinal Thomas W 1473 1530 England
William Caxton 1422 1491 England
William Shakespea 1564 1616 England
Francis Drake 1540 1596 England
Henry VIII 1491 1547 England
Elizabeth I 1533 1603 England
Christopher Colum 1451 1509 Spain & It
Ferdinand Magella 1480 1521 Portugal
Dante Alighieri 1265 1321 Italy (Flo
Michelangelo di L 1475 1564 Italy (Flo
Henry VII 1457 1509 England
Hans Holbein 1497 1543 Germany &
Anne Boleyn 1501 1536 England
Geoffrey Chaucer 1343 1400 England
Marco Polo 1254 1324 Italy (Ven
Catherine of Arag 1485 1536 Spain & En
Edward VI 1537 1553 England
Galileo Galilei 1564 1642 Italy (Tus
Johannes Kepler 1571 1630 Germany
Jane Grey 1536 1554 England
Martin Luther 1483 1546 Germany
Nicolaus Copernic 1473 1543 Poland
Tomas de Torquema 1420 1498 Spain
Donald Duck 1910 2000 USA
sqlite>
sqlite>
And so we have added a new column, and not so much removed an unwanted column as recreated the table without the unwanted column.
Next time I hope to tie this in with Python programming.
No comments:
Post a Comment