Retrieving possible list for use in a subsequent INSERT

N

Nick the Gr33k

====================================
# 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 )
# get the primary key value of the new added record
cID = cur.lastrowid


# find out if visitor has downloaded torrents in the past
cur.execute('''SELECT torrent FROM files WHERE host = %s''', host )
data = cur.fetchall()

downloads = []
if data:
for torrent in data:
downloads.append( torrent )
else:
downloads.append( 'None Yet' )


# add this visitor entry into database
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) )
====================================


Hello,
In my attempt to add as an extra column key the possible downloads of
the current visitor( based on its hostname to identify him) i have wrote
the above code to try to do so.

I'am afraid something its not working as i expect it to work.
Where is my mistake?
 
N

Nick the Gr33k

The error seen form error log is:

[Thu Oct 31 09:29:35 2013] [error] [client 46.198.103.93]
[Thu Oct 31 09:29:35 2013] [error] [client 46.198.103.93] Traceback
(most recent call last):
[Thu Oct 31 09:29:35 2013] [error] [client 46.198.103.93] File
"/home/nikos/public_html/cgi-bin/metrites.py", line 274, in <module>
[Thu Oct 31 09:29:35 2013] [error] [client 46.198.103.93] (cID,
refs, host, city, useros, browser, visits, downloads) )

[Thu Oct 31 09:29:35 2013] [error] [client 46.198.103.93]
pymysql.err.InternalError: (1241, 'Operand should contain 1 column(s)')

line 274 is:

# add this visitor entry into database
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) )
 
N

Nick the Gr33k

Στις 31/10/2013 11:32 πμ, ο/η Nick the Gr33k έγÏαψε:
The error seen form error log is:

[Thu Oct 31 09:29:35 2013] [error] [client 46.198.103.93]
[Thu Oct 31 09:29:35 2013] [error] [client 46.198.103.93] Traceback
(most recent call last):
[Thu Oct 31 09:29:35 2013] [error] [client 46.198.103.93] File
"/home/nikos/public_html/cgi-bin/metrites.py", line 274, in <module>
[Thu Oct 31 09:29:35 2013] [error] [client 46.198.103.93] (cID,
refs, host, city, useros, browser, visits, downloads) )

[Thu Oct 31 09:29:35 2013] [error] [client 46.198.103.93]
pymysql.err.InternalError: (1241, 'Operand should contain 1 column(s)')

line 274 is:

# add this visitor entry into database
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) )
Click to expand...


This error happened at the moment when i decided to insert the
'downloads' column into the 'visitors' database
 
N

Nick the Gr33k

Στις 31/10/2013 1:19 μμ, ο/η Nick the Gr33k έγÏαψε:
Στις 31/10/2013 11:32 πμ, ο/η Nick the Gr33k έγÏαψε:
The error seen form error log is:

[Thu Oct 31 09:29:35 2013] [error] [client 46.198.103.93]
[Thu Oct 31 09:29:35 2013] [error] [client 46.198.103.93] Traceback
(most recent call last):
[Thu Oct 31 09:29:35 2013] [error] [client 46.198.103.93] File
"/home/nikos/public_html/cgi-bin/metrites.py", line 274, in <module>
[Thu Oct 31 09:29:35 2013] [error] [client 46.198.103.93] (cID,
refs, host, city, useros, browser, visits, downloads) )

[Thu Oct 31 09:29:35 2013] [error] [client 46.198.103.93]
pymysql.err.InternalError: (1241, 'Operand should contain 1 column(s)')

line 274 is:

# add this visitor entry into database
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) )
Click to expand...


This error happened at the moment when i decided to insert the
'downloads' column into the 'visitors' database
Click to expand...



someone please that is aware of what's wrong....?
 
R

rurpy

[...]
# find out if visitor has downloaded torrents in the past
cur.execute('''SELECT torrent FROM files WHERE host = %s''', host )
data = cur.fetchall()

downloads = []
if data:
for torrent in data:
downloads.append( torrent )
else:
downloads.append( 'None Yet' )

