Help me with this code PLEASE

[Nick the Gr33k]

======================================
# fetch those columns that act as lists but are stored as strings
cur.execute('''SELECT refs, visits, downloads FROM visitors WHERE
counterID = %s and host = %s''', (cID, host) )
data = cur.fetchone()

ref = visit = download = []
if cur.rowcount:
# unpack data into variables
(ref, visit, download) = data

# retrieve long strings and convert them into lists respectively
ref = ref.split()
visit = visit.split()
download = download.split()
else:
# initiate these values
ref = ref
visit = lastvisit
download = ''

refs = visits = downloads = []
# add current values to each list respectively
refs.append( ref )
visits.append( visit )
downloads.append( download )

# convert lists back to longstrings
refs = ', '.join( refs )
visits = ', '.join( visits )
downloads = ', '.join( downloads )

# save this visit as an 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)
ON DUPLICATE KEY UPDATE refs = %s, visits = %s, hits = hits + 1,
downloads = %s''',
(cID, refs, host, city, useros, browser, visits, downloads, refs,
visits, downloads) )
=============================================

IAM STRUGGLING WITH IT 2 DAYS NOW AND I CANNOT GET IT TO WORK.

ALL I WANT IT TO DO IS JUST

1. RETRIEVE 3 COLUMNS THAT CONSIST OF 3 LONG STRINGS
2. CONVERT LONG STRINGS TO LISTS
3. ADD SOME CURRENT VALUES TO THOSE LISTS
4. CONVERT FROM LISTS TO LONG STRINGS SO I CAN STORE SUCCESSFULLY LIST
PYTHON DATATYPE TO MYSQL SCALAR STRING.

EVERYHTIGN I TRIED FAILED.

 

[Antoon Pardon]

=============================================

IAM STRUGGLING WITH IT 2 DAYS NOW AND I CANNOT GET IT TO WORK.

ALL I WANT IT TO DO IS JUST

1. RETRIEVE 3 COLUMNS THAT CONSIST OF 3 LONG STRINGS
2. CONVERT LONG STRINGS TO LISTS
3. ADD SOME CURRENT VALUES TO THOSE LISTS
4. CONVERT FROM LISTS TO LONG STRINGS SO I CAN STORE SUCCESSFULLY LIST PYTHON DATATYPE TO MYSQL SCALAR STRING.

EVERYHTIGN I TRIED FAILED.

Don't start a new thread, where you just ask the same question, you already asked before.
The answer will not be different just because you started a new thread.

Did you already read the documentation of fetchone?

 

[mm0fmf]

EVERYHTIGN I TRIED FAILED.

Maybe try some of the advice you have been given instead?

 

[Steven D'Aprano]

ALL I WANT IT TO DO IS JUST

1. RETRIEVE 3 COLUMNS THAT CONSIST OF 3 LONG STRINGS 2. CONVERT LONG
STRINGS TO LISTS
3. ADD SOME CURRENT VALUES TO THOSE LISTS 4. CONVERT FROM LISTS TO LONG
STRINGS SO I CAN STORE SUCCESSFULLY LIST PYTHON DATATYPE TO MYSQL SCALAR
STRING.

You cannot do this with Python. You should use a better language more
suited to you. Try this:

http://php.net/manual/en/tutorial.php


I hope that you will be much happier with this, since you are struggling
to get Python to work the way you want it to.

 

[Antoon Pardon]

Op 05-11-13 18:41, Steven D'Aprano schreef:

You cannot do this with Python. You should use a better language more
suited to you. Try this:

http://php.net/manual/en/tutorial.php


I hope that you will be much happier with this, since you are struggling
to get Python to work the way you want it to.

Steve, what went on in your mind? If Nikos follows you advise he will
probably come here with all kinds of php questions.

 

[Nick the Gr33k]

Στις 5/11/2013 7:41 μμ, ο/η Steven D'Aprano έγÏαψε:

You cannot do this with Python. You should use a better language more
suited to you. Try this:

http://php.net/manual/en/tutorial.php


I hope that you will be much happier with this, since you are struggling
to get Python to work the way you want it to.

--
Steven, i want switch to php, i uses to start with php, then perl and
now i ended up with python.

All this problem arises because MySQL's hasn't got a datatype able to
store an array of elements, a list.

Is there some other database, sql connector i can use except 'pymysql'
that supports a "collection" record type?

postgresql or somethign similar?
If it does i will switch to that and avoid these tedius convertions from
long strings => list and backwise.

Somehting that can just take Python's datatypes 'list' or 'tuple' or
'dict' as tehy are and just store them into the database convertionless.

 

[Denis McMahon]

IAM STRUGGLING WITH IT 2 DAYS NOW AND I CANNOT GET IT TO WORK.

Try starting with something simple. The following is a step by step guide
to working out how you need to do this. Follow all the steps. Do not skip
any steps. Each stage builds on the previous code.

Write python code to create a list of strings and print out the elements
of the list. This just needs to generate a list of n entries of m
characters in length of random character data.

Write python code to concatenate a list of strings into some longer
string using a separator and print out the long string.

Now add some python code to split the long string into a list based on
the separator, and print out the list elements.

Once you have this code working, make a test table with a single string
column and an integer index field in a test database.

Now this is starting to push the boundaries of your abilities, but write
code to create two lists, and store them with indexes 1 and 2
respectively.

Now, and this is really really going to tax you, write some more code
that will first retrieve the string for index 1, convert it back into a
list, and display the list contents, then do the same for list 2, then
retrieve all the data from the db and reproduce each list in turn.

Then you might be ready to try coding what you're trying to code.

 

[Nick the Gr33k]

Στις 5/11/2013 8:02 μμ, ο/η Denis McMahon έγÏαψε:

Try starting with something simple. The following is a step by step guide
to working out how you need to do this. Follow all the steps. Do not skip
any steps. Each stage builds on the previous code.

Write python code to create a list of strings and print out the elements
of the list. This just needs to generate a list of n entries of m
characters in length of random character data.

Write python code to concatenate a list of strings into some longer
string using a separator and print out the long string.

Now add some python code to split the long string into a list based on
the separator, and print out the list elements.

Once you have this code working, make a test table with a single string
column and an integer index field in a test database.

Now this is starting to push the boundaries of your abilities, but write
code to create two lists, and store them with indexes 1 and 2
respectively.

Now, and this is really really going to tax you, write some more code
that will first retrieve the string for index 1, convert it back into a
list, and display the list contents, then do the same for list 2, then
retrieve all the data from the db and reproduce each list in turn.

Then you might be ready to try coding what you're trying to code.

--
Denis, i have already provided my code trying to do what i need and i
need some commendation on how to make it work.

O even better an rdbms than allows complex data such as tuples, lists,
dicts to be saved into the db as they are so i dont have to cobvet back
and forth each time.

 

[Tobiah]

All this problem arises because MySQL's hasn't got a datatype able to store an array of elements, a list.

Um, yes it does. It's called a table.

 

[Joel Goldstick]

All this problem arises because MySQL's hasn't got a datatype able to store
an array of elements, a list.

Um, yes it does. It's called a table.

Perhaps we are splitting hairs, but a relational database will let you
stuff any text you want in a text field, but First Normal Form is a
necessary (and not complete) requirement (from wikipedia)

First normal form (1NF) is a property of a relation in a relational
database. A relation is in first normal form if the domain of each
attribute contains only atomic values, and the value of each attribute
contains only a single value from that domain.[1]

If you don't have a single thing in a field, you can't search or sort
it or filter by its value with sql. Since those are very important
properties of sql, not using 1nf is similar to tossing a whole
warehouse of stuff in a warehouse without any sort of organization for
where different things are to be put, and therefore, where they can be
found.

If you don't have first normal form data, you are misusing a
relational database. If you don't want to learn about relational
databases and what they can do to help you construct software that
solves interesting problems, then perhaps you are not curious enough
to ever become competent in the field.

That said, sql is a different beast than is python or php or any
procedural (or oops) language. Just like writing HTML and CSS is
something that people who write computer programs for the web might
do, its a total shift in thinking from writing python.

There is no rule that a person must be a computer programmer, but if a
person wants to be a competent and successful computer programmer, one
must learn from the evolving understanding of the last 50 years or so.
Its a craft of understanding how to divide impossibly large problems
into understandable pieces -- using the best tools for each piece.
You may say this is just one person's opinion -- but then again, I'm
not the one screaming in all caps about the same several problems over
and over for the last year or more on this list. The results show no
more understanding or growth in skills, and an attitude that never
shows a glimmer of interest in learning.

 

[Denis McMahon]

Denis, i have already provided my code trying to do what i need and i
need some commendation on how to make it work.

Nick, you're obviously trying to code way above your abilities.

If you want me to write your code, you will have to pay my day rate, and
you can't afford it. If you could afford it, you'd be paying someone
competent already.

Here, have a code example, this works, although the formatting might get
broken. The database "ntg1" has a single table called str_idx defined as
follows:

create table idx_str ( idx int primary key, str varchar[1024] );

#!/usr/bin/python

import random
import sqlite3

def make_list( length ):
l = []
if ( length < 1 ):
return l
for count in range( 0, length ):
s = '';
for i in range( 1, random.randrange( 4, 12 ) ):
c = chr( random.randrange( 97, 123 ) )
s += c
l.append( s )
return l

def list_to_string( l ):
return "|".join( l )

def string_to_list( s ):
return s.split( "|" )

l1 = make_list( 10 )
print "l1 -> ", l1
s = list_to_string( l1 )
print "s -> ", s
l2 = string_to_list( s )
print "l2 -> ", l2
print "l1 == l2 -> ", l2 == l1

l2 = make_list( 10 )
l3 = make_list( 10 )
l4 = make_list( 10 )

print "Original Lists"
print "l1 -> ", l1
print "l2 -> ", l2
print "l3 -> ", l3
print "l4 -> ", l4

conn = sqlite3.connect( "ntg1" )
cur = conn.cursor()
cur.execute( "delete from idx_str where idx is not null" )
cur.execute(
"insert into idx_str values ( 1, '{0}' )".format(
list_to_string( l1 ) ) )
cur.execute(
"insert into idx_str values ( 2, '{0}' )".format(
list_to_string( l2 ) ) )
cur.execute(
"insert into idx_str values ( 3, '{0}' )".format(
list_to_string( l3 ) ) )
cur.execute(
"insert into idx_str values ( 4, '{0}' )".format(
list_to_string( l4 ) ) )
conn.commit()
conn.close()

print "Lists now in DB"
print "Reading 1 record at a time"

conn2 = sqlite3.connect( "ntg1" )
cur2 = conn2.cursor()

cur2.execute( "select * from idx_str where idx = 1" );
row = cur2.fetchone()
print "stored 1 -> ", row[ 1 ]
print "l1 -> ", string_to_list( row[ 1 ] )

cur2.execute( "select * from idx_str where idx = 2" );
row = cur2.fetchone()
print "stored 2 -> ", row[ 1 ]
print "l2 -> ", string_to_list( row[ 1 ] )

cur2.execute( "select * from idx_str where idx = 3" );
row = cur2.fetchone()
print "stored 3 -> ", row[ 1 ]
print "l3 -> ", string_to_list( row[ 1 ] )

cur2.execute( "select * from idx_str where idx = 4" );
row = cur2.fetchone()
print "stored 4 -> ", row[ 1 ]
print "l4 -> ", string_to_list( row[ 1 ] )

conn2.close()

print "Reading all records at once"

conn3 = sqlite3.connect( "ntg1" )

cur3 = conn3.cursor()

cur3.execute( "select * from idx_str" );

row = cur3.fetchone()

while not row == None:

print "stored ", row[ 0 ], " -> ", row[ 1 ]
print "list ", row[ 0 ], " -> ", string_to_list( row[ 1 ] )
row = cur3.fetchone()

conn3.close()

One thing you haven't considered, what happens if a user has so many
downloads in his list that the converted list doesn't fit in the declared
string column width in your database?

 

[John Gordon]

IAM STRUGGLING WITH IT 2 DAYS NOW AND I CANNOT GET IT TO WORK.

ALL I WANT IT TO DO IS JUST

1. RETRIEVE 3 COLUMNS THAT CONSIST OF 3 LONG STRINGS
2. CONVERT LONG STRINGS TO LISTS
3. ADD SOME CURRENT VALUES TO THOSE LISTS
4. CONVERT FROM LISTS TO LONG STRINGS SO I CAN STORE SUCCESSFULLY LIST
PYTHON DATATYPE TO MYSQL SCALAR STRING.

EVERYHTIGN I TRIED FAILED.

How did it fail?

Error message?
No results at all?
Different results than you wanted? If so, how did they differ?

 

[Nick the Gr33k]

Στις 5/11/2013 10:19 μμ, ο/η John Gordon έγÏαψε:

How did it fail?

Error message?
No results at all?
Different results than you wanted? If so, how did they differ?

I know i'm close to solution, i can feel it but i have some issues.
The code we arr discussing is the following:


=================================================================================================================
# ~ DATABASE INSERTS ~
=================================================================================================================
if cookieID != 'nikos' and ( os.path.exists( path + page ) or
os.path.exists( cgi_path + page ) ) and re.search(
r'(amazon|google|proxy|cloud|reverse|fetch|msn|who|spider|crawl|ping)',
host ) is None:

try:
# 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 )
cID = cur.lastrowid

# fetch those columns that act as lists but are stored as strings
cur.execute('''SELECT refs, visits, downloads FROM visitors WHERE
counterID = %s and host = %s''', (cID, host) )
data = cur.fetchone()

ref = visit = download = []
if cur.rowcount:
# unpack data into variables
(ref, visit, download) = data

# retrieve long strings and convert them into lists respectively
ref = ref.split()
visit = visit.split()
download = download.split()
else:
# initiate these values
ref = ref
visit = lastvisit
download = ''

refs = visits = downloads = []
# add current values to each list respectively
refs.append( ref )
visits.append( visit )
downloads.append( download )

# convert lists back to longstrings
refs = ', '.join( refs )
visits = ', '.join( visits )
downloads = ', '.join( downloads )

# save this visit as an 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)
ON DUPLICATE KEY UPDATE refs = %s, visits = %s, hits = hits + 1,
downloads = %s''',
(cID, refs, host, city, useros, browser, visits, downloads, refs,
visits, downloads) )

