Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]
Groups > comp.lang.python > #58279
| 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 | 2013-11-01 11:56 -0400 |
| Subject | Re: Retrieving possible list for use in a subsequent INSERT |
| From | Joel Goldstick <joel.goldstick@gmail.com> |
| Newsgroups | comp.lang.python |
| Message-ID | <mailman.1935.1383321401.18130.python-list@python.org> (permalink) |
On Fri, Nov 1, 2013 at 11:25 AM, Nick the Gr33k <nikos.gr33k@gmail.com> wrote:
> Στις 31/10/2013 9:22 μμ, ο/η rurpy@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
Back to comp.lang.python | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread
Retrieving possible list for use in a subsequent INSERT Nick the Gr33k <nikos.gr33k@gmail.com> - 2013-10-31 11:24 +0200
Re: Retrieving possible list for use in a subsequent INSERT Nick the Gr33k <nikos.gr33k@gmail.com> - 2013-10-31 11:32 +0200
Re: Retrieving possible list for use in a subsequent INSERT Nick the Gr33k <nikos.gr33k@gmail.com> - 2013-10-31 13:19 +0200
Re: Retrieving possible list for use in a subsequent INSERT Nick the Gr33k <nikos.gr33k@gmail.com> - 2013-10-31 18:49 +0200
Re: Retrieving possible list for use in a subsequent INSERT Christian Gollwitzer <auriocus@gmx.de> - 2013-10-31 19:05 +0100
Re: Retrieving possible list for use in a subsequent INSERT rurpy@yahoo.com - 2013-10-31 12:22 -0700
Re: Retrieving possible list for use in a subsequent INSERT Nick the Gr33k <nikos.gr33k@gmail.com> - 2013-10-31 23:54 +0200
Re: Retrieving possible list for use in a subsequent INSERT Nick the Gr33k <nikos.gr33k@gmail.com> - 2013-11-01 00:24 +0200
Re: Retrieving possible list for use in a subsequent INSERT Nick the Gr33k <nikos.gr33k@gmail.com> - 2013-11-01 17:04 +0200
Re: Retrieving possible list for use in a subsequent INSERT Nick the Gr33k <nikos.gr33k@gmail.com> - 2013-11-01 17:52 +0200
Re: Retrieving possible list for use in a subsequent INSERT rurpy@yahoo.com - 2013-11-01 19:00 -0700
Re: Retrieving possible list for use in a subsequent INSERT Nick the Gr33k <nikos.gr33k@gmail.com> - 2013-11-02 10:49 +0200
Re: Retrieving possible list for use in a subsequent INSERT Nick the Gr33k <nikos.gr33k@gmail.com> - 2013-11-02 12:57 +0200
Re: Retrieving possible list for use in a subsequent INSERT Lele Gaifax <lele@metapensiero.it> - 2013-11-02 12:32 +0100
Re: Retrieving possible list for use in a subsequent INSERT Dennis Lee Bieber <wlfraed@ix.netcom.com> - 2013-11-02 12:58 -0400
Re: Retrieving possible list for use in a subsequent INSERT Nick the Gr33k <nikos.gr33k@gmail.com> - 2013-11-01 17:25 +0200
Re: Retrieving possible list for use in a subsequent INSERT Joel Goldstick <joel.goldstick@gmail.com> - 2013-11-01 11:56 -0400
Re: Retrieving possible list for use in a subsequent INSERT Nick the Gr33k <nikos.gr33k@gmail.com> - 2013-11-01 18:00 +0200
Re: Retrieving possible list for use in a subsequent INSERT "Paul Simon" <psimon@sonic.net> - 2013-11-01 10:07 -0700
Re: Retrieving possible list for use in a subsequent INSERT Nick the Gr33k <nikos.gr33k@gmail.com> - 2013-11-01 19:35 +0200
Re: Retrieving possible list for use in a subsequent INSERT "Paul Simon" <psimon@sonic.net> - 2013-11-01 12:19 -0700
Re: Retrieving possible list for use in a subsequent INSERT Denis McMahon <denismfmcmahon@gmail.com> - 2013-11-01 19:12 +0000
Re: Retrieving possible list for use in a subsequent INSERT Nick the Gr33k <nikos.gr33k@gmail.com> - 2013-11-02 02:06 +0200
Re: Retrieving possible list for use in a subsequent INSERT Denis McMahon <denismfmcmahon@gmail.com> - 2013-11-02 16:41 +0000
csiph-web