Database statements via python but database left intact
Νίκος Αλεξόπουλος
nikos.gr33k at gmail.com
Sun Oct 6 12:49:26 EDT 2013
Στις 6/10/2013 6:52 μμ, ο/η Adam Tauno Williams έγραψε:
>>>> 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.
>
> 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.
--
What is now proved was at first only imagined! & WebHost
<http://superhost.gr>
More information about the Python-list
mailing list