* Alan Gauld <alan.ga...@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!
_______________________________________________
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor
