How to add a current string into an already existing list

[Nick the Gr33k]

Trying to add the current filename into the existent 'downloads' column
Somehow i don't think i just use the plus sign into an existing column.
We don't try to add numbers here but add an extra string to an already
existing array of strings(list).

======================================================
# update specific torrent's download counter
cur.execute('''UPDATE files SET hits = hits + 1, host = %s, city = %s,
lastvisit = %s WHERE torrent = %s''', (host, city, lastvisit, filename) )

# update specific visitor's download record
cur.execute('''UPDATE visitors SET downloads = downloads + %s WHERE host
= %s''', (filename, host) )
======================================================




Retrieval time for displaying purposes:
======================================================
downloads = []
if cur.rowcount:
for torrent in data:
downloads = ', '.join( torrent )
else:
downloads = 'Κανένα κατέβασμα ταινίας'

# add this visitor entry into database (visits is unique)
cur.execute('''INSERT INTO visitors (counterID, refs, host, city,
useros, browser, visits, downloads) VALUES (%s, %s, %s, %s, %s, %s, %s,
%s)''', (cID, refs, host, city, useros, browser, visits, downloads) )
======================================================

Is this correct, personally i would just prefer:

for torrent in data:
downloads.append( torrent )

Can you tell me the differenced on these two ways?

Aren't the result of both of them a list?

 

[Andreas Perstinger]

Trying to add the current filename into the existent 'downloads' column
Somehow i don't think i just use the plus sign into an existing column.
We don't try to add numbers here but add an extra string to an already
existing array of strings(list).
[SNIP]

# update specific visitor's download record
cur.execute('''UPDATE visitors SET downloads = downloads + %s WHERE host
= %s''', (filename, host) )
======================================================

Well, when do you understand that your MySQL problems have nothing to do
with Python?

Everything inside the triple quotes is MySQL specific, so it's a MySQL
problem whether you can use + to "add an extra string to an already
existing array of strings(list)".

This list is not a MySQL support forum.

Bye, Andreas

 

[Denis McMahon]

I just want a mysql column type that can be eligible to store an array
of elements, a list that is, no need for having a seperate extra table
for that if we can have a column that can store a list of values.

You'd better take that up with the mysql designers.

 

[rusi]

You'd better take that up with the mysql designers.

That Codd...
Should have studied some computer science

[Ive a vague feeling I am repeating myself...]

 

[Jussi Piitulainen]

You'd better take that up with the mysql designers.

That Codd...
Should have studied some computer science

[Ive a vague feeling I am repeating myself...]

Date and Darwen think that SQL designers should have studied and
implemented relational theory (Codd's database theory). This doesn't
contradict you, of course - possibly Codd should have studied CS.

Date and Darwen think also that structured values like sets and
relations should be allowed in a database and supported by the query
language. Maybe their work should be taken as what Codd might think
today, while the implementors of SQL products go their own merry ways.

I grepped through one SQL standard on the web once to see how it
refers to relational theory. Every single occurrence of "relation" was
a substring of "correlational value" or something like that. My take
from that is that SQL doesn't even pretend to be an implementation of
Codd's theory.

Suppose a database allowed structured values like lists of strings,
lists of numbers, or even lists of such lists and more. Then it would
actually be a Python issue how best to support that database.

 

[Chris Angelico]

Suppose a database allowed structured values like lists of strings,
lists of numbers, or even lists of such lists and more. Then it would
actually be a Python issue how best to support that database.

PostgreSQL supports some higher-level structures like arrays.
Personally, though, I think the most general representation of a
Python list in a database is either a varchar field with some form of
structure (eg the repr of a list), or a separate table with a foreign
key back to this one.

ChrisA

 

[Roy Smith]

PostgreSQL supports some higher-level structures like arrays.
Personally, though, I think the most general representation of a
Python list in a database is either a varchar field with some form of
structure (eg the repr of a list), or a separate table with a foreign
key back to this one.

When you say "database" here, you're really talking about relational
databases. There are other kinds.

In MongoDB, for example, storing a list of lists of strings is a
perfectly reasonable and straight-forward thing to do. Anything which
can be represented by bson (which is more or less the same as anything
which can be represented by json) can be inserted directly.

 

[Chris Angelico]

When you say "database" here, you're really talking about relational
databases. There are other kinds.

Quite right, my apologies. I'm talking about the classic relational
database accessed by SQL, which is what the OP's currently working
with (MySQL). I've worked with other types of database, but was trying
to stick as closely as possible to the question. But yes, the
clarification is important here.

ChrisA

 

[Gregory Ewing]

I just want a mysql column type that can be eligible to store an array
of elements, a list that is, no need for having a seperate extra table
for that if we can have a column that can store a list of values.

Relational database systems typically don't provide any
such type, because it's not the recommended way of storing
that kind of data in a relational database.

The recommended way is to use a secondary table, as has
been pointed out.

You're making things difficult for yourself by refusing
to consider that solution.

 

[Antoon Pardon]

Op 03-11-13 07:06, Gregory Ewing schreef:

Relational database systems typically don't provide any
such type, because it's not the recommended way of storing
that kind of data in a relational database.

The recommended way is to use a secondary table, as has
been pointed out.

You're making things difficult for yourself by refusing
to consider that solution.

You are talking to Nikos! The person who choose code because
he prefers how it looks over examples of working code.

 

[Roy Smith]

Relational database systems typically don't provide any
such type, because it's not the recommended way of storing
that kind of data in a relational database.

The recommended way is to use a secondary table, as has
been pointed out.

Most SQL databases allow you to store arbitrary data as an opaque value
(i.e. BLOB). So, one possibility would be to just serialize your list
(pickle, json, whatever) and store it that way. I've seen databases
that didn't use BLOB, but just stored json in a string field.

The limitation, of course, is that the data is opaque as far as the
database goes; you can't do queries against it. But, if all you need to
do is store the list and be able to retrieve it, it's a perfectly
reasonable thing to do, and a lot more efficient than doing a join on a
secondary table.

Normalization is for database weenies

 

[Chris Angelico]

The limitation, of course, is that the data is opaque as far as the
database goes; you can't do queries against it. But, if all you need to
do is store the list and be able to retrieve it, it's a perfectly
reasonable thing to do, and a lot more efficient than doing a join on a
secondary table.

Yeah, that can be an effective way to store complex data - especially
if the nesting level isn't fixed. (Normalization can handle a
single-level list, but it's a lot messier for handling lists of lists,
for instance.)

