Retrieving possible list for use in a subsequent INSERT

[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

-- 
Joel Goldstick
http://joelgoldstick.com