# add this visitor entry into database
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) )
[...]
and


The error seen form error log is:

[Thu Oct 31 09:29:35 2013] [error] [client 46.198.103.93]
[Thu Oct 31 09:29:35 2013] [error] [client 46.198.103.93] Traceback
(most recent call last):
[Thu Oct 31 09:29:35 2013] [error] [client 46.198.103.93] File
"/home/nikos/public_html/cgi-bin/metrites.py", line 274, in <module>
[Thu Oct 31 09:29:35 2013] [error] [client 46.198.103.93] (cID,
refs, host, city, useros, browser, visits, downloads) )

[Thu Oct 31 09:29:35 2013] [error] [client 46.198.103.93]
pymysql.err.InternalError: (1241, 'Operand should contain 1 column(s)')

line 274 is:

# add this visitor entry into database
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) )
Click to expand...


You set the value of 'downloads' to a list:
downloads = []
if data:
for torrent in data:
downloads.append( torrent )
Click to expand...

and when you use 'downloads', use have:

INSERT INTO visitors (..., downloads) VALUES (..., %s), (..., downloads)

If the 'downloads' column in table 'visitors' is a
normal scalar value (text string or such) then perhaps
you can't insert a value that is a list into it? And
that may be causing your problem?

If that is in fact the problem (I am only guessing), you
could convert 'downloads' to a single string for insertion
into your database with something like,

downloads = ', '.join( downloads )
 
N

Nick the Gr33k

Στις 31/10/2013 9:22 μμ, ο/η (e-mail address removed) έγÏαψε:
You set the value of 'downloads' to a list:
downloads = []
if data:
for torrent in data:
downloads.append( torrent )
Click to expand...
and when you use 'downloads', use have:

INSERT INTO visitors (..., downloads) VALUES (..., %s), (..., downloads)

If the 'downloads' column in table 'visitors' is a
normal scalar value (text string or such) then perhaps
you can't insert a value that is a list into it? And
that may be causing your problem?

If that is in fact the problem (I am only guessing), you
could convert 'downloads' to a single string for insertion
into your database with something like,

downloads = ', '.join( downloads )
Click to expand...

Hello rurpy! I haven't forget ypu still have to answer you i the other
thread for the big explanation you provided, i just didnt had the time yet!

Yes indeed by MySQL's time definition 'downloads' columns is set as:
'varchar(50) not null'

So we have 2 options as you said:

1. Alter the type of 'downloads' colums to soemthign that can hold a list

2. Alter the code to make list beceome an alltogether joined string.

# find out if visitor had downloaded torrents in the past
cur.execute('''SELECT torrent FROM files WHERE host = %s''', host )
data = cur.fetchall()

downloads = []
if data:
for torrent in data:
downloads = ', '.join( downloads )
else:
downloads = None

# add this visitor entry into database (host && downloads are 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) )


I can be seen here: http://superhost.gr/?show=log&page=index.html


But this unfortunate;y do not produce proper results
 
N

Nick the Gr33k

Στις 31/10/2013 9:22 μμ, ο/η (e-mail address removed) έγÏαψε:
You set the value of 'downloads' to a list:
downloads = []
if data:
for torrent in data:
downloads.append( torrent )
Click to expand...
and when you use 'downloads', use have:

INSERT INTO visitors (..., downloads) VALUES (..., %s), (..., downloads)

If the 'downloads' column in table 'visitors' is a
normal scalar value (text string or such) then perhaps
you can't insert a value that is a list into it? And
that may be causing your problem?

If that is in fact the problem (I am only guessing), you
could convert 'downloads' to a single string for insertion
into your database with something like,

downloads = ', '.join( downloads )
Click to expand...

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,
hits integer(5) not null default 1,
visits datetime not null,
downloads set('Καμία Ακόμη'),
foreign key (counterID) references counters(ID),
unique index (visits)
)ENGINE = MYISAM;

Decided to declare downlods as SET column type.
and maintain this:

