[Tutor] How to create a sqlite table schema dynamically

[Toni Fuente]

* Alan Gauld <alan.gauld at btinternet.com> [2014-03-20 19:27:57 +0000]:

> On 20/03/14 17:31, Toni Fuente wrote:
> 
> >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)
> 
> You should never do this, it is a huge security hole and even if you
> are not opening it up to outside access you should still avoid it as
> bad practice.
> 
> Instead use the SQLite executes own parameter mechanism.
> Use a question mark instead of %s and pass the values
> into the execute()
> 

Ok, I see, I'll use SQLite own parameter then.

> >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".
> 
> I confess I'm still not clear on your schema. What should the
> populated table(s) look like? It all feels very un-SQL like to me.

It would be like this:

Week, Redhat, CentOS 6, CentOS 5, Debian Squeeze, Debian Whezzy, ..., Ubuntu, Solaris, Windows XP, Windows 7

13	4	6	   5	        3		5		8	4	  4		8
14	3	7	   4		3		5		7	4	  4		4
15

I want to generated the columns dynamically from that dictionary,
osDict, that I've created collecting data from an outside database:

osDict = {'Redhat': 4, 'CentOS 6': 6, 'CentOS 5': 5,..., 'Windows 7': 8}

This osDict will have different values every week that I run the script
and grab the data from the external database, the keys (OS) will be the
same.

What I want to do is create the schema dynamically, without keying in
all the different operating systems.

> >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?
> 
> You use ? in your insert string:
> 
> insertion = "INSERT INTO newOS(week%s) VALUES (?, ?)" % osStringI
> 
> for os in osDict:
>       cur.execute(insertion, val1,val2)

I thought I wouldn't be able to use SQLite own parameter with python
2.4, but I will give it a try, and if I can't, I will found a place
sqlite3 module.

> >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])
> >
> 
> This is whee I'm confused.
> 
> You have a single table, newOS with 4 columns. And you are trying to
> insert only two values? Its not valid SQL.

It will have more than four values and my intention was that it would go
through a loop that will put the values into the different OS columns, which
just one row for each week. As the description above.

I think I am trying to build the table as I want the report to look
like.

Is it the wrong way to create this table?

> I would expect your table to have only 3 columns:
> 
> week, Name, quantity.
> 
> and the insert to be like
> 
> insert into os(week, name quantity) values(weekNumber, os, osDict[os])

If you think this is the right way to approach this problem I'll do it
like that. My first intention was to explore how to create dynamically
the schema. 

> >Where weekNumber = datetime.date.today().isocalendar()[1]
> >and osDict[os] the number of OS installed of each one.
> 
> 
> You then run your report with something like
> 
> select name, count from os
> where week == '15'
> 
> or somesuch

Aha, and then produce the report in the way, that I am trying to build
the table?

Week, Redhat, CentOS 6, CentOS 5, Debian Squeeze, Debian Whezzy, ..., Ubuntu, Solaris, Windows XP, Windows 7

13      4       6          5            3               5               8       4         4             8
14      3       7          4            3               5               7       4         4             4

> Alan G
> Author of the Learn to Program web site
> http://www.alan-g.me.uk/
> http://www.flickr.com/photos/alangauldphotos

Regards,
-- 
Toni

Well, O.K.  I'll compromise with my principles because of EXISTENTIAL DESPAIR!