Retrieving possible list for use in a subsequent INSERT

Retrieving possible list for use in a subsequent INSERT

Postby NikosGr » Thu Oct 31, 2013 12:58 pm

Code: Select all
# 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 = ['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?

The error seen form error log is:

Code: Select all
[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
NikosGr
 
Posts: 48
Joined: Thu Mar 28, 2013 6:31 pm
Location: Thessaloniki

Re: Retrieving possible list for use in a subsequent INSERT

Postby ochichinyezaboombwa » Thu Oct 31, 2013 8:33 pm

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

(Depending on your columns types, this might require additional '' around some of %s).
ochichinyezaboombwa
 
Posts: 200
Joined: Tue Jun 04, 2013 7:53 pm

Re: Retrieving possible list for use in a subsequent INSERT

Postby NikosGr » Thu Oct 31, 2013 9:18 pm

I noticed that you have altered ',' with '%'.
What is the logic behind it?

Now instead for a blank screen i'm receiving this error:

Code: Select all
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, Direct Hit, 46-198-103-93.adsl.cyta.gr, Europe/Athens, Windows, Ch' at line 1"),)
NikosGr
 
Posts: 48
Joined: Thu Mar 28, 2013 6:31 pm
Location: Thessaloniki

Re: Retrieving possible list for use in a subsequent INSERT

Postby ochichinyezaboombwa » Thu Oct 31, 2013 11:21 pm

I suggest you split your one line into three:

Code: Select all
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)
ochichinyezaboombwa
 
Posts: 200
Joined: Tue Jun 04, 2013 7:53 pm

Re: Retrieving possible list for use in a subsequent INSERT

Postby NikosGr » Fri Nov 01, 2013 4:17 am

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 )
NikosGr
 
Posts: 48
Joined: Thu Mar 28, 2013 6:31 pm
Location: Thessaloniki

Re: Retrieving possible list for use in a subsequent INSERT

Postby NikosGr » Sat Nov 02, 2013 8:38 am

ochichinyezaboombwa wrote:I suggest you split your one line into three:

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


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

Code: Select all
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: Select all
"INSERT INTO visitors (counterID, refs, host, city, useros, browser, visits, downloads) VALUES (1, No Referrer - Direct 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 - Direct Hit, 46-198-103-93.adsl.cyta.gr, Europe/Athens, Windows, C' at line 1"),)
NikosGr
 
Posts: 48
Joined: Thu Mar 28, 2013 6:31 pm
Location: Thessaloniki


Return to Web Development

Who is online

Users browsing this forum: micseydel and 2 guests