downloads = []
if data:
for torrent in data:
downloads.append( torrent )


but error still is:

[Thu Oct 31 22:24:41 2013] [error] [client 46.198.103.93]
pymysql.err.InternalError: (1241, 'Operand should contain 1 column(s)')
 
N

Nick the Gr33k

Στις 1/11/2013 12:24 πμ, ο/η Nick the Gr33k έγÏαψε:
Στις 31/10/2013 9:22 μμ, ο/η (e-mail address removed) έγÏαψε:
You set the value of 'downloads' to a list:
downloads = []
if data:
for torrent in data:
downloads.append( torrent )
Click to expand...
and when you use 'downloads', use have:

INSERT INTO visitors (..., downloads) VALUES (..., %s), (...,
downloads)

If the 'downloads' column in table 'visitors' is a
normal scalar value (text string or such) then perhaps
you can't insert a value that is a list into it? And
that may be causing your problem?

If that is in fact the problem (I am only guessing), you
could convert 'downloads' to a single string for insertion
into your database with something like,

downloads = ', '.join( downloads )
Click to expand...

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,
hits integer(5) not null default 1,
visits datetime not null,
downloads set('Καμία Ακόμη'),
foreign key (counterID) references counters(ID),
unique index (visits)
)ENGINE = MYISAM;

Decided to declare downlods as SET column type.
and maintain this:

downloads = []
if data:
for torrent in data:
downloads.append( torrent )


but error still is:

[Thu Oct 31 22:24:41 2013] [error] [client 46.198.103.93]
pymysql.err.InternalError: (1241, 'Operand should contain 1 column(s)')
Click to expand...


Rurpy can you help me please solve this?
is enum or set column types what needed here as proper columns to store
'download' list?
 
N

Nick the Gr33k

Στις 31/10/2013 9:22 μμ, ο/η (e-mail address removed) έγÏαψε:
[...]
# find out if visitor has downloaded torrents in the past
cur.execute('''SELECT torrent FROM files WHERE host = %s''', host )
data = cur.fetchall()

downloads = []
if data:
for torrent in data:
downloads.append( torrent )
else:
downloads.append( 'None Yet' )

# add this visitor entry into database
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) )
[...]
and


The error seen form error log is:

[Thu Oct 31 09:29:35 2013] [error] [client 46.198.103.93]
[Thu Oct 31 09:29:35 2013] [error] [client 46.198.103.93] Traceback
(most recent call last):
[Thu Oct 31 09:29:35 2013] [error] [client 46.198.103.93] File
"/home/nikos/public_html/cgi-bin/metrites.py", line 274, in <module>
[Thu Oct 31 09:29:35 2013] [error] [client 46.198.103.93] (cID,
refs, host, city, useros, browser, visits, downloads) )

[Thu Oct 31 09:29:35 2013] [error] [client 46.198.103.93]
pymysql.err.InternalError: (1241, 'Operand should contain 1 column(s)')

line 274 is:

# add this visitor entry into database
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) )
Click to expand...


You set the value of 'downloads' to a list:
downloads = []
if data:
for torrent in data:
downloads.append( torrent )
Click to expand...

and when you use 'downloads', use have:

INSERT INTO visitors (..., downloads) VALUES (..., %s), (..., downloads)

If the 'downloads' column in table 'visitors' is a
normal scalar value (text string or such) then perhaps
you can't insert a value that is a list into it? And
that may be causing your problem?

If that is in fact the problem (I am only guessing), you
could convert 'downloads' to a single string for insertion
into your database with something like,

downloads = ', '.join( downloads )
Click to expand...


I would like to know if there's a way to store an entire list into a
MySQL table.
--
Code: 
# find out if visitor had downloaded torrents in the past
cur.execute('''SELECT torrent FROM files WHERE host = %s''', host )
data = cur.fetchall()

downloads = []
if data:
for torrent in data:
downloads.append( torrent )
else:
downloads = 'None Yet'

