Path: csiph.com!usenet.pasdenom.info!aioe.org!news.stack.nl!newsfeed.xs4all.nl!newsfeed1.news.xs4all.nl!xs4all!post.news.xs4all.nl!not-for-mail
MIME-Version: 1.0
In-Reply-To: <l50h4e$306$1@dont-email.me>
References: <l4t7jt$52m$1@dont-email.me> <l4t83c$7cn$1@dont-email.me> <e1b4046c-3180-4ad8-a90a-6e87dd4df6ba@googlegroups.com> <l50h4e$306$1@dont-email.me>
Date: Fri, 1 Nov 2013 11:56:38 -0400
Subject: Re: Retrieving possible list for use in a subsequent INSERT
From: Joel Goldstick <joel.goldstick@gmail.com>
To: Nick the Gr33k <nikos.gr33k@gmail.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Cc: "python-list@python.org" <python-list@python.org>
Precedence: list
Newsgroups: comp.lang.python
Message-ID: <mailman.1935.1383321401.18130.python-list@python.org>
Lines: 164
NNTP-Posting-Host: 2001:888:2000:d::a6
Xref: csiph.com comp.lang.python:58279

On Fri, Nov 1, 2013 at 11:25 AM, Nick the Gr33k <nikos.gr33k@gmail.com> wro=
te:
> =CE=A3=CF=84=CE=B9=CF=82 31/10/2013 9:22 =CE=BC=CE=BC, =CE=BF/=CE=B7 rurp=
y@yahoo.com =CE=AD=CE=B3=CF=81=CE=B1=CF=88=CE=B5:
>>
>> 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 =3D %s''', =
host
>>> )
>>>         data =3D cur.fetchall()
>>>
>>>         downloads =3D []
>>>         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 =3D []
>>>         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 =3D ', '.join( downloads )
>>
>
>
> I would like to know if there's a way to store an entire list into a MySQ=
L
> table.
> --
> [code]
>                 # find out if visitor had downloaded torrents in the past
>
>                 cur.execute('''SELECT torrent FROM files WHERE host =3D %=
s''',
> host )
>                 data =3D cur.fetchall()
>
>                 downloads =3D []
>                 if data:
>                         for torrent in data:
>                                 downloads.append( torrent )
>                 else:
>                         downloads =3D 'None Yet'
>
>
>                 # add this visitor entry into database (host && downloads
> are unique)
>                 cur.execute('''INSERT INTO visitors (counterID, refs, hos=
t,
> 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 my=
sql
> column.
>
> the list is suppose to store torrent filenames in a form of
>
> downloads =3D ["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 =3D 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

--=20
Joel Goldstick
http://joelgoldstick.com