Cookbook for Python access to Access?

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

Cookbook for Python access to Access?

Goldsmith, David

Hi!  I’m relatively new to DB’s, though not to Python.  I simply need to learn how to programmatically put data--obtained using a Python script--into an existing Access DB.  I’ve searched and I’ve searched, found a few brief examples of how to connect--using, e.g., sqlite3--but when I execute a query, I’m told the file I’ve connected to is either encrypted or not a DB; it may be the former (I don’t know how to tell for certain, but I can access it using MATLAB’s database toolbox w/out any explicit decryption), but it definitely is a DB.  Can anyone refer me to a relevant cookbook or pretty complete example set?  Thanks!

 

--------------------------------------
David Goldsmith
Washington State Department of Ecology
Environmental Assessment Program
Modeling and Information Support Unit
300 Desmond Drive  | P.O. Box 47600
Lacey, WA  98503   | Olympia, WA  98504-7600
Tel:     (360) 407-6194
Fax:     (360) 407-6884

Email:   [hidden email]
Station: C2D-59

Web:     http://www.ecy.wa.gov/programs/eap/

 


_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|

Re: Cookbook for Python access to Access?

Susan-61
David, 

Have you tried these: 

Shuying

On Mon, Oct 4, 2010 at 1:25 PM, Goldsmith, David <[hidden email]> wrote:

Hi!  I’m relatively new to DB’s, though not to Python.  I simply need to learn how to programmatically put data--obtained using a Python script--into an existing Access DB.  I’ve searched and I’ve searched, found a few brief examples of how to connect--using, e.g., sqlite3--but when I execute a query, I’m told the file I’ve connected to is either encrypted or not a DB; it may be the former (I don’t know how to tell for certain, but I can access it using MATLAB’s database toolbox w/out any explicit decryption), but it definitely is a DB.  Can anyone refer me to a relevant cookbook or pretty complete example set?  Thanks!

 

--------------------------------------
David Goldsmith
Washington State Department of Ecology
Environmental Assessment Program
Modeling and Information Support Unit
300 Desmond Drive  | P.O. Box 47600
Lacey, WA  98503   | Olympia, WA  98504-7600
Tel:     (360) 407-6194
Fax:     (360) 407-6884

Email:   [hidden email]
Station: C2D-59

Web:     http://www.ecy.wa.gov/programs/eap/

 


_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig



_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|

Re: Cookbook for Python access to Access?

Carl Karsten
In reply to this post by Goldsmith, David
On Sun, Oct 3, 2010 at 9:25 PM, Goldsmith, David <[hidden email]> wrote:
> Hi!  I’m relatively new to DB’s, though not to Python.  I simply need to
> learn how to programmatically put data--obtained using a Python script--into
> an existing Access DB.  I’ve searched and I’ve searched, found a few brief
> examples of how to connect--using, e.g., sqlite3--but when I execute a
> query, I’m told the file I’ve connected to is either encrypted or not a DB;

Sounds like the error should read "not an sqlite db"

I am guessing you are trying to use the sqlite3 code to write to an Access db.

Is there an access app that still needs to use the db?  If not, then
you can create an sqlite db.


--
Carl K
_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|

Re: Cookbook for Python access to Access?

Vernon D. Cole
In reply to this post by Goldsmith, David
please pardon slowness, brevity & typos ... am on vacation & using
Windows Mobile...
 step 1) load pywin32 from sourceforge.
  step 2) look in your Python site-packages/adodbapi/test folder for example.
You can read or update ACCESS db using normal SQL statements and
Python api calls.

On 10/3/10, Goldsmith, David <[hidden email]> wrote:

> Hi!  I'm relatively new to DB's, though not to Python.  I simply need to
> learn how to programmatically put data--obtained using a Python
> script--into an existing Access DB.  I've searched and I've searched,
> found a few brief examples of how to connect--using, e.g., sqlite3--but
> when I execute a query, I'm told the file I've connected to is either
> encrypted or not a DB; it may be the former (I don't know how to tell
> for certain, but I can access it using MATLAB's database toolbox w/out
> any explicit decryption), but it definitely is a DB.  Can anyone refer
> me to a relevant cookbook or pretty complete example set?  Thanks!
>
>
>
> --------------------------------------
> David Goldsmith
> Washington State Department of Ecology
> Environmental Assessment Program
> Modeling and Information Support Unit
> 300 Desmond Drive  | P.O. Box 47600
> Lacey, WA  98503   | Olympia, WA  98504-7600
> Tel:     (360) 407-6194
> Fax:     (360) 407-6884
> Email:   [hidden email] <mailto:[hidden email]>
> Station: C2D-59
> Web:     http://www.ecy.wa.gov/programs/eap/
> <http://www.ecy.wa.gov/programs/eap/>
>
>
>
>
_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|

Re: Cookbook for Python access to Access?

Goldsmith, David
Thanks, Vernon.  I did as you suggest, including visiting

http://www.python.org/dev/peps/pep-0249/

I still don't see how to return a list of tables available in the DB one
has connected to.  