I still think that the OP's task would be best suited to a separate
table (one table of visitors, another of downloads, where the
Downloads table has a foreign key to Visitors), but I'm reminded of
XKCD 1027: the thing standing in the way of his code is that the
person coding it... is him. And of course, this is all without getting
into the non-code aspects of this proposal - as have been mentioned
several times, like EU regulations on retaining this level of data.

ChrisA

 

[Nick the Gr33k]

Στις 3/11/2013 2:16 μμ, ο/η Roy Smith έγÏαψε:

Most SQL databases allow you to store arbitrary data as an opaque value
(i.e. BLOB). So, one possibility would be to just serialize your list
(pickle, json, whatever) and store it that way. I've seen databases
that didn't use BLOB, but just stored json in a string field.

The limitation, of course, is that the data is opaque as far as the
database goes; you can't do queries against it. But, if all you need to
do is store the list and be able to retrieve it, it's a perfectly
reasonable thing to do, and a lot more efficient than doing a join on a
secondary table.

Normalization is for database weenies

Exactly my sentiments Roy!

Call me picky but even if they try to hit me hard i wll always get to
pick the simplest and better looking way.


I have managed to make my code work by:


create table visitors
(
counterID integer(5) not null,
host varchar(50) not null,
refs varchar(25) not null,
city varchar(20) not null,
userOS varchar(10) not null,
browser varchar(10) not null,
visits datetime not null,
hits integer(5) not null default 1,
downloads varchar(50) not null default '',
foreign key (counterID) references counters(ID),
unique index (visits)
)ENGINE = MYISAM;


=============================================
# add this visitor entry into database (hits && downloads are defaulted)
cur.execute('''INSERT INTO visitors (counterID, refs, host, city,
useros, browser, visits) VALUES (%s, %s, %s, %s, %s, %s, %s)''', (cID,
ref, host, city, useros, browser, lastvisit) )
=============================================

and later on ....

=============================================
torrents = []
# check if visitor has downloaded movies
for download in downloads:
if download != '':
torrents.append( download )