con.commit()
except pymysql.ProgrammingError as e:
print( repr(e) )
con.rollback()
sys.exit(0)
===============================================


[Tue Nov 05 23:21:52 2013] [error] [client 176.92.96.218] File
"/home/nikos/public_html/cgi-bin/metrites.py", line 274, in <module>
[Tue Nov 05 23:21:52 2013] [error] [client 176.92.96.218] visit =
visit.split()
[Tue Nov 05 23:21:52 2013] [error] [client 176.92.96.218]
AttributeError: 'NoneType' object has no attribute 'split'

 

[Mark Lawrence]

Dear John,

This approach has already been tried by Dave Angel, Steven D'Aprano and
Chris Angelico amongst others and has failed dismally so why should you
be any different?

I've taken a different approach. I've put the contract out to tender and
hereby give you the winners
http://www.mudefordwoodcommunitycentre.co.uk/playgroup-and-tiny-tots/

Part of the rationale is that they're literally 100 yards from my front
door so communicating with them effectively should be extremely easy.
This will be particularly useful when it comes down to debugging such
difficult issues as "> TypeError: 'NoneType' object is not iterable".

I'm looking forward to working in partnership with them and am convinced
that within a few days all of Nikos' problems will have been solved.

Oh dear it looks as if we might have to renegotiate the contract as I
understand that we've an almost intractable problem to solve it's
"AttributeError: 'NoneType' object has no attribute 'split'".

 

