[Tutor] How to create a sqlite table schema dynamically

[Toni Fuente]

* bob gailer <bgailer at gmail.com> [2014-03-20 11:38:47 -0400]:

> On 3/19/2014 8:19 AM, Toni Fuente wrote:
> >Hello everyone,
> >
> >I am stack with a problem that I can't find a solution:
> >
> >I need to create a sqlite schema dynamically, I've got a dictionary with
> >text keys: "RedHat", "CentOS", "SLES9",..., "etc", "etc"
> >
> >My intention was at the time of creating the table schema run a loop
> >through the dictionary keys and incorporate them to the schema:
> >
> >for os in osDict.items():
> >    cur.execute('''CREATE TABLE mytable(week INTEGER NOT NULL, os TEXT NOT NULL, number INTEGER NOT NULL)''')
> IMHO you are mixing data with column names. Usually the column name
> in this case would be just os.
> 
> What is your use case for this?

I'll try to explain: 

This is a kind of little job/exercise, to learn some python.

I got a database from where I get the data that I am going to process,
and create a dictionary osDict. This dictionary has the form of:

osDict = {'CentOS v4.x': 10, 'Linux OS': 5, 'Redhat Enterprise 4': 7}

I want to create a weekly report in form of a csv or a spreadsheet file,
with the quantity of different OS that have been installed, and store it
in a sqlite database.

So the table schema for the sqlite database would be:

for os in osDict:
    osString += ', ' + '"' + os + '"' + ' TEXT NOT NULL'

schema = "CREATE TABLE newOS(week INTEGER NOT NULL%s)" % osString

Now I can create the table:

cur.execute("%s" % schema)

My next step is to fill up the sqlite table with data, and that was
about my next email to the list with subject "String with literal %s".

Thanks to Alan Gauld now I know how to add those literal %s.

for os in osDict:
    osStringI += ', ' + '"' + os + '"'

insertion = "INSERT INTO newOS(week%s) VALUES (%%s, %%s)" % osStringI

Now I should be able to populate the table, I am now in this stage, so I
haven't tried now but this is the code:

for os in osDict:
    cur.execute("%s" % insertion ... mmmhh how do I key in now the
    values?

my idea was to do something like this:

for os in osDict:
    cur.execute("%s" % insertion which will expand to:
                "INSERT INTO newOS(week, "Redhat Enterprise 4", "Linux OS", "CentOS v4.x") VALUES (%s, %s)" , (weekNumber, osDict[os])

Where weekNumber = datetime.date.today().isocalendar()[1]
and osDict[os] the number of OS installed of each one.

But yes, now I can see new problems, and here is where I am at the
moment.

Any advise is very welcome.


-- 
Toni

Ninguna palabra asoma a mis labios sin que haya estado primero en mi
corazón.
		-- Andre Gide. (1869-1951) Escritor francés.