Re: Retrieving possible list for use in a subsequent INSERT

Nick the Gr33k Fri, 01 Nov 2013 09:07:45 -0700

Στις 1/11/2013 5:56 μμ, ο/η Joel Goldstick έγραψε:

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

I can create another table for filenames and use a many to manyrelationship between them because many movies can be downloaded by avisitor and many visitors can download a movie.

That could work, but i wish to refrain from creating another mysqltabale just to store a couple of movies the visitor could or could notdownload.

Just a mysql column table that will be able to store a list(movies thevisitor selected) should do.

--
https://mail.python.org/mailman/listinfo/python-list

Re: Retrieving possible list for use in a subsequent INSERT

Reply via email to