[Chris Angelico]

I've taken a different approach. I've put the contract out to tender and
hereby give you the winners
http://www.mudefordwoodcommunitycentre.co.uk/playgroup-and-tiny-tots/

Sounds good! But I don't see a list of their technologies - do they
use open source tools like Python, or is it proprietary stuff like
Fisher-Price(tm) "My First Website"? The latter is probably sufficient
for these tasks, but I would hope that your contractors are able to
master real-world problems too.

Nikos, once again you are getting yourself into a tizz over this,
because the problem is urgent to you. You need to stop programming
professionally and make it a hobby, so that you remove the time
pressure. You cope badly with pressure, so why torture yourself? Get
into a completely different industry for your day job, and do all your
programming in the evenings. You'll find lots of things easier and
less stressful.

ChrisA

 

[John Gordon]

# fetch those columns that act as lists but are stored as strings
cur.execute('''SELECT refs, visits, downloads FROM visitors WHERE
counterID = %s and host = %s''', (cID, host) )
data = cur.fetchone()

if cur.rowcount:
# unpack data into variables
(ref, visit, download) = data

visit = visit.split()

[Tue Nov 05 23:21:52 2013] [error] [client 176.92.96.218] visit =
visit.split()
[Tue Nov 05 23:21:52 2013] [error] [client 176.92.96.218]
AttributeError: 'NoneType' object has no attribute 'split'

