Retrieving possible list for use in a subsequent INSERT

[Nick the Gr33k]

Στις 1/11/2013 5:56 μμ, ο/η Joel Goldstick έγραψε:
> On Fri, Nov 1, 2013 at 11:25 AM, Nick the Gr33k <nikos.gr33k at gmail.com> wrote:
>> Στις 31/10/2013 9:22 μμ, ο/η rurpy at yahoo.com έγραψε:
>>>
>>> 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 )
>>>
>>
>>
>> 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) )
>> [/code]
>>
>>
>> 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.
>> --
>> https://mail.python.org/mailman/listinfo/python-list
>
> 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
>



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.