:-) Ok, point taken! I fixed it in the end. It was nothing interesting at all - just a wayward line of code that was doing exactly what I feared - replacing the database each time rather than just when it was needed.
Ben Leo Kislov wrote: > 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 -- http://mail.python.org/mailman/listinfo/python-list