INSERT statements not INSERTING when using mysql from python

Ben Benjamin.Barker at gmail.com
Fri Dec 29 07:29:28 EST 2006


I have found the problem, but not the cause.

I tried setting the database up manually before hand, which let me get
rid of the "IF NOT EXISTS" lines, and now it works!

But why the *** should it not work anyway? The first time it is run, no
database or tables, so it creates them. That works. But apparentlyu on
subsequent runs it decides the tables it created arent' actually there,
and overwrites them. Grrrrrrrrrrrrr.


Ben



Ben wrote:
> Well, I've checked the SQL log, and my insert statements are certainly
> being logged. The only option left open is that the table in question
> is being replaced, but I can't see why it should be...
>
>
> Ben wrote:
> > Nope... that can't be it. I tried running those commands manually and
> > nothing went wrong.
> > But then again when I execute the problematic command manually nothing
> > goes wrong. Its just not executing until the last time, or being
> > overwritten.
> >
> >
> > Ben wrote:
> > > Each time my script is run, the following is called:
> > >
> > > self.cursor.execute("CREATE DATABASE IF NOT EXISTS "+name)
> > > self.cursor.execute("USE "+name)
> > > self.cursor.execute("CREATE TABLE IF NOT EXISTS table_name ( ....
> > >
> > > The idea being that stuf is only created the first time the script is
> > > run, and after that the original tables and database is used. This
> > > might explain my pronblem if for some reason the old tables are being
> > > replaced... can anyone see anything wrong with the above?
> > >
> > > Ben
> > >
> > >
> > >
> > >
> > >
> > >
> > > Ben wrote:
> > > > One partial explanation might be that for some reason it is recreating
> > > > the table each time the code runs. My code says "CREATE TABLE IF NOT
> > > > EXISTS" but if for some reason it is creating it anyway and dropping
> > > > the one before that could explain why there are missing entires.
> > > >
> > > > It wouldn't explain why the NOT EXISTS line is being ignored though...
> > > >
> > > > Ben
> > > >
> > > >
> > > > Ben wrote:
> > > > > I initially had it set up so that when I connected to the database I
> > > > > started a transaction, then when I disconnected I commited.
> > > > >
> > > > > I then tried turning autocommit on, but that didn't seem to make any
> > > > > difference (althouh initially I thought it had)
> > > > >
> > > > > I'll go back and see what I can find...
> > > > > Cheers,
> > > > > Ben
> > > > >
> > > > >
> > > > > johnf wrote:
> > > > > > Ben wrote:
> > > > > >
> > > > > > > I don't know whether anyone can help, but I have an odd problem. I have
> > > > > > > a PSP (Spyce) script that makes many calls to populate a database. They
> > > > > > > all work without any problem except for one statement.
> > > > > > >
> > > > > > > I first connect to the database...
> > > > > > >
> > > > > > > self.con = MySQLdb.connect(user=username, passwd =password)
> > > > > > > self.cursor = self.con.cursor()
> > > > > > > self.cursor.execute("SET max_error_count=0")
> > > > > > >
> > > > > > > All the neccesary tables are created...
> > > > > > >
> > > > > > > self.cursor.execute("CREATE DATABASE IF NOT EXISTS "+name)
> > > > > > > self.cursor.execute("USE "+name)
> > > > > > >
> > > > > > > self.cursor.execute("CREATE TABLE IF NOT EXISTS networks (SM
> > > > > > > varchar(20),DMC int,DM varchar(50),NOS int,OS varchar(50),NID
> > > > > > > varchar(20))
> > > > > > >
> > > > > > > Then I execute many insert statements in various different loops on
> > > > > > > various tables, all of which are fine, and result in multiple table
> > > > > > > entries. The following one is executed many times also. and seems
> > > > > > > identical to the rest. The print statements output to the browser
> > > > > > > window, and appear repeatedly, so the query must be being called
> > > > > > > repeatedly also:
> > > > > > >
> > > > > > > print "<p><b>SQL query executing</b><p>"
> > > > > > > self.cursor.execute("INSERT INTO networks VALUES ('a',' "+i+"
> > > > > > > ','c','2','e','f','g')")
> > > > > > > print "<p><b>SQL query executed</b><p>"
> > > > > > >
> > > > > > > I have, for debugging, set "i" up as a counter variable.
> > > > > > >
> > > > > > > No errors are given, but the only entry to appear in the final database
> > > > > > > is that from the final execution of the INSERT statement (the last
> > > > > > > value of i)
> > > > > > >
> > > > > > > I suspect that this is to vague for anyone to be able to help, but if
> > > > > > > anyone has any ideas I'd be really grateful :-)
> > > > > > >
> > > > > > > It occured to me that if I could access the mysql query log that might
> > > > > > > help, but I was unsure how to enable logging for MysQL with python.
> > > > > > >
> > > > > > > Cheers,
> > > > > > >
> > > > > > > Ben
> > > > > >
> > > > > > Not sure this will help but where is the "commit"?  I don't use MySQL but
> > > > > > most SQL engines require a commit.
> > > > > > Johnf




More information about the Python-list mailing list