# present visitor's movie picks if any
if torrents:
print( '<td><select>' )
for n, torrent in enumerate( torrents ):
if n == 0:
op_selected = 'selected'
else:
op_selected = ''
print( '<option %s> %s </option>' % (op_selected, torrent) )
print( '</select></td>' )
else:
print( '<td><center><b><font color=white> Δεν Ï€Ïαγματοποίηθηκαν ακόμη!
</td>' )
break
=============================================


Please since this column you mentioned is able to store a Python's list
datatype could you tell me what needs alternation in:

1. MySQL's visitor's table definition time
2. python database cur.execute method
3. retrieval time

Thank you very much for anyone wishes to give me a hand here.

 

[Denis McMahon]

Please since this column you mentioned is able to store a Python's list
datatype could you tell me what needs alternation in:

We've already told you, there is NO mysql datatype that can store a
python list directly. There are ways of storing lists in mysql, but you
can't pass a list directly into a mysql update or insert query as a
single record field (you may be able to pass a list of tuples in for a
multi row update or insert, but that's a different issue).

You could convert the python list into a storable entity, for example
imploding a list of strings with some arbitrary separator to create a
long string, store the long string, then when you read it from the
database explode it back into a list.

Or you could store each element of the list in a "downloads" tables along
with a reference to the associated record in the "users" table.

These are two possible methods of storing a list that is associated with
a user entry. Which method you use is up to you. There may be others.
Pick a method and code it.

There is no built in support in the python / mysql system for putting a
list straight into a database, because mysql does not have a "collection"
record type.

 

[Nick the Gr33k]

Στις 5/11/2013 12:46 πμ, ο/η Denis McMahon έγÏαψε:

There is no built in support in the python / mysql system for puttinga
list straight into a database, because mysql does not have"collection"
record type.

Does postgresql has this 'collection' record type

You could convert the python list into a storable entity, for example
imploding a list of strings with some arbitrary separator to create a
long string, store the long string, then when you read it from the
database explode it back into a list.
Which method you use is up to you. There may be others.
Pick a method and code it.

Okey here is my attempt to code your solution as best as i can get my
head around it:

This is the part that is responsible to do the database insertion
converting scalars to lists and backwards.

=====================================
try:
# if first time for webpage; create new record( primary key is
automatic, hit is defaulted ), if page exists then update record
cur.execute('''INSERT INTO counters (url) VALUES (%s) ON DUPLICATE KEY
UPDATE hits = hits + 1''', page )
cID = cur.lastrowid

# fetch those columns that act as lists but are stored as strings
cur.execute('''SELECT refs, visits, downloads FROM visitors WHERE
counterID = %s''', cID )
data = cur.fetchone

ref = data[0]
visit = data[1]
download = data[2]

# retrieve long strings and convert them into lists respectively
refs = ref.split()
visits = visit.split()
downloads = download.split()

# add current strings to the each list respectively
refs.appends( ref )
visits.appends( visit )
downloads.appends( download )

# convert lists back to longstrings
refs = ', '.join( refs )
visits = ', '.join( visits )
downloads = ', '.join( downloads )

# add this visitor entry into database (hits && downloads are defaulted)
cur.execute('''INSERT INTO visitors (counterID, refs, host, city,
useros, browser, visits, hits = hits + 1, downloads) VALUES (%s, %s, %s,
%s, %s, %s, %s, %s, %s)''',
(cID, refs, host, city, useros, browser, visits, hits, downloads) )

con.commit()
except pymysql.ProgrammingError as e:
print( repr(e) )
con.rollback()
sys.exit(0)
===================================

Please tell me if this logic is correct, for some reason it doesn't do
what i need it to do.

Thank you.

 

[Nick the Gr33k]

Στις 5/11/2013 8:54 πμ, ο/η Nick the Gr33k έγÏαψε:

Στις 5/11/2013 12:46 πμ, ο/η Denis McMahon έγÏαψε:

There is no built in support in the python / mysql system for puttinga
list straight into a database, because mysql does not have"collection"
record type.

Does postgresql has this 'collection' record type

You could convert the python list into a storable entity, for example
imploding a list of strings with some arbitrary separator to create a
long string, store the long string, then when you read it from the
database explode it back into a list.
Which method you use is up to you. There may be others.
Pick a method and code it.

Okey here is my attempt to code your solution as best as i can get my
head around it:

This is the part that is responsible to do the database insertion
converting scalars to lists and backwards.

=====================================
try:
# if first time for webpage; create new record( primary key is
automatic, hit is defaulted ), if page exists then update record
cur.execute('''INSERT INTO counters (url) VALUES (%s) ON
DUPLICATE KEY UPDATE hits = hits + 1''', page )
cID = cur.lastrowid

# fetch those columns that act as lists but are stored as strings
cur.execute('''SELECT refs, visits, downloads FROM visitors
WHERE counterID = %s''', cID )
data = cur.fetchone

ref = data[0]
visit = data[1]
download = data[2]

# retrieve long strings and convert them into lists respectively
refs = ref.split()
visits = visit.split()
downloads = download.split()

# add current strings to the each list respectively
refs.appends( ref )
visits.appends( visit )
downloads.appends( download )

# convert lists back to longstrings
refs = ', '.join( refs )
visits = ', '.join( visits )
downloads = ', '.join( downloads )

# add this visitor entry into database (hits && downloads are
defaulted)
cur.execute('''INSERT INTO visitors (counterID, refs, host,
city, useros, browser, visits, hits = hits + 1, downloads) VALUES (%s,
%s, %s, %s, %s, %s, %s, %s, %s)''',
(cID, refs, host, city, useros, browser,
visits, hits, downloads) )