I can't believe no one has ever needed to do this, yet all the replies
I'm getting/examples I'm being referred to/methods I read about would
seem to indicate that such an operation is generally not supported by
the DB model--is that correct?

DG

-----Original Message-----
From: Vernon Cole [mailto:[hidden email]]
Sent: Thursday, October 07, 2010 10:44 AM
To: Goldsmith, David; DB-SIG @ Python.org
Subject: Re: [DB-SIG] Cookbook for Python access to Access?

please pardon slowness, brevity & typos ... am on vacation & using
Windows Mobile...
 step 1) load pywin32 from sourceforge.
  step 2) look in your Python site-packages/adodbapi/test folder for
example.
You can read or update ACCESS db using normal SQL statements and
Python api calls.

On 10/3/10, Goldsmith, David <[hidden email]> wrote:
> Hi!  I'm relatively new to DB's, though not to Python.  I simply need
to
> learn how to programmatically put data--obtained using a Python
> script--into an existing Access DB.  I've searched and I've searched,
> found a few brief examples of how to connect--using, e.g.,
sqlite3--but

> when I execute a query, I'm told the file I've connected to is either
> encrypted or not a DB; it may be the former (I don't know how to tell
> for certain, but I can access it using MATLAB's database toolbox w/out
> any explicit decryption), but it definitely is a DB.  Can anyone refer
> me to a relevant cookbook or pretty complete example set?  Thanks!
>
>
>
> --------------------------------------
> David Goldsmith
> Washington State Department of Ecology
> Environmental Assessment Program
> Modeling and Information Support Unit
> 300 Desmond Drive  | P.O. Box 47600
> Lacey, WA  98503   | Olympia, WA  98504-7600
> Tel:     (360) 407-6194
> Fax:     (360) 407-6884
> Email:   [hidden email]
<mailto:[hidden email]>
> Station: C2D-59
> Web:     http://www.ecy.wa.gov/programs/eap/
> <http://www.ecy.wa.gov/programs/eap/>
>
>
>
>
_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|

Re: Cookbook for Python access to Access?

Goldsmith, David
In reply to this post by Vernon D. Cole
Ala James Briggs' post (attached), is there a way in adodbapi to
leverage the fact that I have a UserDSN defined for my DB?

DG

-----Original Message-----
From: Vernon Cole [mailto:[hidden email]]
Sent: Thursday, October 07, 2010 10:44 AM
To: Goldsmith, David; DB-SIG @ Python.org
Subject: Re: [DB-SIG] Cookbook for Python access to Access?

please pardon slowness, brevity & typos ... am on vacation & using
Windows Mobile...
 step 1) load pywin32 from sourceforge.
  step 2) look in your Python site-packages/adodbapi/test folder for
example.
You can read or update ACCESS db using normal SQL statements and
Python api calls.

On 10/3/10, Goldsmith, David <[hidden email]> wrote:
> Hi!  I'm relatively new to DB's, though not to Python.  I simply need
to
> learn how to programmatically put data--obtained using a Python
> script--into an existing Access DB.  I've searched and I've searched,
> found a few brief examples of how to connect--using, e.g.,
sqlite3--but

> when I execute a query, I'm told the file I've connected to is either
> encrypted or not a DB; it may be the former (I don't know how to tell
> for certain, but I can access it using MATLAB's database toolbox w/out
> any explicit decryption), but it definitely is a DB.  Can anyone refer
> me to a relevant cookbook or pretty complete example set?  Thanks!
>
>
>
> --------------------------------------
> David Goldsmith
> Washington State Department of Ecology
> Environmental Assessment Program
> Modeling and Information Support Unit
> 300 Desmond Drive  | P.O. Box 47600
> Lacey, WA  98503   | Olympia, WA  98504-7600
> Tel:     (360) 407-6194
> Fax:     (360) 407-6884
> Email:   [hidden email]
<mailto:[hidden email]>
> Station: C2D-59
> Web:     http://www.ecy.wa.gov/programs/eap/
> <http://www.ecy.wa.gov/programs/eap/>
>
>
>
>



I have no idea why the ODBC driver connect string doesn't work.

I did notice you have a "User DSN", defined for Sandbox in the last graphic. A much simpler way to connect with an ODBC database when this is the case is the following:

>>> import pyodbc
>>> db=pyodbc.connect('DSN=Sandbox')


An Example I just tried on old access database I have on my current PC :

