Database statements via python but database left intact

  • Thread starter Îίκος Αλεξόπουλος
  • Start date
Î

Îίκος Αλεξόπουλος

Excuse me for asking again today, but i see no error in the following
code, yes no isertion or update happens into the database:


try:
# locate the ID of the page's URL
cur.execute('''SELECT ID FROM counters WHERE url = %s''', page )
data = cur.fetchone() #URL is unique, so should only be one

if not data:
#first time for page; primary key is automatic, hit is defaulted
cur.execute('''INSERT INTO counters (url) VALUES (%s)''', page )
cID = cur.lastrowid #get the primary key value of the new added record
else:
#found the page, save primary key and use it to issue hit UPDATE
cID = data[0]
cur.execute('''UPDATE counters SET hits = hits + 1 WHERE ID = %s''', cID )


When this code runs i check instantly my database via PHPMyAdmin and i
see that it was left intact.
 
Î

Îίκος Αλεξόπουλος

Actually the whole code is this:

#
=================================================================================================================
# DATABASE INSERTS -
#
=================================================================================================================
if cookieID != 1977 and re.search(
r'(msn|gator|amazon|yandex|reverse|who|cloudflare|fetch|barracuda|spider|google|crawl|pingdom)',
host ) is None:

try:
# locate the ID of the page's URL
cur.execute('''SELECT ID FROM counters WHERE url = %s''', page )
data = cur.fetchone() #URL is unique, so should only be one

if not data:
#first time for page; primary key is automatic, hit is defaulted
cur.execute('''INSERT INTO counters (url) VALUES (%s)''', page )
cID = cur.lastrowid #get the primary key value of the new added record
else:
#found the page, save primary key and use it to issue hit UPDATE
cID = data[0]
cur.execute('''UPDATE counters SET hits = hits + 1 WHERE ID = %s''',
cID )


# find the visitor record for the (saved) cID and Cookie
cur.execute('''SELECT * FROM visitors WHERE counterID = %s and
cookieID = %s''', (cID, cookieID) )
data = cur.fetchone() #cookieID is unique

if not data:
# first time visitor on this page, create new record
cur.execute('''INSERT INTO visitors (counterID, cookieID, host, city,
useros, browser, ref, lastvisit) VALUES (%s, %s, %s, %s, %s, %s, %s,
%s)''', (cID, cookieID, host, city, useros, browser, ref, lastvisit) )
else:
# found the page, save its primary key for later use
vID = data[0]
# UPDATE record using retrieved vID
cur.execute('''UPDATE visitors SET host = %s, city = %s, useros = %s,
browser = %s, ref= %s, hits = hits + 1, lastvisit = %s
WHERE counterID = %s and cookieID = %s''', (host, city, useros,
browser, ref, lastvisit, vID, cookieID) )

except pymysql.ProgrammingError as e:
print( repr(e) )
sys.exit(0)

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

If at some point an error is made does that mean that no
update/insertion happens?
PEhats that is why iam seeing no entries at all into my database tables?
 
Î

Îίκος Αλεξόπουλος

Στις 5/10/2013 10:29 μμ, ο/η Zero Piraeus έγÏαψε:
:

Excuse me for asking again today, but i see no error in the
following code, yes no isertion or update happens into the database:

[...]

When this code runs i check instantly my database via PHPMyAdmin and
i see that it was left intact.

Are you sure that you're committing your changes (either by having
autocommit set or using an explicit con.commit() call)?

http://geert.vanderkelen.org/dont-forget-the-commit-in-mysql/


I dont think that is the issue, because up until now i never used commit
and all transaction were successfully were happening.
 
I

Ian Kelly

Óôéò 5/10/2013 10:29 ìì, ï/ç Zero Piraeus Ýãñáøå:
:

Excuse me for asking again today, but i see no error in the
following code, yes no isertion or update happens into the database:

[...]


When this code runs i check instantly my database via PHPMyAdmin and
i see that it was left intact.


Are you sure that you're committing your changes (either by having
autocommit set or using an explicit con.commit() call)?

http://geert.vanderkelen.org/dont-forget-the-commit-in-mysql/



I dont think that is the issue, because up until now i never used commit and
all transaction were successfully were happening.

Well, have you changed anything in your database configuration?
Whether MySQL uses transactions or not depends on which storage engine
is being used. I suggest running a test insert with and without
commit to check whether you actually need it or not.

Also, are you certain that the code is actually being run? Perhaps
the if condition is evaluating as false and the whole block is being
skipped, or perhaps the code is silently exiting for some reason
before it ever gets to this point. Do some debugging to determine
what exactly is being executed.
 
Î

Îίκος Αλεξόπουλος

Στις 5/10/2013 11:31 μμ, ο/η Ian Kelly έγÏαψε:
Well, have you changed anything in your database configuration?
Whether MySQL uses transactions or not depends on which storage engine
is being used. I suggest running a test insert with and without
commit to check whether you actually need it or not.


I cannot beleive it!

I have deleted the database and tables and recreted it.
By default it seems to use the InnoDB Engine which wouldn't let anythign
get inserted/updated.

