How could i store an entire list into a MySQL table

This is the place for queries that don't fit in any of the other categories.

How could i store an entire list into a MySQL table

Postby NikosGr » Fri Nov 01, 2013 3:19 pm

I would like to know if there's a way to store an entire list into a MySQL table.

Code: Select all
      # 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) )


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.
Last edited by Yoriz on Fri Nov 01, 2013 4:20 pm, edited 1 time in total.
Reason: changed title
NikosGr
 
Posts: 48
Joined: Thu Mar 28, 2013 6:31 pm
Location: Thessaloniki

Re: I would like to know if there's a way to store an entire

Postby Yoriz » Fri Nov 01, 2013 3:43 pm

Create another table for filenames and use a many to many relationship between them because many movies can be downloaded by a vistor and many visitors can download a movie.
New Users, Read This
Join the #python-forum IRC channel on irc.freenode.net!
Spam topic disapproval technician
Windows7, Python 2.7.4., WxPython 2.9.5.0., some Python 3.3
User avatar
Yoriz
 
Posts: 725
Joined: Fri Feb 08, 2013 1:35 am
Location: UK

Re: I would like to know if there's a way to store an entire

Postby NikosGr » Fri Nov 01, 2013 3:49 pm

That could work,yes, but i refrain from creating another mysql tabale just to store a couple of movies the visitor could or could not download.

Just a mysql column table that will be able to store a list(movies the visitor selected) should do.
NikosGr
 
Posts: 48
Joined: Thu Mar 28, 2013 6:31 pm
Location: Thessaloniki


Return to General Coding Help

Who is online

Users browsing this forum: No registered users and 3 guests