Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.python > #58156 > unrolled thread

Retrieving possible list for use in a subsequent INSERT

Back to article view | Back to comp.lang.python

Contents

  Retrieving possible list for use in a subsequent INSERT Nick the Gr33k <nikos.gr33k@gmail.com> - 2013-10-31 11:24 +0200
    Re: Retrieving possible list for use in a subsequent INSERT Nick the Gr33k <nikos.gr33k@gmail.com> - 2013-10-31 11:32 +0200
      Re: Retrieving possible list for use in a subsequent INSERT Nick the Gr33k <nikos.gr33k@gmail.com> - 2013-10-31 13:19 +0200
        Re: Retrieving possible list for use in a subsequent INSERT Nick the Gr33k <nikos.gr33k@gmail.com> - 2013-10-31 18:49 +0200
          Re: Retrieving possible list for use in a subsequent INSERT Christian Gollwitzer <auriocus@gmx.de> - 2013-10-31 19:05 +0100
      Re: Retrieving possible list for use in a subsequent INSERT rurpy@yahoo.com - 2013-10-31 12:22 -0700
        Re: Retrieving possible list for use in a subsequent INSERT Nick the Gr33k <nikos.gr33k@gmail.com> - 2013-10-31 23:54 +0200
        Re: Retrieving possible list for use in a subsequent INSERT Nick the Gr33k <nikos.gr33k@gmail.com> - 2013-11-01 00:24 +0200
          Re: Retrieving possible list for use in a subsequent INSERT Nick the Gr33k <nikos.gr33k@gmail.com> - 2013-11-01 17:04 +0200
            Re: Retrieving possible list for use in a subsequent INSERT Nick the Gr33k <nikos.gr33k@gmail.com> - 2013-11-01 17:52 +0200
            Re: Retrieving possible list for use in a subsequent INSERT rurpy@yahoo.com - 2013-11-01 19:00 -0700
              Re: Retrieving possible list for use in a subsequent INSERT Nick the Gr33k <nikos.gr33k@gmail.com> - 2013-11-02 10:49 +0200
                Re: Retrieving possible list for use in a subsequent INSERT Nick the Gr33k <nikos.gr33k@gmail.com> - 2013-11-02 12:57 +0200
                Re: Retrieving possible list for use in a subsequent INSERT Lele Gaifax <lele@metapensiero.it> - 2013-11-02 12:32 +0100
          Re: Retrieving possible list for use in a subsequent INSERT Dennis Lee Bieber <wlfraed@ix.netcom.com> - 2013-11-02 12:58 -0400
        Re: Retrieving possible list for use in a subsequent INSERT Nick the Gr33k <nikos.gr33k@gmail.com> - 2013-11-01 17:25 +0200
          Re: Retrieving possible list for use in a subsequent INSERT Joel Goldstick <joel.goldstick@gmail.com> - 2013-11-01 11:56 -0400
            Re: Retrieving possible list for use in a subsequent INSERT Nick the Gr33k <nikos.gr33k@gmail.com> - 2013-11-01 18:00 +0200
            Re: Retrieving possible list for use in a subsequent INSERT "Paul Simon" <psimon@sonic.net> - 2013-11-01 10:07 -0700
              Re: Retrieving possible list for use in a subsequent INSERT Nick the Gr33k <nikos.gr33k@gmail.com> - 2013-11-01 19:35 +0200
                Re: Retrieving possible list for use in a subsequent INSERT "Paul Simon" <psimon@sonic.net> - 2013-11-01 12:19 -0700
      Re: Retrieving possible list for use in a subsequent INSERT Denis McMahon <denismfmcmahon@gmail.com> - 2013-11-01 19:12 +0000
        Re: Retrieving possible list for use in a subsequent INSERT Nick the Gr33k <nikos.gr33k@gmail.com> - 2013-11-02 02:06 +0200
          Re: Retrieving possible list for use in a subsequent INSERT Denis McMahon <denismfmcmahon@gmail.com> - 2013-11-02 16:41 +0000

Page 1 of 2  [1] 2  Next page →

