[Tutor] String with literal %s

[Toni Fuente]

* Alan Gauld <alan.gauld at btinternet.com> [2014-03-20 16:18:35 +0000]:

> On 20/03/14 14:46, Toni Fuente wrote:
> 
> >I am trying to create a string this way:
> >
> >insertion = "INSERT INTO mytable(week %s) VALUES (\%s, \%s)" % osStringI
> >
> >not enough arguments for format string
> >
> >Where the first %s is going to be substitute by the variable osStringI,
> >but the other two VALUES (\%s, \%s), should be created as literals '%s'.
> 
> 
> If you double the % sign it is treated as a percent character.
> 
> "INSERT INTO mytable(week %s) VALUES (%%s, %%s)"
> 
> But do you really want to do it this way?
> Wouldn't it be better to use the SQLite parameter insertion syntax
> and do it all at the execute stage?
> 
> cur.execute("INSERT INTO mytable(week ?) VALUES (?, ?)", val1,val2,val3)
> 
> just a thought,

Thank you Alan, that is what I was trying to achieve (%%s)

I forgot to mention that I am using python 2.4.

And here is what I am trying to do for the bigger picture:


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

A prig is a fellow who is always making you a present of his opinions.
		-- George Eliot