con.commit()
except pymysql.ProgrammingError as e:
print( repr(e) )
con.rollback()
sys.exit(0)
===================================

Please tell me if this logic is correct, for some reason it doesn't do
what i need it to do.

Thank you.

Better version for it, i think, but still none working:

=========================================
# if first time for webpage; create new record( primary key is
automatic, hit is defaulted ), if page exists then update record
cur.execute('''INSERT INTO counters (url) VALUES (%s) ON DUPLICATE KEY
UPDATE hits = hits + 1''', page )
cID = cur.lastrowid

# fetch those columns that act as lists but are stored as strings
cur.execute('''SELECT refs, visits, downloads FROM visitors WHERE
counterID = (SELECT ID FROM counters WHERE url = %s) ORDER BY visits
DESC''', page )
data = cur.fetchall

for row in data:
(refs, visits, downloads) = row

# retrieve long strings and convert them into lists respectively
refs = ref.split()
visits = visit.split()
downloads = download.split()

# add current strings to each list respectively
refs.appends( ref )
visits.appends( visit )
downloads.appends( download )

# convert lists back to longstrings
refs = ', '.join( refs )
visits = ', '.join( visits )
downloads = ', '.join( downloads )

# add this visitor entry into database (hits && downloads are defaulted)
cur.execute('''INSERT INTO visitors (counterID, refs, host, city,
useros, browser, visits, hits = hits + 1, downloads) VALUES (%s, %s, %s,
%s, %s, %s, %s, %s, %s)''',
(cID, refs, host, city, useros, browser, visits, hits, downloads) )
=========================================

[Tue Nov 05 10:06:57 2013] [error] [client 176.92.96.218] Traceback
(most recent call last):
[Tue Nov 05 10:06:57 2013] [error] [client 176.92.96.218] File
"/home/nikos/public_html/cgi-bin/metrites.py", line 267, in <module>
[Tue Nov 05 10:06:57 2013] [error] [client 176.92.96.218] for row in
data:
[Tue Nov 05 10:06:57 2013] [error] [client 176.92.96.218] TypeError:
'method' object is not iterable

How is ti possible for data to be none iterable?

 

[Antoon Pardon]

Op 05-11-13 09:21, Chris Angelico schreef:

Do you know how to call a method in Python? If not, go back to the
beginning of the tutorial and start reading. If so, look through your
code and see where you have a 'method' object that you are trying to
treat as iterable. It's really REALLY obvious, and you even have the
line number to tell you.

Nick, you *need* to learn how to read Python tracebacks. They are
incredibly helpful. Be glad you don't just get "Segmentation fault"
and a process termination (or, worse, a security hole).

No he doesn't. It seems there will always be someone who can't resist
the temptation to spoon feed him. Sooner or later someone will provide
him the answer he craves.

So no, Nikos doesn't need to learn anything.

 

[Nick the Gr33k]

Στις 5/11/2013 10:21 πμ, ο/η Chris Angelico έγÏαψε:

Do you know how to call a method in Python? If not, go back to the
beginning of the tutorial and start reading. If so, look through your
code and see where you have a 'method' object that you are trying to
treat as iterable. It's really REALLY obvious, and you even have the
line number to tell you.

Nick, you *need* to learn how to read Python tracebacks. They are
incredibly helpful. Be glad you don't just get "Segmentation fault"
and a process termination (or, worse, a security hole).

data = cur.fetchall

for row in data:

the only thing i can understand by looking the above 2 lines is this:

'fo'r fails to iterate over 'data' because for some reason 'data'
haven't resulted as a list of rows that can be iterated.

But that just doesn't hlp me much.

 

[Nick the Gr33k]

Στις 5/11/2013 10:21 πμ, ο/η Chris Angelico έγÏαψε:

Do you know how to call a method in Python? If not, go back to the
beginning of the tutorial and start reading. If so, look through your
code and see where you have a 'method' object that you are trying to
treat as iterable. It's really REALLY obvious, and you even have the
line number to tell you.

Nick, you *need* to learn how to read Python tracebacks. They are
incredibly helpful. Be glad you don't just get "Segmentation fault"
and a process termination (or, worse, a security hole).

===============
data = cur.fetchall
for row in data:
===============

The only thing i can understand by looking the above 2 lines is this:

'for' fails to iterate over 'data' because for some reason 'data'
haven't resulted as a list of rows that can be iterated row by row.

But that just doesn't help me much.

 

[M.F.]

===============
data = cur.fetchall

data = cur.fetchall()
That is what the stack trace and Christ tried to inform you.