# add this visitor entry into database (host && downloads are 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) )

If the 'downloads' column in table 'visitors' is a
normal scalar value (text string or such) then perhaps
i cannot insert a value that is a list into it.

From within my python script i need to to store a list variable into a
mysql column.

the list is suppose to store torrent filenames in a form of

downloads = ["movie1", "movie2", "movie3", "movie3"]

is enum or set column types what needed here as proper columns to store
'download' list?

Code:
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,
hits integer(5) not null default 1,
visits datetime not null,
downloads set('None Yet'),
foreign key (counterID) references counters(ID),
unique index (visits)
)ENGINE = MYISAM;


Is the SET column type the way to do it?
i tried it but the error i'm receiving is:

pymysql.err.InternalError: (1241, 'Operand should contain 1 column(s)')

Please help pick the necessary column type that will be able to store a
a list of values.
 
N

Nick the Gr33k

Στις 1/11/2013 5:04 μμ, ο/η Nick the Gr33k έγÏαψε:
Στις 1/11/2013 12:24 πμ, ο/η Nick the Gr33k έγÏαψε:
Στις 31/10/2013 9:22 μμ, ο/η (e-mail address removed) έγÏαψε:
You set the value of 'downloads' to a list:
downloads = []
if data:
for torrent in data:
downloads.append( torrent )
and when you use 'downloads', use have:

INSERT INTO visitors (..., downloads) VALUES (..., %s), (...,
downloads)

If the 'downloads' column in table 'visitors' is a
normal scalar value (text string or such) then perhaps
you can't insert a value that is a list into it? And
that may be causing your problem?

If that is in fact the problem (I am only guessing), you
could convert 'downloads' to a single string for insertion
into your database with something like,

downloads = ', '.join( downloads )
Click to expand...

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,
hits integer(5) not null default 1,
visits datetime not null,
downloads set('Καμία Ακόμη'),
foreign key (counterID) references counters(ID),
unique index (visits)
)ENGINE = MYISAM;

Decided to declare downlods as SET column type.
and maintain this:

downloads = []
if data:
for torrent in data:
downloads.append( torrent )


but error still is:

[Thu Oct 31 22:24:41 2013] [error] [client 46.198.103.93]
pymysql.err.InternalError: (1241, 'Operand should contain 1 column(s)')
Click to expand...


Rurpy can you help me please solve this?
is enum or set column types what needed here as proper columns to store
'download' list?
Click to expand...


I can create another table for filenames and use a many to many
relationship between them because many movies can be downloaded by a
visitor and many visitors can download a movie.

That could work, but i wish to refrain from creating another mysql
tabale just to store a couple of movies the visitor could or could not
download.

Just a mysql column table that will be able to store a list(movies the
visitor selected) should do.
 
J

Joel Goldstick

Στις 31/10/2013 9:22 μμ, ο/η (e-mail address removed) έγÏαψε:
[...]
# find out if visitor has downloaded torrents in the past
cur.execute('''SELECT torrent FROM files WHERE host = %s''', host
)
data = cur.fetchall()

downloads = []
if data:
for torrent in data:
downloads.append( torrent )
else:
downloads.append( 'None Yet' )

# add this visitor entry into database
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) )
[...]

and



The error seen form error log is:

[Thu Oct 31 09:29:35 2013] [error] [client 46.198.103.93]
[Thu Oct 31 09:29:35 2013] [error] [client 46.198.103.93] Traceback
(most recent call last):
[Thu Oct 31 09:29:35 2013] [error] [client 46.198.103.93] File
"/home/nikos/public_html/cgi-bin/metrites.py", line 274, in <module>
[Thu Oct 31 09:29:35 2013] [error] [client 46.198.103.93] (cID,
refs, host, city, useros, browser, visits, downloads) )

[Thu Oct 31 09:29:35 2013] [error] [client 46.198.103.93]
pymysql.err.InternalError: (1241, 'Operand should contain 1 column(s)')

line 274 is:

# add this visitor entry into database
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) )
Click to expand...



