[Tutor] How to create a sqlite table schema dynamically
Alan Gauld
alan.gauld at btinternet.com
Thu Mar 20 20:27:57 CET 2014
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()
> 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.
> 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)
> 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.
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])
> 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
--
Alan G
Author of the Learn to Program web site
http://www.alan-g.me.uk/
http://www.flickr.com/photos/alangauldphotos
More information about the Tutor
mailing list