I then deleted the database recretaed and used at the end fo my create
table statements the:

create table counters
(
ID integer(5) not null auto_increment primary key,
URL varchar(100) not null,
hits integer(5) not null default 1,
unique index (URL)
)ENGINE = MYISAM;

After that all mysql queries executed(inserted/updated) properly!

I neved had though of than an engine type could make so much mess.
MyISAM is the way to go then for my web development?
Why InnoDB failed to execute the queries?
 
N

Ned Batchelder

Στις 5/10/2013 11:31 μμ, ο/η Ian Kelly έγÏαψε:


I cannot beleive it!

I have deleted the database and tables and recreted it.
By default it seems to use the InnoDB Engine which wouldn't let
anythign get inserted/updated.

I then deleted the database recretaed and used at the end fo my create
table statements the:

create table counters
(
ID integer(5) not null auto_increment primary key,
URL varchar(100) not null,
hits integer(5) not null default 1,
unique index (URL)
)ENGINE = MYISAM;

After that all mysql queries executed(inserted/updated) properly!

I neved had though of than an engine type could make so much mess.
MyISAM is the way to go then for my web development?
Why InnoDB failed to execute the queries?

Now is a good time to go read about transactions, and committing, and
the difference between MyISAM and InnoDB. Please don't ask more about
it here.

--Ned.
 
Z

Zero Piraeus

:

I neved had though of than an engine type could make so much mess.
MyISAM is the way to go then for my web development?
Why InnoDB failed to execute the queries?

Because you didn't commit. MyISAM doesn't support transactions, so when
you use it as the engine, your mistake happens to go unpunished.

Note that this is a weakness of MyISAM cancelling out a failure in your
code; it does *not* mean that MyISAM is better suited to your task.

By the way, Ned's right. At this point, this is no longer a Python
issue, and is off-topic for discussion here.

-[]z.
 
C

Chris Angelico

Now is a good time to go read about transactions, and committing, and the
difference between MyISAM and InnoDB. Please don't ask more about it here.

It's because of threads like this that I would really like Python to
nudge people towards something stronger than MySQL. Would it kill
Python to incorporate PostgreSQL bindings automatically?

ChrisA
 
C

Chris Angelico

The Python bindings for MySQL or PostgreSQL, or even SQLite, are tied to
extension libraries for the specific database engine.

With SQLite this is not a problem for Python's release management,
because Python's release includes the entire SQLite database engine.
That code is quite small, so this is deemed a good trade.

With a separately-installed, far more complex database engine like MySQL
or PostgreSQL, the Python bindings will only work if they are compiled
against the correct client library.

Hmm. I see what you mean. Of course, that doesn't bind Python to a
specific server version, as one version of the client can talk to any
earlier and many later versions of server, but it is an issue.

It'd probably be unreasonable to package libpq with Windows
installations, but with Linux builds, it should be easy enough to link
against whatever libpq happens to be around. If that's unsuited to
your server version, it's going to be broken for any other libpq-based
apps too. (With package managers like apt, same thing - link against
whatever version can be retrieved easily.)

The alternative is a pure-Python implementation of the PostgreSQL wire
protocol. That would most likely be smaller (if the problem is the
size cost of incorporating all of libpq), but would tie Python's pgsql
module to a specific protocol version (which doesn't change very
often). I don't know if one exists already or not, but it ought to be
possible to port the Pike module [1] to Python, if GPL/LGPL/MPL is
compatible with the Python licensing.

ChrisA

[1] http://pike.lysator.liu.se/generated/manual/modref/ex/predef_3A_3A/Sql/pgsql.html
 
R

rusi

So, instead of this, maybe we should work on getting psycopg2 to the
top result on Googling “python sql”, or even “python mysql” with an
anti-MySQL ad? (like vim was doing some time ago on Googling “emacs”)

Do you have any accessible data about this?
Reasons I ask:
1. The decreasing popularity of emacs wrt vi seems out of proportion to theactual functionality
2. The downward emacs-curve is all the more striking considering the reverse situation some 15-20 years ago
 
C

Chris “Kwpolska†Warrick

Do you have any accessible data about this?
Reasons I ask:
1. The decreasing popularity of emacs wrt vi seems out of proportion to the actual functionality
2. The downward emacs-curve is all the more striking considering the reverse situation some 15-20 years ago

I have a screenshot:
https://dl.dropboxusercontent.com/u/1933476/screenshots/emacs.png —
Dropbox claims it was taken at around 2011-03-19T11:32:24Z. Earlier
today, the exact same ad appeared while searching for “vimâ€, but not
“emacs†(why bother when you are the first hit for this query
anyways?).

Now, for statistics, how many hits it got, or whatnot — go ask the Vim
developers.
 
R

Roy Smith

Chris Angelico said:
I would hope that an absence of libpq could simply result in a
courteous exception when the module's imported, but maybe that'd be
hard to implement.

It works fine. I've had this in production for a while:

# Psycopg2 is only needed for a single hacky lookup which only happens
# in the station details admin tool. Installing it has compicated
# dependencies, so don't worry if it's not there.
try:
import psycopg2
except ImportError:
psycopg2 = None