You set the value of 'downloads' to a list:
downloads = []
if data:
for torrent in data:
downloads.append( torrent )
Click to expand...


and when you use 'downloads', use have:

INSERT INTO visitors (..., downloads) VALUES (..., %s), (...,
downloads)

If the 'downloads' column in table 'visitors' is a
normal scalar value (text string or such) then perhaps
you can't insert a value that is a list into it? And
that may be causing your problem?

If that is in fact the problem (I am only guessing), you
could convert 'downloads' to a single string for insertion
into your database with something like,

downloads = ', '.join( downloads )
Click to expand...


I would like to know if there's a way to store an entire list into a MySQL
table.
--
Code: 
# find out if visitor had downloaded torrents in the past

cur.execute('''SELECT torrent FROM files WHERE host = %s''',
host )
data = cur.fetchall()

downloads = []
if data:
for torrent in data:
downloads.append( torrent )
else:
downloads = 'None Yet'


# add this visitor entry into database (host && downloads
are 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) )


If the 'downloads' column in table 'visitors' is a
normal scalar value (text string or such) then perhaps
i cannot insert a value that is a list into it.

From within my python script i need to to store a list variable into a mysql
column.

the list is suppose to store torrent filenames in a form of

downloads = ["movie1", "movie2", "movie3", "movie3"]


is enum or set column types what needed here as proper columns to store
'download' list?

Code:

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,
hits integer(5) not null default 1,
visits datetime not null,
downloads set('None Yet'),

foreign key (counterID) references counters(ID),
unique index (visits)
)ENGINE = MYISAM;


Is the SET column type the way to do it?
i tried it but the error i'm receiving is:


pymysql.err.InternalError: (1241, 'Operand should contain 1 column(s)')

Please help pick the necessary column type that will be able to store a a
list of values.
Click to expand...

If you have a list of values of the same type, but different values,
you need a new table with a foreign key to the table it relates to.
This is a relational database question. You can read more here:

http://en.wikipedia.org/wiki/Database_normalization#Normal_forms
 
N

Nick the Gr33k

Στις 1/11/2013 5:56 μμ, ο/η Joel Goldstick έγÏαψε:
Στις 31/10/2013 9:22 μμ, ο/η (e-mail address removed) έγÏαψε:
On 10/31/2013 03:24 AM, Nick the Gr33k wrote:

[...]
# find out if visitor has downloaded torrents in the past
cur.execute('''SELECT torrent FROM files WHERE host = %s''', host
)
data = cur.fetchall()

downloads = []
if data:
for torrent in data:
downloads.append( torrent )
else:
downloads.append( 'None Yet' )

# add this visitor entry into database
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) )
[...]


and

On 10/31/2013 03:32 AM, Nick the Gr33k wrote:

The error seen form error log is:

[Thu Oct 31 09:29:35 2013] [error] [client 46.198.103.93]
[Thu Oct 31 09:29:35 2013] [error] [client 46.198.103.93] Traceback
(most recent call last):
[Thu Oct 31 09:29:35 2013] [error] [client 46.198.103.93] File
"/home/nikos/public_html/cgi-bin/metrites.py", line 274, in <module>
[Thu Oct 31 09:29:35 2013] [error] [client 46.198.103.93] (cID,
refs, host, city, useros, browser, visits, downloads) )

[Thu Oct 31 09:29:35 2013] [error] [client 46.198.103.93]
pymysql.err.InternalError: (1241, 'Operand should contain 1 column(s)')

line 274 is:

# add this visitor entry into database
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) )



You set the value of 'downloads' to a list:

downloads = []
if data:
for torrent in data:
downloads.append( torrent )


and when you use 'downloads', use have:

INSERT INTO visitors (..., downloads) VALUES (..., %s), (...,
downloads)

If the 'downloads' column in table 'visitors' is a
normal scalar value (text string or such) then perhaps
you can't insert a value that is a list into it? And
that may be causing your problem?