It appears that in the row you're fetching from the visitors table, the
'visits' column is NULL.

Your next step is track down why.

 

[Piet van Oostrum]

IAM STRUGGLING WITH IT 2 DAYS NOW AND I CANNOT GET IT TO WORK.

ALL I WANT IT TO DO IS JUST

1. RETRIEVE 3 COLUMNS THAT CONSIST OF 3 LONG STRINGS
2. CONVERT LONG STRINGS TO LISTS
3. ADD SOME CURRENT VALUES TO THOSE LISTS
4. CONVERT FROM LISTS TO LONG STRINGS SO I CAN STORE SUCCESSFULLY LIST
PYTHON DATATYPE TO MYSQL SCALAR STRING.

EVERYHTIGN I TRIED FAILED.

Then why don't you use the simple solution: use a relational database to store the data?

 

[Nick the Gr33k]

Στις 6/11/2013 12:06 πμ, ο/η John Gordon έγÏαψε:

# fetch those columns that act as lists but are stored as strings
cur.execute('''SELECT refs, visits, downloads FROM visitors WHERE
counterID = %s and host = %s''', (cID, host) )
data = cur.fetchone()

if cur.rowcount:
# unpack data into variables
(ref, visit, download) = data

visit = visit.split()

[Tue Nov 05 23:21:52 2013] [error] [client 176.92.96.218] visit =
visit.split()
[Tue Nov 05 23:21:52 2013] [error] [client 176.92.96.218]
AttributeError: 'NoneType' object has no attribute 'split'

