SQL to handle table updates in a "dynamically typed" fashion

Question

I'm playing around with Python 3's sqlite3 module, and acquainting myself with SQL in the process.

I've written a toy program to hash a salted password and store it, the associated username, and the salt into a database. I thought it would be intuitive to create a function of the signature:

def store(table, data, database=':memory:')

Callable as, for example, store('logins', {'username': 'bob', 'salt': 'foo', 'salted_hash' : 'bar'}), and be able to individually add into logins, into new a row, the value bob for username, foo for salt, et caetera.

Unfortunately I'm swamped with what SQL to code. I'm trying to do this in a "dynamically typed" fashion, in that I won't be punished for storing the wrong types, or be able to add new columns at will, for example.

I want the function to, sanitizing all input:

• Check if the table exists, and create it if it doesn't, with the passed keys from the dictionary as the columns;
• If the table already exists, check if a table has the specified columns (the keys to the passed dictionary), and add them if it doesn't (is this even possible with SQL?);
• Add the individual values from my dictionary to the appropriate columns in the dictionary.

I can use INSERT for the latter, but it seems very rigid. What happens if the columns don't exist, for example? How could we then add them?

I don't mind whether the code is tailored to Python 3's sqlite3, or just the SQL as an outline; as long as I can work it and use it to some extent (and learn from it) I'm very grateful.

(On a different note, I'm wondering what other approaches I could use instead of a SQL'd relational database; I've used Amazon's SimpleDB before and have considered using that for this purpose as it was very "dynamically typed", but I want to know what SQL code I'd have to use for this purpose.)

Answer 1

1. SQLite3 is dynamically typed, so no problem there.

2. CREATE TABLE IF NOT EXISTS <name> ... See here.

3. You can see if the columns you need already exist in the table by using sqlite_master documented in this FAQ. You'll need to parse the sql column, but since it's exactly what your program provided to create the table, you should know the syntax.

4. If the column does not exist, you can ALTER TABLE <nam>? ADD COLUMN ... See here.