If that is in fact the problem (I am only guessing), you
could convert 'downloads' to a single string for insertion
into your database with something like,

downloads = ', '.join( downloads )
Click to expand...


I would like to know if there's a way to store an entire list into a MySQL
table.
--
Code: 
# find out if visitor had downloaded torrents in the past

cur.execute('''SELECT torrent FROM files WHERE host = %s''',
host )
data = cur.fetchall()

downloads = []
if data:
for torrent in data:
downloads.append( torrent )
else:
downloads = 'None Yet'


# add this visitor entry into database (host && downloads
are 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) )


If the 'downloads' column in table 'visitors' is a
normal scalar value (text string or such) then perhaps
i cannot insert a value that is a list into it.

From within my python script i need to to store a list variable into a mysql
column.

the list is suppose to store torrent filenames in a form of

downloads = ["movie1", "movie2", "movie3", "movie3"]


is enum or set column types what needed here as proper columns to store
'download' list?

Code:

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,
hits integer(5) not null default 1,
visits datetime not null,
downloads set('None Yet'),

foreign key (counterID) references counters(ID),
unique index (visits)
)ENGINE = MYISAM;


Is the SET column type the way to do it?
i tried it but the error i'm receiving is:


pymysql.err.InternalError: (1241, 'Operand should contain 1 column(s)')

Please help pick the necessary column type that will be able to store a a
list of values.
Click to expand...

If you have a list of values of the same type, but different values,
you need a new table with a foreign key to the table it relates to.
This is a relational database question. You can read more here:

http://en.wikipedia.org/wiki/Database_normalization#Normal_forms
Click to expand...



I can create another table for filenames and use a many to many
relationship between them because many movies can be downloaded by a
visitor and many visitors can download a movie.

That could work, but i wish to refrain from creating another mysql
tabale just to store a couple of movies the visitor could or could not
download.

Just a mysql column table that will be able to store a list(movies the
visitor selected) should do.
 
P

Paul Simon

???? 31/10/2013 9:22 ??, ?/? (e-mail address removed) ??????:
[...]
# find out if visitor has downloaded torrents in the past
cur.execute('''SELECT torrent FROM files WHERE host = %s''',
host
)
data = cur.fetchall()

downloads = []
if data:
for torrent in data:
downloads.append( torrent )
else:
downloads.append( 'None Yet' )

# add this visitor entry into database
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) )
[...]

and



The error seen form error log is:

[Thu Oct 31 09:29:35 2013] [error] [client 46.198.103.93]
[Thu Oct 31 09:29:35 2013] [error] [client 46.198.103.93] Traceback
(most recent call last):
[Thu Oct 31 09:29:35 2013] [error] [client 46.198.103.93] File
"/home/nikos/public_html/cgi-bin/metrites.py", line 274, in <module>
[Thu Oct 31 09:29:35 2013] [error] [client 46.198.103.93] (cID,
refs, host, city, useros, browser, visits, downloads) )

[Thu Oct 31 09:29:35 2013] [error] [client 46.198.103.93]
pymysql.err.InternalError: (1241, 'Operand should contain 1 column(s)')

line 274 is:

# add this visitor entry into database
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) )
Click to expand...



You set the value of 'downloads' to a list:
downloads = []
if data:
for torrent in data:
downloads.append( torrent )
Click to expand...


and when you use 'downloads', use have:

INSERT INTO visitors (..., downloads) VALUES (..., %s), (...,
downloads)

If the 'downloads' column in table 'visitors' is a
normal scalar value (text string or such) then perhaps
you can't insert a value that is a list into it? And
that may be causing your problem?

If that is in fact the problem (I am only guessing), you
could convert 'downloads' to a single string for insertion
into your database with something like,

downloads = ', '.join( downloads )
Click to expand...


I would like to know if there's a way to store an entire list into a MySQL
table.
--
Code: 
# find out if visitor had downloaded torrents in the past

cur.execute('''SELECT torrent FROM files WHERE host =
%s''',
host )
data = cur.fetchall()

