On Oct 3, 2012, at 7:15 AM, Paolo Di Prodi wrote:
> Hello guys,
> I am implementing a round robin database for my project in SQLAlchemy and I
> have some problems with TRIGGERS.
> Everything works but for each INSERT OR REPLACE, the trigger is not called at
> all so in this case I would have only 25 rows rather than 50.
>
> Any suggestion to debug it?
Since you're using a plain SQL string, you'd probably want to run that same SQL
in the command line for your database.Getting it to work with just the
plain database console would be the first step. From there, the client API
should work the same way.
I'm not sure what database this is, but you should carefully review its
documentation to see what the specified behavior is regarding an INSERT trigger
and REPLACE.
>
> Here's my code:
>
>
> def InitRoundRobin(self):
> """
> Creates all the tables necessary one for the network swap events
> and one for the motion
> """
> #print "Creating database "+self.__file_path
> self.__file_event = Table('network', self.__metadata,
> Column('rrd_key', Integer, Sequence('rrd_key_seq'),
> primary_key=True),
> Column('name', String, default="NONE"),
> Column('location', String, default="NONE"),
> Column('type', String,default="NUM"),
> Column('direction', String,default="INP"),
> Column('value', String,default="0"),
> Column('time', TIMESTAMP(), default=now()),
> UniqueConstraint('name', 'time', name='name_time')
> )
> self.__file_event.create()
>
> self.__file_rrd = Table('rrdkey', self.__metadata,
> Column('rrd_key', Integer, Sequence('rrd_key_seq'),
> primary_key=True)
> )
> self.__file_rrd.create()
> operation = self.__file_rrd.insert()
> ## attempt an insert
> result=operation.execute(rrd_key=0);
>
> self.rrd_ins = DDL('''\
> DROP TRIGGER IF EXISTS rrd_ins;
> DELIMITER $$
> CREATE TRIGGER rrd_ins
> BEFORE INSERT ON network
> FOR EACH ROW
> BEGIN
> SET @rrd_key = 0;
> SET @rows = 10;
> IF NEW.rrd_key = 0 THEN
> SELECT rrd_key + 1
> FROM rrdkey
> INTO @rrd_key;
> SET NEW.rrd_key = @rrd_key;
> END IF;
> IF (NEW.rrd_key % @rows) THEN
> SET NEW.rrd_key = NEW.rrd_key % @rows;
> ELSE
> SET NEW.rrd_key = @rows;
> END IF;
> UPDATE network SET rrd_key = NEW.rrd_key;
> END;
> $$
> DELIMITER;''')
> event.listen(self.__file_event, 'after_create', self.rrd_ins)
>
> And the REPLACE code:
>
> def addEntryReplace(self, location,name,value,type):
> #print "Adding entry"
> #operation = self.__file_event.insert()
> t = text("INSERT OR REPLACE INTO network
> (location,name,value,type,time) VALUES (:location,:name,:value,:type,:time)")
> print t
> #operation.prefix_with("OR REPLACE");
> result=self.__connection.execute(t, location=location, name=name,
> value=value, type=type, time=now())
>
> This is called like this:
>
> def loopTest(self):
> self.__connection=connection = self.__db.connect()
> for i in range(0, 50):
> self.addEntryReplace("CASA","PARAM",i,"TYPE")
> print "Added entry ", i
>
> self.__connection.close()
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/sqlalchemy/-/1vqI9TpN-IEJ.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
