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?
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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