>>> db=pyodbc.connect('DSN=test')
>>> c=db.cursor()
>>> c.tables()
<pyodbc.Cursor object at 0x00F2F250>
>>> L=c.fetchall()
>>> for each in L:
...     print each
...    
(u'D:\\JUNK\\RABT.mdb', None, u'dbo_rabt_rec_differences', u'SYNONYM', None)
(u'D:\\JUNK\\RABT.mdb', None, u'dbo_rabt_rec_reports', u'SYNONYM', None)
(u'D:\\JUNK\\RABT.mdb', None, u'dbo_rabt_rec_results', u'SYNONYM', None)
(u'D:\\JUNK\\RABT.mdb', None, u'MSysAccessObjects', u'SYSTEM TABLE', None)
(u'D:\\JUNK\\RABT.mdb', None, u'MSysACEs', u'SYSTEM TABLE', None)
(u'D:\\JUNK\\RABT.mdb', None, u'MSysObjects', u'SYSTEM TABLE', None)
(u'D:\\JUNK\\RABT.mdb', None, u'MSysQueries', u'SYSTEM TABLE', None)
(u'D:\\JUNK\\RABT.mdb', None, u'MSysRelationships', u'SYSTEM TABLE', None)
(u'D:\\JUNK\\RABT.mdb', None, u'acurity', u'VIEW', None)
(u'D:\\JUNK\\RABT.mdb', None, u'admin', u'VIEW', None)
(u'D:\\JUNK\\RABT.mdb', None, u'admin_acurity', u'VIEW', None)
(u'D:\\JUNK\\RABT.mdb', None, u'not_in_acurity', u'VIEW', None)
(u'D:\\JUNK\\RABT.mdb', None, u'not_in_admin', u'VIEW', None)
(u'D:\\JUNK\\RABT.mdb', None, u'rec_differences', u'VIEW', None)
(u'D:\\JUNK\\RABT.mdb', None, u'rec_reports', u'VIEW', None)
(u'D:\\JUNK\\RABT.mdb', None, u'rec_results', u'VIEW', None)
(u'D:\\JUNK\\RABT.mdb', None, u'show rec results with subdatasheet detail', u'VIEW', None)
(u'D:\\JUNK\\RABT.mdb', None, u'show rec results with subdatasheet summary', u'VIEW', None)
(u'D:\\JUNK\\RABT.mdb', None, u'subdatasheet detail', u'VIEW', None)
(u'D:\\JUNK\\RABT.mdb', None, u'subdatasheet summary', u'VIEW', None)
>>>





_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|

Re: Cookbook for Python access to Access?

Joshua D. Drake
In reply to this post by Vernon D. Cole
On Thu, 2010-10-07 at 11:44 -0600, Vernon Cole wrote:
> please pardon slowness, brevity & typos ... am on vacation & using
> Windows Mobile...

That is no way to spend a vacation.
--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt

_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|

Re: Cookbook for Python access to Access?

Vernon D. Cole
In reply to this post by Goldsmith, David
I can't test this right now, but it looks like one may be able to get that information from a table called "dbf.databases".
Check out the documentation in http://technet.microsoft.com/en-us/library/cc805477.aspx particularly chapter 2.
   I note where it says: "To get a list of the objects in a database, you iterate through each Collection object. The following example iterates through each TableDef, QueryDef, Relation, and Container object in a database, and prints the object name to the Debug window."Followed by a VB example. If you can work out how to do that retrieval in SQL you will have it.

Let us know what works, if it works.
--
Vernon

On Thu, Oct 7, 2010 at 1:57 PM, Goldsmith, David <[hidden email]> wrote:
Thanks, Vernon.  I did as you suggest, including visiting

http://www.python.org/dev/peps/pep-0249/

I still don't see how to return a list of tables available in the DB one
has connected to.

I can't believe no one has ever needed to do this, yet all the replies
I'm getting/examples I'm being referred to/methods I read about would
seem to indicate that such an operation is generally not supported by
the DB model--is that correct?

DG

-----Original Message-----
From: Vernon Cole [mailto:[hidden email]]
Sent: Thursday, October 07, 2010 10:44 AM
To: Goldsmith, David; DB-SIG @ Python.org
Subject: Re: [DB-SIG] Cookbook for Python access to Access?

please pardon slowness, brevity & typos ... am on vacation & using
Windows Mobile...
 step 1) load pywin32 from sourceforge.
 step 2) look in your Python site-packages/adodbapi/test folder for
example.
You can read or update ACCESS db using normal SQL statements and
Python api calls.

On 10/3/10, Goldsmith, David <[hidden email]> wrote:
> Hi!  I'm relatively new to DB's, though not to Python.  I simply need
to
> learn how to programmatically put data--obtained using a Python
> script--into an existing Access DB.  I've searched and I've searched,
> found a few brief examples of how to connect--using, e.g.,
sqlite3--but
> when I execute a query, I'm told the file I've connected to is either
> encrypted or not a DB; it may be the former (I don't know how to tell
> for certain, but I can access it using MATLAB's database toolbox w/out
> any explicit decryption), but it definitely is a DB.  Can anyone refer
> me to a relevant cookbook or pretty complete example set?  Thanks!
>
>
>
> --------------------------------------
> David Goldsmith
> Washington State Department of Ecology
> Environmental Assessment Program
> Modeling and Information Support Unit
> 300 Desmond Drive  | P.O. Box 47600
> Lacey, WA  98503   | Olympia, WA  98504-7600
> Tel:     (360) 407-6194
> Fax:     (360) 407-6884
> Email:   [hidden email]
<mailto:[hidden email]>
> Station: C2D-59
> Web:     http://www.ecy.wa.gov/programs/eap/
> <http://www.ecy.wa.gov/programs/eap/>
>
>
>
>
_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig


_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig