"Joel Goldstick" <joel.goldst...@gmail.com> wrote in message news:mailman.1935.1383321401.18130.python-l...@python.org... On Fri, Nov 1, 2013 at 11:25 AM, Nick the Gr33k <nikos.gr...@gmail.com> wrote: > ???? 31/10/2013 9:22 ??, ?/? ru...@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 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 -- https://mail.python.org/mailman/listinfo/python-list