[DB-SIG] SQLServer 2005
Robert Brewer
fumanchu at aminus.org
Wed Feb 4 23:00:12 CET 2009
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
More information about the DB-SIG
mailing list