Re: [Tutor] How to create a sqlite table schema dynamically
On 20/03/14 22:39, Toni Fuente wrote: I would expect your table to have only 3 columns: week, Name, quantity. You then run your report with something like select name, count from os where week == '15' 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 53 5 8 4 4 8 14 3 7 43 5 7 4 4 4 OK, Then I'd use something like select week, name, count from os ordered by week. That gives you all the rows in week order so all the os values for a given week are together. You can then iterate over each week group and reformat as you require to get your table above. Or use Peter's approach which I just spotted ;-) -- Alan G Author of the Learn to Program web site http://www.alan-g.me.uk/ http://www.flickr.com/photos/alangauldphotos ___ Tutor maillist - Tutor@python.org To unsubscribe or change subscription options: https://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] How to create a sqlite table schema dynamically
* Alan Gauld [2014-03-20 19:27:57 +]:
> 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 53 5 8
4 4 8
14 3 7 43 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 53 5 8
4 4 8
14 3 7 43 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
Re: [Tutor] How to create a sqlite table schema dynamically
Alan Gauld wrote:
> 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.
I'll make a bold guess that he wants to make a pivot table, something that
is indeed not supported by sqlite.
E. g., start with
week | os | installs
| |
2014-01 | redhat | 5
2014-01 | suse | 2
2014-02 | debian | 2
2014-02 | redhat | 7
2014-03 | suse | 3
2014-03 | ubuntu | 3
2014-03 | mint | 1
and wield it into something like
week | debian | mint | redhat | suse | ubuntu
| | | | |
2014-01 | 0| 0| 5| 2| 0
2014-02 | 2| 0| 7| 0| 0
2014-03 | 0| 1| 0| 3| 3
Below is my attempt:
import sqlite3
db = sqlite3.connect(":memory:")
cs = db.cursor()
data = [
# week, os, installs
("2014-01", "redhat", 5),
("2014-01", "suse", 2),
("2014-02", "debian", 2),
("2014-02", "redhat", 7),
("2014-03", "suse", 3),
("2014-03", "ubuntu", 3),
("2014-03", "mint", 1),
]
def print_row(row, space=" "):
print(" | ".join(str(field).ljust(8, space) for field in row))
def show(sql):
first = True
for row in cs.execute(sql):
if first:
print_row(d[0] for d in cs.description)
print_row(("" for d in cs.description), "-")
first = False
print_row(row)
print("")
def sanitized(name):
"""Prevent SQL injection"""
if not name.isalpha(): # XXX a tad too rigid
raise ValueError("Illegal name {!r}".format(name))
return name
cs.execute("create table weekly_installs (week, os, installs);")
cs.executemany(
"insert into weekly_installs "
"(week, os, installs) values (?, ?, ?)", data)
show("select * from weekly_installs")
distros = sorted(
sanitized(distro) for [distro] in
cs.execute("select distinct os from weekly_installs"))
cs.execute("create table pivot (week, {})".format(
", ".join(d + " default 0" for d in distros)))
cs.executemany(
"insert into pivot (week) values (?)",
cs.execute("select distinct week from weekly_installs").fetchall())
for distro in distros:
update = "update pivot set {distro} = ? where week = ?"
update = update.format(distro=distro)
lookup = ("select installs, week from weekly_installs "
"where os = '{distro}'")
lookup = lookup.format(distro=distro)
cs.executemany(update, cs.execute(lookup).fetchall())
show("select * from pivot order by week")
OK, it still may serve as a bad example ;) Doing it in Python should be
much cleaner, but I'll leave that as an exercise...
___
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] How to create a sqlite table schema dynamically
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
___
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] How to create a sqlite table schema dynamically
* bob gailer [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.
___
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] How to create a sqlite table schema dynamically
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?
But I don't know how to pass the os key to the sqlite command.
Thank you in advance for any help,
Kind regards,
___
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] How to create a sqlite table schema dynamically
* Mark Lawrence [2014-03-19 15:54:27 +]:
> On 19/03/2014 12:19, 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)''')
> >
> >But I don't know how to pass the os key to the sqlite command.
> >
> >Thank you in advance for any help,
> >Kind regards,
> >
> >
>
> http://docs.python.org/3/library/sqlite3.html#module-sqlite3 the 7th
> paragraph describes 'parameter substitution'
>
> --
> My fellow Pythonistas, ask not what our language can do for you, ask
> what you can do for our language.
>
> Mark Lawrence
>
Thank you Mark.
I forgot to say that I am using python 2.4.3, but that helped me.
--
Toni
Por los defectos de los demás el sabio corrige los propios.
-- Publio Siro.
___
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor
Re: [Tutor] How to create a sqlite table schema dynamically
On 19/03/2014 12:19, 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)''')
But I don't know how to pass the os key to the sqlite command.
Thank you in advance for any help,
Kind regards,
http://docs.python.org/3/library/sqlite3.html#module-sqlite3 the 7th
paragraph describes 'parameter substitution'
--
My fellow Pythonistas, ask not what our language can do for you, ask
what you can do for our language.
Mark Lawrence
---
This email is free from viruses and malware because avast! Antivirus protection
is active.
http://www.avast.com
___
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor
[Tutor] How to create a sqlite table schema dynamically
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)''')
But I don't know how to pass the os key to the sqlite command.
Thank you in advance for any help,
Kind regards,
--
Toni
Tímido Busca..., Bueno No..., Es Igual... Nada.
___
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor
