wtr_web wrote: > I need to access a SQLServer 2005 by creating, reading and writing by > a single thread/single user. > Has anybody experience with some openSrc SW? There are quite large > amounts of data involved (around a million rows in some tables). > I have only used Oracle-access myself.
http://www.aminus.net/geniusql can do that. To create a SQL Server 2005 database: >>> db = geniusql.db("sqlserver", **{ "name": 'demo', 'connections.Connect': "Provider=SQLNCLI; Integrated Security=SSPI; Initial Catalog=demo; Data Source=(local)\VAIO_VEDB"}) >>> db.create() >>> s = db.schema() >>> s.create() >>> s.name 'dbo' Now add a table: >>> Animal = s.table('Animal') >>> Animal['ID'] = s.column(int, autoincrement=True, key=True) >>> Animal['Name'] = s.column(unicode) >>> Animal['Legs'] = s.column(int, default=4) >>> s['Animal'] = Animal >>> s.items() [('Animal', geniusql.providers.sqlserver.SQLServerTable('Animal', '[Animal]'))] >>> s['Animal'] geniusql.providers.sqlserver.SQLServerTable('Animal', '[Animal]') >>> s['Animal']['Name'] geniusql.objects.Column(<type 'unicode'>, geniusql.providers.sqlserver.VARCHAR(_bytes=255), default=None, key=False, name='Name', qname='[Name]') ...and insert some rows: >>> Animal.insert(Name="Gorilla", Legs=2) {'Legs': 2, 'Name': 'Gorilla', 'ID': 1} >>> Animal.insert(Name="Fox") {'Name': 'Fox', 'ID': 2} >>> Animal.insert(Name="Bear") {'Name': 'Bear', 'ID': 3} >>> Animal.select_all(Legs=4) [{'Legs': 4, 'ID': 2, 'Name': u'Fox'}, {'Legs': 4, 'ID': 3, 'Name': u'Bear'}] Now let's close our first connection and reconnect: >>> db.connections.shutdown() >>> del s >>> db = geniusql.db("sqlserver", **{ "name": 'demo', 'connections.Connect': "Provider=SQLNCLI; Integrated Security=SSPI; Initial Catalog=demo; Data Source=(local)\VAIO_VEDB"}) >>> s = db.schema("dbo") >>> s.discover_all() >>> s.items() [('Animal', geniusql.providers.sqlserver.SQLServerTable('Animal', '[Animal]'))] >>> s['Animal'].select_all(Legs=4) [{'Legs': 4, 'ID': 2, 'Name': 'Fox'}, {'Legs': 4, 'ID': 3, 'Name': 'Bear'}] By default, db.connections uses a pool of connections; any thread that needs a new connection checks one out of the pool: >>> db.connections._factory <geniusql.conns.ConnectionPool object at 0x0217A490> However, there are other available factories: * ConnectionPerThread: makes a new conn for each thread * SingleConnection: makes a new conn per process (shared by all threads) * ConnectionFactory: makes a new conn per query/statement Example: >>> db.connections._factory = geniusql.conns.ConnectionPerThread( db.connections._get_conn, db.connections._del_conn) >>> Animal.select_all(Legs=4) [{'Legs': 4, 'ID': 2, 'Name': u'Fox'}, {'Legs': 4, 'ID': 3, 'Name': u'Bear'}] Robert Brewer fuman...@aminus.org _______________________________________________ DB-SIG maillist - DB-SIG@python.org http://mail.python.org/mailman/listinfo/db-sig
