#1
  1. Contributing User
    Devshed Beginner (1000 - 1499 posts)

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

    Question I would like to know if there's a way to store a list into a column of 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) )
    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 Nik; November 1st, 2013 at 11:23 AM.
    What is now proved was once only imagined!
  2. #2
  3. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,730
    Rep Power
    1959
    SET type is only for a predefined list.
    According to the normalization rule, one field shall only have one value. In you case it means it will only accept the value "None Yet".
    Can't explain why you get this error.

    In you description, you have described what is called a MANY-TO-MANY relation, as the visitor can have 1 or more torrent selected and a torrent can be used by 1 or more visitors. (0 selection would not have any record saved)

    What you should do is create a third table which can be called 'VisitorsDownloads' or something like that.
    In this table you will then store the ID of the torrent/file and the visitor. You can also add a date if you want to keep track of when a download occurs.
  4. #3
  5. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2003
    Location
    Thessaloniki
    Posts
    1,285
    Rep Power
    13
    But i want to avoid this.

    I wish not to create an extra mysql tabale but just have a columns that can store zero or multiple values within it.

    Can this be done?
    What is now proved was once only imagined!
  6. #4
  7. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,730
    Rep Power
    1959
    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.

    Any specific reason you don't want to create an extra table?
    You might already know this, but:
    - If you only wants to show what the visitor itself had downloaded, then it might be fine. Although you wont have any "direct link" to the file that had been downloaded.
    - If you ever wants to perform any selection that go across the visitors, the query for that will - at best - be very ineffective; if not impossible.
  8. #5
  9. Contributing User
    Devshed Beginner (1000 - 1499 posts)

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

    Question


    Would you please show me a way to print the pure sql statement that will be passed to the mysql engine before it does?
    What is now proved was once only imagined!
  10. #6
  11. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2003
    Location
    Thessaloniki
    Posts
    1,285
    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 04:47 AM.
    What is now proved was once only imagined!
  12. #7
  13. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,730
    Rep Power
    1959
    If you want to add something to a text field, you have to put ' ' around the text-value.
  14. #8
  15. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2003
    Location
    Thessaloniki
    Posts
    1,285
    Rep Power
    13
    Trying to add the current filename into the existent 'downloads' column
    Somehow i don't think i just use the plus sign into an existing column.
    We don't try to add numbers here but add an extra string to an already existing array of strings(list).

    ======================================================
    # update specific torrent's download counter
    cur.execute('''UPDATE files SET hits = hits + 1, host = %s, city = %s, lastvisit = %s WHERE torrent = %s''', (host, city, lastvisit, filename) )

    # update specific visitor's download record
    cur.execute('''UPDATE visitors SET downloads = downloads + %s WHERE host = %s''', (filename, host) )
    ======================================================




    Retrieval time for displaying purposes:
    ======================================================
    downloads = []
    if cur.rowcount:
    for torrent in data:
    downloads = ', '.join( torrent )
    else:
    downloads = 'Κανένα κατέβασμα ταινίας'

    # add this visitor entry into database (visits is 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 this correct, personally i would just prefer:

    for torrent in data:
    downloads.append( torrent )

    Can you tell me the differenced on these two ways?

    Aren't the result of both of them a list?
    What is now proved was once only imagined!
  16. #9
  17. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,730
    Rep Power
    1959
    I'm not that much into Python itself, but this part looks wrong:
    Code:
    for torrent in data:
    downloads = ', '.join( torrent )
    From my understanding, join takes a list of element and make a separated list.
    This mean you should be able to do it like this:
    Code:
    if cur.rowcount:
    downloads = ', '.join(data)
    else:
    downloads = 'Κανένα κατέβασμα ταινίας'
    But you haven't specified how you got the data part.
    append is a function to add a new element to the list.

    One question though is how and where to control several download of same file.

    This is why it is easier to work with a third table, as you then can make SQL do most of the work for you, without the need of direct string/list manipulation.
  18. #10
  19. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2003
    Location
    Thessaloniki
    Posts
    1,285
    Rep Power
    13
    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) )
    This works bit questios thas arises is what is its difference compare to:

    downloads.append( torrent )

    Are both these statements create a list?
    But in the latter we get the famous:
    pymysql.err.InternalError: (1241, 'Operand should contain 1 column(s)')

    while in the join() we arent getting this.

    I just want a mysql column type that can be eligible to store an array of elements, a list that is, no need for having a seperate extra table for that if we can have a column that can store a list of values.
    What is now proved was once only imagined!

IMN logo majestic logo threadwatch logo seochat tools logo