Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2003
    Location
    Thessaloniki
    Posts
    1,280
    Rep Power
    13

    Question Retrieving possible list for use in a subsequent INSERT


    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.
    What is now proved was once only imagined!
  2. #2
  3. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2003
    Location
    Thessaloniki
    Posts
    1,280
    Rep Power
    13
    I can create another table for filenames and use a many to many relationship between them because many movies can be downloaded by a visitor and many visitors can download a movie.

    That could work, but i wish to 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.
    What is now proved was once only imagined!
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    i want to caution you in the strongest terms against storing multiple values into a single column

    unless you can swear up and down that you will never, ever, ever need to search that column for a specific value in it

    that type of search will not be able to use an index, and instead will use a table scan, which means that the query will get slower and slower the more rows there are

    if you're happy with that scenario, then go for it, knock yourself out as they say
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2003
    Location
    Thessaloniki
    Posts
    1,280
    Rep Power
    13
    I just want to:

    1. store multiple values as they arise in the same column(i.e. visitor select another fileanem for download)

    2. just retrive it in general by doing a select *

    I swear that i have no need to search that column for a specific value in it.

    Can you help me write it please?

    No matter what i try i alqsy get the same error:

    pymysql.err.InternalError: (1241, 'Operand should contain 1 column(s)')

    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) )
    What is now proved was once only imagined!
  8. #5
  9. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2003
    Location
    Thessaloniki
    Posts
    1,280
    Rep Power
    13
    I just want to:

    1. Store multiple values as they arise in the same column(i.e. visitor select another fileanem for download)

    2. Just do a simple retrieve it in general by doing a select *

    I swear that i have no need to search that column for a specific value in it.

    Can you help me write it please?

    No matter what i try i alqsy get the same error:

    pymysql.err.InternalError: (1241, 'Operand should contain 1 column(s)')

    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) )
    What is now proved was once only imagined!
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by Nik
    Can you help me write it please?
    not me, man, i don't do python

    the column you are trying to store multiple values in should be VARCHAR(255), or larger if you need it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2003
    Location
    Thessaloniki
    Posts
    1,280
    Rep Power
    13
    The python code is ready for use, its correct as i have it.

    Should't the column type be of ENUM or SET?

    VARCHAR(255) is to store just a string afaik not a list(array of strings)

    Isn't this so!?
    What is now proved was once only imagined!
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by Nik
    Isn't this so!?
    no, it ain't necessarily so

    VARCHAR is for any strings

    a list of file names can be a string

    it would be totally wrong to use ENUM or SET here
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2003
    Location
    Thessaloniki
    Posts
    1,280
    Rep Power
    13
    i tried varachar and the error remains:

    pymysql.err.InternalError: (1241, 'Operand should contain 1 column(s)')
    What is now proved was once only imagined!
  18. #10
  19. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2003
    Location
    Thessaloniki
    Posts
    1,280
    Rep Power
    13
    I wish not to create an extra MySQL table but just have a column that can store zero or multiple values within it.

    Shall i use a specific column type like ENUM or SET ?

    i tried it but this also doenst work.

    # 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) )

    is the baove code correct?

    downloads is a list(array of multiple values) or even of zero values.

    HOW this 'list' is supposed to get stored into the visitors database?

    What colum is able to handle this list?
    What is now proved was once only imagined!
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,436
    Rep Power
    1688
    I don't really do Python either, so could you just confirm what actual line of your code that is happening on - i.e., are we totally sure it's the insert that is at fault?
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  22. #12
  23. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2003
    Location
    Thessaloniki
    Posts
    1,280
    Rep Power
    13
    Yes i'm afraid it is:

    checking the error_log states:

    Code:
    [Sat Nov 02 02:10:38 2013] [error] [client 46.198.103.93] Traceback (most recent call last):
    [Sat Nov 02 02:10:38 2013] [error] [client 46.198.103.93]   File "/home/nikos/public_html/cgi-bin/metrites.py", line 276, in <module>
    [Sat Nov 02 02:10:38 2013] [error] [client 46.198.103.93]     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) )
    [Sat Nov 02 02:10:38 2013] [error] [client 46.198.103.93]   File "/usr/local/bin/python/lib/python3.3/site-packages/pymysql/cursors.py", line 117, in execute
    [Sat Nov 02 02:10:38 2013] [error] [client 46.198.103.93]     self.errorhandler(self, exc, value)
    [Sat Nov 02 02:10:38 2013] [error] [client 46.198.103.93]   File "/usr/local/bin/python/lib/python3.3/site-packages/pymysql/connections.py", line 202, in defaulterrorhandler
    [Sat Nov 02 02:10:38 2013] [error] [client 46.198.103.93]     raise errorclass(errorvalue)
    [Sat Nov 02 02:10:38 2013] [error] [client 46.198.103.93] pymysql.err.InternalError: (1241, 'Operand should contain 1 column(s)')
    [Sat Nov 02 02:10:39 2013] [error] [client 46.198.103.93] File does not exist: /home/nikos/public_html/favicon.ico
    [Sat Nov 02 02:10:39 2013] [error] [client 46.198.103.93] File does not exist: /home/nikos/public_html/404.shtml
    What is now proved was once only imagined!
  24. #13
  25. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    if you would still like to consider this a mysql problem, would you please show the pure sql statement that will be passed to the mysql engine

    not the python code that will be interpreted with runtime values, with all thos snakey %s things

    it makes sense to test your database stategy in pure sql first, before attempting to parameterize it with an application language
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  26. #14
  27. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2003
    Location
    Thessaloniki
    Posts
    1,280
    Rep Power
    13
    Splitting the statement in 3 steps to print it before actually executing iy.

    Code:
    sql = '''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) 
    print repr(sql)
    cur.execute(sql)
    This the real time values trying to be passed into MySQL table in python script's runtime

    Code:
    "INSERT INTO visitors (counterID, refs, host, city, useros, browser, visits, downloads) VALUES (1, Χωρίς Referrer - Άμεσο Hit, 46-198-103-93.adsl.cyta.gr, Europe/Athens, Windows, Chrome, 13-11-02 10:31:29, [('Jobs.2013. WEBRip XViD juggs',), ('Pacific.Rim.2013.720p.BDRip.XviD.AC3-ELiTE',), ('Man of Steel 2013 BRRip XviD AC3-SANTi',), ('Now You See Me EXTENDED 2013 BRRip XviD AC3-SANTi',), ('DAS EXPERIMENT (2001) 720p.BDRip.XVID.AC3',), ('Behind the Candelabra 2013 BDrip XviD AC3',), ('The.Internship.2013.UNRATED.480p.BRRip.Xvid.AC3',), ('Man Of Tai 2013 WEBrip XVID AC3',), ('Star Trek Into Darkness 2013 BRRip XviD AC3-SANTi',), ('ESCAPE PLAN (2013) CAM XViD UNiQUE',)])" 
    
    ProgrammingError(ProgrammingError(1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Referrer - Άμεσο Hit, 46-198-103-93.adsl.cyta.gr, Europe/Athens, Windows, C' at line 1"),)
    The definition of 'visitro's table is as follows:

    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,
      download text not null,
      foreign key (counterID) references counters(ID),
      unique index (visits)
     )ENGINE = MYISAM;
    It is possible to "just" use a VARCHAR or TEXT field and then add anything you want to it, including a comma or semi-colon separated list.
    Last edited by Nik; November 2nd, 2013 at 03:48 AM.
    What is now proved was once only imagined!
  28. #15
  29. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by Nik
    "INSERT INTO visitors (counterID, refs, host, city, useros, browser, visits, downloads) VALUES (1, Χωρίς Referrer - Άμεσο Hit, 46-198-103-93.adsl.cyta.gr, Europe/Athens, Windows, Chrome, 13-11-02 10:31:29...
    strings need to have single quotes around them
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo