[DB-SIG] SQLServer 2005

[Robert Brewer]

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
fumanchu at aminus.org