downloads = []
if data:
for torrent in data:
downloads.append( torrent )
else:
downloads = 'None Yet'


# add this visitor entry into database (host && downloads
are 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) )


If the 'downloads' column in table 'visitors' is a
normal scalar value (text string or such) then perhaps
i cannot insert a value that is a list into it.

From within my python script i need to to store a list variable into a
mysql
column.

the list is suppose to store torrent filenames in a form of

downloads = ["movie1", "movie2", "movie3", "movie3"]


is enum or set column types what needed here as proper columns to store
'download' list?

Code:

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,
hits integer(5) not null default 1,
visits datetime not null,
downloads set('None Yet'),

foreign key (counterID) references counters(ID),
unique index (visits)
)ENGINE = MYISAM;


Is the SET column type the way to do it?
i tried it but the error i'm receiving is:


pymysql.err.InternalError: (1241, 'Operand should contain 1 column(s)')

Please help pick the necessary column type that will be able to store a a
list of values.
Click to expand...

If you have a list of values of the same type, but different values,
you need a new table with a foreign key to the table it relates to.
This is a relational database question. You can read more here:

http://en.wikipedia.org/wiki/Database_normalization#Normal_forms


--
Joel Goldstick
http://joelgoldstick.com

He doesn't <need> a many to many table, although that would put the schema
into a classic normal form. Yes, there will be duplicated data. Sometimes
de-normalizing a schema may make things simpler and easier to use for
someone not used to database work. I would also use a many to many table
being familiar with normal forms but it is not a neccessity.

Paul Simon
 
N

Nick the Gr33k

Στις 1/11/2013 7:07 μμ, ο/η Paul Simon έγÏαψε:
If you have a list of values of the same type, but different values,
you need a new table with a foreign key to the table it relates to.
This is a relational database question. You can read more here:

http://en.wikipedia.org/wiki/Database_normalization#Normal_forms
Click to expand...

I already answered to that in my previous post, this answer was Joel's
there was no need to retype it since i i saw it and responded to it.
 
D

Denis McMahon

The error seen form error log is:
Click to expand...
[Thu Oct 31 09:29:35 2013] [error] [client 46.198.103.93]
pymysql.err.InternalError: (1241, 'Operand should contain 1 column(s)')
Click to expand...
(cID, refs, host, city, useros, browser, visits, downloads) )
Click to expand...

I suspect the issue is that, as a list type, the pysql interface expects
to map "downloads" to either multiple columns, or one column in multiple
rows, ie it is expecting the list to deliver multiple data items to the
database, but the database is looking for a single data item.
 
P

Paul Simon

Nick the Gr33k said:
???? 1/11/2013 7:07 ??, ?/? Paul Simon ??????:


I already answered to that in my previous post, this answer was Joel's
there was no need to retype it since i i saw it and responded to it.
Click to expand...

Perhaps you misunderstood my response to Joel's comment. You don't <need> a
many to many table as he said above in your quote. That's required for a
normal form but isn't necessary. Denormalize the many to many form, have
duplicated data in your only table if that works for you. Storage is cheap
and its easier to create sql stsatements, too.

Paul Simon
 
N

Nick the Gr33k

Στις 1/11/2013 9:12 μμ, ο/η Denis McMahon έγÏαψε:
The error seen form error log is:
Click to expand...
[Thu Oct 31 09:29:35 2013] [error] [client 46.198.103.93]
pymysql.err.InternalError: (1241, 'Operand should contain 1 column(s)')
Click to expand...
(cID, refs, host, city, useros, browser, visits, downloads) )
Click to expand...

I suspect the issue is that, as a list type, the pysql interface expects
to map "downloads" to either multiple columns, or one column in multiple
rows, ie it is expecting the list to deliver multiple data items to the
database, but the database is looking for a single data item.
Click to expand...


--
I wish not to create an extra MySQL table but just have a column that
can store zero or multiple values within it.

Shall i use a specific column type like ENUM or SET ?

