[DB-SIG] Connecting to MS SQL Server, Access, Oracle examples

[Dave Cole]

>>>>> "Jose" == Jose Rodriguez <jrodriguez3@cinci.rr.com> writes:

Jose> Ladies, Gentlemen,

Jose>         I am beginning to learn more about how to use Python to
Jose> access external databases.  I have got to be perfectly honest in
Jose> that I learn best by example.  Can anyone assist concerning
Jose> posting an example or better explaining how to attach to and
Jose> manipulate data within a database especially to MS SQL Server or
Jose> Access via ODBC connection?

Jose>         I appreciate any and all assistance with this matter.

If you are prepared to try alternatives, you can use my MSSQL Server
module.

It is still early days but I have not received any reports of it not
working.  I have not received any reports of it working either - but
that is another issue...

There is still more work to be done with the module - announcements
will be made to this mailing list.

Go to this page:

        http://www.object-craft.com.au/projects/mssql/

Get the module:

        http://www.object-craft.com.au/projects/mssql/mssql-0.04.tar.gz

If you unpack the archive you will find a pre-compiled binary module
for Windows.

As the page says, you should be able to simply do things like this:

>>> import MSSQL
>>> db = MSSQL.connect('rat', 'sa', '', 'pubs')
>>> c = db.cursor()
>>> c.execute('select * from titles')
>>> c.fetchone()
('BU1032', "The Busy Executive's Database Guide", 'business    ', '1389', 19.99, 5000.00, 10, 4095, 'An overview of available database systems with\015\012emphasis on common business applications. Illustrated.', Jun 12 1991 12:00:00:000AM)
>>> for f in c.description: print f
... 
('title_id', 47, 0, 7, 0, 0, 0)
('title', 47, 0, 81, 0, 0, 0)
('type', 47, 0, 13, 0, 0, 0)
('pub_id', 47, 0, 5, 0, 0, 0)
('price', 60, 0, 8, 0, 0, 0)
('advance', 60, 0, 8, 0, 0, 0)
('royalty', 56, 0, 4, 0, 0, 0)
('ytd_sales', 56, 0, 4, 0, 0, 0)
('notes', 47, 0, 201, 0, 0, 0)
('pubdate', 61, 0, 8, 0, 0, 0)
>>> c = db.cursor()
>>> c.callproc('sp_help', {'': 'titles'})
>>> c.fetchall()
[]
>>> c.nextset()
1
>>> for f in c.fetchall(): print f
... 
('titles', 'dbo', 'user table', Nov 13 1998  3:10:48:970AM)
>>> c.nextset()
1
>>> for f in c.fetchall(): print f
... 
('title_id', 'tid', 'no', 6, '     ', '     ', 'no', 'yes', 'no')
('title', 'varchar', 'no', 80, '     ', '     ', 'no', 'yes', 'no')
('type', 'char', 'no', 12, '     ', '     ', 'no', 'yes', 'no')
('pub_id', 'char', 'no', 4, '     ', '     ', 'yes', 'yes', 'yes')
('price', 'money', 'no', 8, '19   ', '4    ', 'yes', '(n/a)', '(n/a)')
('advance', 'money', 'no', 8, '19   ', '4    ', 'yes', '(n/a)', '(n/a)')
('royalty', 'int', 'no', 4, '10   ', '0    ', 'yes', '(n/a)', '(n/a)')
('ytd_sales', 'int', 'no', 4, '10   ', '0    ', 'yes', '(n/a)', '(n/a)')
('notes', 'varchar', 'no', 200, '     ', '     ', 'yes', 'yes', 'no')
('pubdate', 'datetime', 'no', 8, '     ', '     ', 'no', '(n/a)', '(n/a)')
>>> c.nextset()
1
>>> for f in c.fetchall(): print f
... 
('No identity column defined.', None, None, None)
>>> 

- Dave

-- 
http://www.object-craft.com.au