It appears that in the row you're fetching from the visitors table, the
'visits' column is NULL.

Your next step is track down why.

Indeed.

The code i provided only worked once before it failed and managed to
store this:


counterID,host,refs,city,userOS,browser,visits,hits,download
-------------------------------------------------------------
1, 176-92-96-218.adsl.cyta.gr, Europe/Athens, Windows, Chrome,
0000-00-00 00:00:00, 1, ''

'visit's column is full of zeroes. Perhaps this is considered as Null
for Python?

But ref is also null, why the code didn't complain for 'ref' which is
retrieved just before 'visits'?

How, i eman what si the way to investigate this further?

 

[Cameron Simpson]

O even better an rdbms than allows complex data such as tuples,
lists, dicts to be saved into the db as they are so i dont have to
cobvet back and forth each time.

If you're just using the db for storage or adhoc and arbitrary
python objects (and not querying the stored values via SQL - eg
WHERE), then: make a column of type BLOB, convert Python values to
bytes using pickle, store. And of course the reverse.

It is not a great use of an RDB, but it seems to adhere to what you ask.

 

[Nick the Gr33k]

Στις 6/11/2013 12:15 πμ, ο/η Piet van Oostrum έγÏαψε:

Then why don't you use the simple solution: use a relational database to store the data?

--
How you mean?

Up until now i was using for years MySQL.
Now i realizes i just cannot store lists into it's columns because it
does not support a collection datatype.

perhaps its time to use postgresql so to avoid these coversions?

does postgresql support "collection" columns?

and is its synta