#58156 — Retrieving possible list for use in a subsequent INSERT

====================================
# if first time for webpage; create new record( primary key is 
automatic, hit is defaulted ), if page exists then update record
cur.execute('''INSERT INTO counters (url) VALUES (%s) ON DUPLICATE KEY 
UPDATE hits = hits + 1''', page )
# get the primary key value of the new added record
cID = cur.lastrowid


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


Hello,
In my attempt to add as an extra column key the possible downloads of 
the current visitor( based on its hostname to identify him) i have wrote 
the above code to try to do so.

I'am afraid something its not working as i expect it to work.
Where is my mistake?

[toc] | [next] | [standalone]

#58158

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

[toc] | [prev] | [next] | [standalone]

#58165

Στις 31/10/2013 11:32 πμ, ο/η Nick the Gr33k έγραψε:
> 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) )


This error happened at the moment when i decided to insert the 
'downloads' column into the 'visitors' database

[toc] | [prev] | [next] | [standalone]

#58193

Στις 31/10/2013 1:19 μμ, ο/η Nick the Gr33k έγραψε:
> Στις 31/10/2013 11:32 πμ, ο/η Nick the Gr33k έγραψε:
>> 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) )
>
>
> This error happened at the moment when i decided to insert the
> 'downloads' column into the 'visitors' database



someone please that is aware of what's wrong....?

[toc] | [prev] | [next] | [standalone]

#58199

Am 31.10.13 17:49, schrieb Nick the Gr33k:
> Στις 31/10/2013 1:19 μμ, ο/η Nick the Gr33k έγραψε:
>
> someone please that is aware of what's wrong....?

Your attitude.

[toc] | [prev] | [next] | [standalone]

#58204

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 ) 

[toc] | [prev] | [next] | [standalone]

#58209

Στις 31/10/2013 9:22 μμ, ο/η rurpy@yahoo.com έγραψε:
>
> 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 )

Hello rurpy! I haven't forget ypu still have to answer you i  the other 
thread for the big explanation you provided, i just didnt had the time yet!

Yes indeed by MySQL's time definition 'downloads' columns is set as:
'varchar(50) not null'

So we have 2 options as you said:

1. Alter the type of 'downloads' colums to soemthign that can hold a list

2. Alter the code to make list beceome an alltogether joined string.

		# 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 = ', '.join( downloads )
		else:
			downloads = None
		
		# 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) )


I can be seen here:   http://superhost.gr/?show=log&page=index.html


But this unfortunate;y do not produce proper results

[toc] | [prev] | [next] | [standalone]

#58211

Στις 31/10/2013 9:22 μμ, ο/η rurpy@yahoo.com έγραψε:
>
> 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)')

[toc] | [prev] | [next] | [standalone]

#58273

Στις 1/11/2013 12:24 πμ, ο/η Nick the Gr33k έγραψε:
> Στις 31/10/2013 9:22 μμ, ο/η rurpy@yahoo.com έγραψε:
>>
>> 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)')


Rurpy can you help me please solve this?
is enum or set column types what needed here as proper columns to store 
'download' list?

[toc] | [prev] | [next] | [standalone]

#58278

Στις 1/11/2013 5:04 μμ, ο/η Nick the Gr33k έγραψε:
> Στις 1/11/2013 12:24 πμ, ο/η Nick the Gr33k έγραψε:
>> Στις 31/10/2013 9:22 μμ, ο/η rurpy@yahoo.com έγραψε:
>>>
>>> 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)')
>
>
> Rurpy can you help me please solve this?
> is enum or set column types what needed here as proper columns to store
> 'download' list?


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.

[toc] | [prev] | [next] | [standalone]

#58309

On Friday, November 1, 2013 9:04:08 AM UTC-6, Ferrous Cranus wrote:
> Rurpy can you help me please solve this?
> is enum or set column types what needed here as proper columns to store 
> 'download' list?

