Insert problems with springpython.database.factory.PgdbConnectionFactor y

[swilhelm]

Hi,

in one project I use a the postgres dbconn factory. Standard selects work fine, but trying to insert data is a little bit mysterious.

As result I get "1" back (ok, success), but no data is written into the table.

I did most of the debugging from top and downside and found out that it must be around the quoting functions of the pgdb driver.

Maybe I'm to stupid, but I can't get it to work as written in the springpython docs.
Workaround: I use mysql (buaaahhhh

Any help welcome:

OS: Debian Etch
DB: PostgreSQL-8.3 (backport from lenny)
Pygresql: Official Debian Package 1:3.8.1-1etch1

Kind regards

Sven

[gregturn]

I think you may be assuming that Pgdb is using autocommit. If you visit https://fisheye.springframework.org/...ringpythontest, and then visit both databaseCoreTestCases.py, and databaseTransactionTestCases.py, you should find some samples of working code. If you scroll down a bit in the first file, you will notice a block like this:

class AbstractDatabaseTemplateTestCase(unittest.TestCase):
    def __init__(self, methodName='runTest'):
        unittest.TestCase.__init__(self, methodName)
        self.factory = None
        self.createdTables = False

    def setUp(self):
        if not self.createdTables:
            self.createTables()
        self.databaseTemplate = DatabaseTemplate(self.factory)
        self.databaseTemplate.execute("DELETE FROM animal")
        self.factory.commit()
        self.assertEquals(len(self.databaseTemplate.query_for_list("SELECT * FROM animal")), 0)
        self.databaseTemplate.execute("INSERT INTO animal (name, category, population) VALUES ('snake', 'reptile', 1)")
        self.databaseTemplate.execute("INSERT INTO animal (name, category, population) VALUES ('racoon', 'mammal', 0)")
        self.databaseTemplate.execute ("INSERT INTO animal (name, category, population) VALUES ('black mamba', 'kill_bill_viper', 1)")
        self.databaseTemplate.execute ("INSERT INTO animal (name, category, population) VALUES ('cottonmouth', 'kill_bill_viper', 1)")
        self.factory.commit()
        self.assertEquals(len(self.databaseTemplate.query_for_list("SELECT * FROM animal")), 4)

If you embed a factory.commit() operation, does it start working? factory should be handle on your PgdbConnectionFactory. NOTE: Several of the test cases show it inserting rows and then immediately query, to test the results. This works even if commits are required as long as your are using the same connection. If you try to examine the same data from another connection or if this connection factory had been some pooled solution, then you wouldn't see the inserted data, until it is committed.

Why does MySQL work? I believe there are two types of tables supported in MySQL. One of them actually does NOT support transactions, so every insert is an automatic commit, without you having to do it yourself.

• MyISAM — The default MySQL storage engine and the one that is used the most in Web, data warehousing, and other application environments. MyISAM is supported in all MySQL configurations, and is the default storage engine unless you have configured MySQL to use a different one by default.
• InnoDB — Used for transaction processing applications, and sports a number of features including ACID transaction support and foreign keys. InnoDB is included by default in all MySQL 5.1 binary distributions. In source distributions, you can enable or disable either engine by configuring MySQL as you like.

This list was plucked from MySQL's documentation, and shows the MyISAM is the default, which doesn't support transactions. For testing purposes, I have my test code deliberately using innodb, to confirm Spring Python handles transactions for MySQL, Sqlite, and PostGreSQL.

[Ammiddeon]

I want to use unbuffered and buffered input. I am, at the moment, only using un-buffered input, but I need both. Maybe this has something to do with Hikari input. Hikari doesnt take input. I have already changed to un-buffered input, but I want to do both types of input. How do I go about this?

[gregturn]

Okay, I don't really know what you are talking about when you say buffered and unbuffered. Sounds like file-based modes you are speaking. Is this some mode setting pgdb has with python?