i tried it but this also doenst work.

# find out if visitor had downloaded torrents in the past
cur.execute('''SELECT torrent FROM files WHERE host = %s''', host )
data = cur.fetchall()

downloads = []
if data:
for torrent in data:
downloads.append( torrent )
else:
downloads = 'None Yet'

# add this visitor entry into database (host && downloads are 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 the baove code correct?

downloads is a list(array of multiple values) or even of zero values.

HOW this 'list' is supposed to get stored into the visitors database?

What colum is able to handle this list?
 
R

rurpy

Rurpy can you help me please solve this?
is enum or set column types what needed here as proper columns to store
'download' list?
Click to expand...

I'd help if I could but I don't use MySql and don't know anything
about its column types. All I could do it try to read about it
(which I don't have time for right now) and you know more about
it than me so you can probably figure it out more quickly.
 
N

Nick the Gr33k

Στις 2/11/2013 4:00 πμ, ο/η (e-mail address removed) έγÏαψε:
I'd help if I could but I don't use MySql and don't know anything
about its column types. All I could do it try to read about it
(which I don't have time for right now) and you know more about
it than me so you can probably figure it out more quickly.
Click to expand...


Okey here is some improvement:

Splitting the statement in 3 steps to print it before actually executing iy.

=====
sql = '''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)
print repr(sql)
cur.execute(sql)
=====

This the real time values trying to be passed into MySQL table in python
script's runtime

=====
"INSERT INTO visitors (counterID, refs, host, city, useros, browser,
visits, downloads) VALUES (1, ΧωÏίς Referrer - Άμεσο Hit,
46-198-103-93.adsl.cyta.gr, Europe/Athens, Windows, Chrome, 13-11-02
10:31:29, [('Jobs.2013. WEBRip XViD juggs',),
('Pacific.Rim.2013.720p.BDRip.XviD.AC3-ELiTE',), ('Man of Steel 2013
BRRip XviD AC3-SANTi',), ('Now You See Me EXTENDED 2013 BRRip XviD
AC3-SANTi',), ('DAS EXPERIMENT (2001) 720p.BDRip.XVID.AC3',), ('Behind
the Candelabra 2013 BDrip XviD AC3',),
('The.Internship.2013.UNRATED.480p.BRRip.Xvid.AC3',), ('Man Of Tai 2013
WEBrip XVID AC3',), ('Star Trek Into Darkness 2013 BRRip XviD
AC3-SANTi',), ('ESCAPE PLAN (2013) CAM XViD UNiQUE',)])"

ProgrammingError(ProgrammingError(1064, "You have an error in your SQL
syntax; check the manual that corresponds to your MySQL server version
for the right syntax to use near 'Referrer - Άμεσο Hit,
46-198-103-93.adsl.cyta.gr, Europe/Athens, Windows, C' at line 1"),)
=====

The definition of 'visitro's table is as follows:

=====
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,
hits integer(5) not null default 1,
visits datetime not null,
download text not null,
foreign key (counterID) references counters(ID),
unique index (visits)
)ENGINE = MYISAM;
=====

It is possible to "just" use a VARCHAR or TEXT field and then add
anything you want to it, including a comma or semi-colon separated list.
 
N

Nick the Gr33k

You can see the erro as its appearing here:

http://superhost.gr/

Its weird that no single quotes are enclosing the string values though
and the other bizarre thign is that 'downloads' list is tryign to fiull
in all the movies.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to add a current string into an already existing list 40
Adding 'download' column to existing 'visitors' table (as requested) 18
Help me with this code PLEASE 29
Printing a drop down menu for a specific field. 4
Database statements via python but database left intact 17
mysql solution 24
Select fails when cookie tried to get a numeric value 32
Printing a drop down menu for a specific field. 10

Members online

No members online now.
Total: 134 (members: 2, guests: 132)
Robots: 50

Forum statistics

Threads
473,880
Messages
2,569,944
Members
46,249
Latest member
MelodyThye

Latest Threads

Top