By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,769 Members | 1,151 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,769 IT Pros & Developers. It's quick & easy.

Cannot insert new records into Access (Python)

P: 1
This program runs and then at the end where i select the entire database and read it out to the console i see the results. The issue i am having is that when i go to the actual access database there is no changes and it is still blank.

Expand|Select|Wrap|Line Numbers
  1. import os
  2. import adodbapi
  3.  
  4. database = "db1.mdb"
  5. constr = 'Provider=Microsoft.Jet.OLEDB.4.0; Data Source=%s'  % database
  6. tablename = "address"
  7.  
  8. # connect to the database
  9. conn = adodbapi.connect(constr)
  10.  
  11. # create a cursor
  12. cur = conn.cursor()
  13.  
  14. path = 'XXXXXXXX'
  15. listing = os.listdir(path)
  16. i = 0
  17. for infile in listing:
  18.     i + 1
  19.     try:
  20.         if infile.endswith('.xml'):
  21.  
  22.             print('File: ' + path + infile)
  23.  
  24.             from xml.dom import minidom
  25.  
  26.             xmldoc = minidom.parse(path + infile)
  27.  
  28.             xfadata = xmldoc.getElementsByTagName("xfa:data")[0]
  29.             CostDealsForm = xfadata.getElementsByTagName("CostDealsForm")[0]
  30.             P1 = CostDealsForm.getElementsByTagName("P1")[0]
  31.             RefSub = P1.getElementsByTagName("RefSub")[0]
  32.  
  33.             RefNum = RefSub.getElementsByTagName("RefNum")[0].firstChild.data
  34.             Date = RefSub.getElementsByTagName("Date")[0].firstChild.data
  35.  
  36.             #print(RefNum, Date)
  37.  
  38.             # extract all the data
  39.             sql2 = "select * from %s" % tablename
  40.             sql = "INSERT INTO %s (Reference_Number, Date1) Values ('" %tablename + RefNum + "', '"+ Date + "');"
  41.             cur.execute(sql)
  42.             cur.commit(sql)
  43.  
  44.     except AttributeError:
  45.         RefNum = 'Error'
  46.         Date = 'Error'
  47.         sql = "INSERT INTO %s (Reference_Number, Date1) Values ('" %tablename + RefNum + "', '"+ Date + "');"
  48.         cur.execute(sql)
  49.  
  50. cur.execute(sql2)
  51.  
  52. # show the result
  53. result = cur.fetchall()
  54. for item in result:
  55.     print item
  56.  
  57. # close the cursor and connection
  58. cur.close()
  59. conn.close()
  60.  
Any help would be much appreciated.
Be kind as this is my first day on python :$

Thanks
Nov 28 '12 #1
Share this Question
Share on Google+
2 Replies

Expert 100+
P: 626
database = "db1.mdb"
constr = 'Provider=Microsoft.Jet.OLEDB.4.0; Data Source=%s' % database
The database is possibly being created in some directory other than the one you are looking in. Provide a complete path to the location you want to use.
Nov 29 '12 #2

P: 4
I personally work with mysql, however going based off my knowledge with databases and accessing tables for them I noticed two things:
  1. I see no IP address for the database.
  2. I do not see where a username or password was entered for the table/database.

Usually you have to list the IP of the database trying to be accessed, and usually you must have a user and its password to access its tables. Again I am going based off mysql database.
Dec 12 '12 #3

Post your reply

Sign in to post your reply or Sign up for a free account.