I'd help if I could but I don't use MySql and don't know anything
about its column types.  All I could do it try to read about it
(which I don't have time for right now) and you know more about 
it than me so you can probably figure it out more quickly.

[toc] | [prev] | [next] | [standalone]

#58318

Στις 2/11/2013 4:00 πμ, ο/η rurpy@yahoo.com έγραψε:
> On Friday, November 1, 2013 9:04:08 AM UTC-6, Ferrous Cranus wrote:
>> Rurpy can you help me please solve this?
>> is enum or set column types what needed here as proper columns to store
>> 'download' list?
>
> I'd help if I could but I don't use MySql and don't know anything
> about its column types.  All I could do it try to read about it
> (which I don't have time for right now) and you know more about
> it than me so you can probably figure it out more quickly.
>


Okey here is some improvement:

Splitting the statement in 3 steps to print it before actually executing iy.

=====
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

=====
"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:

=====
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.

[toc] | [prev] | [next] | [standalone]

#58320

You can see the erro as its appearing here:

http://superhost.gr/

Its weird that no single quotes are enclosing the string values though 
and the other bizarre thign is that 'downloads' list is tryign to fiull 
in all the movies.

[toc] | [prev] | [next] | [standalone]

#58322

Nick the Gr33k <nikos.gr33k@gmail.com> writes:

> 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:

    >>> myvalue = "Italy, Europe"
    >>> mysql = "INSERT INTO sometable (theid, thevalue) VALUES (%s, %s)" % (myid, myvalue)
    >>> print(mysql)
    INSERT INTO sometable (theid, thevalue) VALUES (theid, Italy, Europe)

ciao, lele.
--
nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri
real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
lele@metapensiero.it  |                 -- Fortunato Depero, 1929.

[toc] | [prev] | [next] | [standalone]

#58333

On Fri, 01 Nov 2013 00:24:57 +0200, Nick the Gr33k <nikos.gr33k@gmail.com>
declaimed the following:

>???? 31/10/2013 9:22 ??, ?/? rurpy@yahoo.com ??????:
>>
>> 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...

-- 
	Wulfraed                 Dennis Lee Bieber         AF6VN
    wlfraed@ix.netcom.com    HTTP://wlfraed.home.netcom.com/

[toc] | [prev] | [next] | [standalone]

#58276

Στις 31/10/2013 9:22 μμ, ο/η rurpy@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.

[toc] | [prev] | [next] | [standalone]

#58279

On Fri, Nov 1, 2013 at 11:25 AM, Nick the Gr33k <nikos.gr33k@gmail.com> wrote:
> Στις 31/10/2013 9:22 μμ, ο/η rurpy@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

-- 
Joel Goldstick
http://joelgoldstick.com

[toc] | [prev] | [next] | [standalone]

#58280

Στις 1/11/2013 5:56 μμ, ο/η Joel Goldstick έγραψε:
> On Fri, Nov 1, 2013 at 11:25 AM, Nick the Gr33k <nikos.gr33k@gmail.com> wrote:
>> Στις 31/10/2013 9:22 μμ, ο/η rurpy@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 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.

[toc] | [prev] | [next] | [standalone]

#58282

"Joel Goldstick" <joel.goldstick@gmail.com> wrote in message
news:mailman.1935.1383321401.18130.python-list@python.org...
On Fri, Nov 1, 2013 at 11:25 AM, Nick the Gr33k <nikos.gr33k@gmail.com>
wrote:
> ???? 31/10/2013 9:22 ??, ?/? rurpy@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


-- 
Joel Goldstick
http://joelgoldstick.com

He doesn't <need> a many to many table, although that would put the schema
into a classic normal form.  Yes, there will be duplicated data.  Sometimes
de-normalizing a schema may make things simpler and easier to use for
someone not used to database work.  I would also use a many to many table
being familiar with normal forms but it is not a neccessity.

Paul Simon

[toc] | [prev] | [next] | [standalone]

#58283

Στις 1/11/2013 7:07 μμ, ο/η Paul Simon έγραψε:

> 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 already answered to that in my previous post, this answer was Joel's 
there was no need to retype it since i i saw it and responded to it.

[toc] | [prev] | [next] | [standalone]

Page 1 of 2  [1] 2  Next page →

Back to top | Article view | comp.lang.python

csiph-web