Retrieving possible list for use in a subsequent INSERT

[Lele Gaifax]

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)

It was suggested *several* times but I'll reiterate: do not use Python
iterpolation to pass parameters to your SQL statements, or you sooner or
later will hit this kind of problems.

To be clear:
INSERT INTO sometable (theid, thevalue) VALUES (theid, Italy, Europe)

ciao, lele.

 

[Denis McMahon]

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

What colum is able to handle this list?

A python list is a python datatype. mysql has no equivalent data type to
a python list. You need to convert your python list into a data element
or elements that mysql understands.

The way I would do it would be to use a table for downloads list where
each download was linked to the visitor who had downloaded it.

I'm sure all the future users of your torrent search website will be
pleased to know just how much unnecessary data you are attempting to
capture about their torrenting activities. When the CIA and the FBI
persuade the greek government to let them impound your servers, all your
users worldwide will have their torrenting history laid out in full,
which I'm sure will please anyone who wishes to litigate against them.

Note also that Greece is part of the EU and that makes your website
subject to EU data protection requirements. You have to tell your users
what data you are collecting and storing, and why you are collecting and
storing it, and you must destroy the data once it is no longer needed.

As an observation, if you are simply providing a torrent search engine,
you do not need to log or record anything at all.

 

[Dennis Lee Bieber]

???? 31/10/2013 9:22 ??, ?/? (e-mail address removed) ??????:

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 )

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('????? ?????'),
foreign key (counterID) references counters(ID),
unique index (visits)
)ENGINE = MYISAM;

Decided to declare downlods as SET column type.
and maintain this:

downloads = []
if data:
for torrent in data:
downloads.append( torrent )


but error still is:

[Thu Oct 31 22:24:41 2013] [error] [client 46.198.103.93]
pymysql.err.InternalError: (1241, 'Operand should contain 1 column(s)')

MySQL SET data type requires: 1) that you PREDEFINE ALL POSSIBLE values
in the table definition, and 2) that the data inserted into that field is a
comma separated STRING. Not a list, not a repr() of a list. What actually
gets saved in the database is a bitmap of the position of the values
defined.

For an ad hoc list of data, the proper method -- for any relational
database requires normalization. That is, you split the repeating group
(the list) out into a second table, with one entry per row, and a foreign
key linking back to the original table. But, since you don't have a primary
key on your data, we'd have to add that first...