[...]

if psycopg2 is None:
logger.warning("psycopg2 not installed")
return None

That being said, I agree that Postgres support does not belong in the
core product. Nor does MySQL, or Oracle, or SqlServer, or MongoDB, or
CouchDB, or Cassandra, or a zillion other databases. There's a few
reasons.

One (as several people have already mentioned), it bulks up, and
complicates, the release process.

The other is that once something is in the core distribution, it become
the de-facto "right way" to do something, whether it's the best or not.
From my own experience, I resisted trying nose for quite a while because
unittest was baked in and it was "good enough". Ditto for requests vs.
urllib.

And finally, with something like a database driver, you really don't
want your release schedule to be tied to the language. If the postgres
folks come out with a new database feature (or bug fix) which requires a
change to the driver, they should be able to release the new driver on
their own schedule.
 
A

Adam Tauno Williams

Are you sure that you're committing your changes (either by having
Depending on autocommit is a bug [when does commit happen then? consistency is a real problem]. Code should always explicitly ate least COMMIT or ROLLBACK if not explicitly BEGIN. Not to mention how much easier it makes it to read the code and understand the units of work.
Well, have you changed anything in your database configuration?

a big downside of autocommit - backend changes can break you app
 
A

Adam Tauno Williams

I neved had though of than an engine type could make so much mess.

Because your app and how it is written is broken.
MyISAM is the way to go then for my web development?
Why InnoDB failed to execute the queries?

No, nothing failed. Your app is broken. You are depending on auto commit - and that is a back end implementation detail. DO NOT USE AUTOCOMMIT.

The newer engine is expecting you to do things the right way. The old engine was sloppy and does serialization wrong - the reason there is a new engine.
 
Î

Îίκος Αλεξόπουλος

Στις 6/10/2013 12:45 πμ, ο/η Zero Piraeus έγÏαψε:
:

I neved had though of than an engine type could make so much mess.
MyISAM is the way to go then for my web development?
Why InnoDB failed to execute the queries?

Because you didn't commit. MyISAM doesn't support transactions, so when
you use it as the engine, your mistake happens to go unpunished.

Note that this is a weakness of MyISAM cancelling out a failure in your
code; it does *not* mean that MyISAM is better suited to your task.

By the way, Ned's right. At this point, this is no longer a Python
issue, and is off-topic for discussion here.

-[]z.
Thanks Zero,

i will use con.commit() from now and on because yesterdays i lost 3
hours trying to identify what was wrong with my MySQL statements in
python and it turned out to be for no good reason.
 
Î

Îίκος Αλεξόπουλος

Στις 6/10/2013 6:52 μμ, ο/η Adam Tauno Williams έγÏαψε:
Depending on autocommit is a bug [when does commit happen then? consistency is a real problem]. Code should always explicitly ate least COMMIT or ROLLBACK if not explicitly BEGIN. Not to mention how much easier it makes it to read the code and understand the units of work.
Well, have you changed anything in your database configuration?

a big downside of autocommit - backend changes can break you app
try:
# locate the ID of the page's URL
cur.execute('''SELECT ID FROM counters WHERE url = %s''', page )
data = cur.fetchone() #URL is unique, so should only be one

if not data:
#first time for page; primary key is automatic, hit is defaulted
cur.execute('''INSERT INTO counters (url) VALUES (%s)''', page )
cID = cur.lastrowid #get the primary key value of the new added record
else:
#found the page, save primary key and use it to issue hit UPDATE
cID = data[0]
cur.execute('''UPDATE counters SET hits = hits + 1 WHERE ID = %s''',
cID )


# find the visitor record for the (saved) cID and Cookie
cur.execute('''SELECT * FROM visitors WHERE counterID = %s and
cookieID = %s''', (cID, cookieID) )
data = cur.fetchone() #cookieID is unique

if not data:
# first time visitor on this page, create new record
cur.execute('''INSERT INTO visitors (counterID, cookieID, host, city,
useros, browser, ref, lastvisit) VALUES (%s, %s, %s, %s, %s, %s, %s,
%s)''', (cID, cookieID, host, city, useros, browser, ref, lastvisit) )
else:
# found the page, save its primary key for later use
vID = data[0]
# UPDATE record using retrieved vID
cur.execute('''UPDATE visitors SET host = %s, city = %s, useros = %s,
browser = %s, ref= %s, hits = hits + 1, lastvisit = %s
WHERE counterID = %s and cookieID = %s''', (host, city, useros,
browser, ref, lastvisit, vID, cookieID) )

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

Before is qw your post i have chnaged it to this.
rollback() is correct where i placed it, i hope con.commit() is also
correct too.
 
P

Piet van Oostrum

Îίκος Αλεξόπουλος said:
i will use con.commit() from now and on because yesterdays i lost 3
hours trying to identify what was wrong with my MySQL statements in
python and it turned out to be for no good reason.

That shows how important it is to study the software that you are using, and also to study the underlying computer science concepts.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,743
Messages
2,569,478
Members
44,899
Latest member
RodneyMcAu

Latest Threads

Top