INSERT statements not INSERTING when using mysql from python

[Leo Kislov]

Ask Ben, he might know, although he's out to lunch.

Ben wrote:

> I'll try it after lunch. Does anyoone know whether this might be the
> problem?
>
> Ben
>
>
> Ben wrote:
> > 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