Database

Re: Can't Insert data into Access Table from Python

5-Dec-18 15:16

The following short Python program is supposed to read data from a SQLite datatable, convert some very large integer values in one field to Doubles, and write the data to an appropriately configured Access Table.
It runs happily with no error, but no data appears in the Access Table. What am I missing?

Python

import sqlite3
import pyodbc

conn1 = sqlite3.connect('data.db')
cur1 = conn1.cursor()

conn2 = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=G:\My_Data_Folder\NewEnergy.accdb;')
cur2 = conn2.cursor()

data=cur2.execute('SELECT * FROM RawValues')
print (cur2.fetchall()) # this correctly displays two dummy records from the Table I am trying to fill

data=cur1.execute('SELECT * FROM RTPValues ORDER BY TimeSerial ASC')
rows=cur1.fetchall()
for row in rows:
    cur2.execute('INSERT INTO RawValues (Millitime, PowerCost) VALUES (?,?)',(float(row[0]),row[1]))
    # If I insert print statements here, they confirm that the right data is being processed
conn2.commit
conn2.close
conn1.close

[EDIT]The error, as pointed out by Richard MacCutchan at the end of the thread, is that the conn2.commit call (as well as the subsequent .close calls) are not function calls at all, because they are missing the terminal empty parentheses that are required for Python to treat them as such. The last three lines of the code should be:

Python

conn2.commit()
conn2.close()
conn1.close()

As it stands, the program does what it is supposed to do, but the data is never written to the file.[/EDIT]

modified 8-Dec-18 14:51pm.

Richard MacCutchan5-Dec-18 22:19

Re: Can't Insert data into Access Table from Python

5-Dec-18 22:19

~~The float built-in only takes a single parameter, you are passing two, so that code will fail. Also you have specified two values in your insert statement but you are trying to pass only one.~~Victor pointed out my mistake.

[edit]
Having looked at the documentation, I guess that the extra parentheses round your values may be the problem. The example is:

Python

cursor.execute("insert into products(id, name) values (?, ?)", 'pyodbc', 'awesome library')

So perhaps your code should be:

Python

cur2.execute('INSERT INTO RawValues (Millitime, PowerCost) VALUES (?,?)', float(row[0]), row[1])

[/edit]

modified 6-Dec-18 7:54am.

Victor Nijegorodov6-Dec-18 1:09

Victor Nijegorodov

6-Dec-18 1:09

Richard MacCutchan wrote:
The float built-in only takes a single parameter, you are passing two, so that code will fail. Also you have specified two values in your insert statement but you are trying to pass only one.

Hmmm...
It seems to me there are two values here:

Quote:
cur2.execute('INSERT INTO RawValues (Millitime, PowerCost) VALUES (?,?)',(float(row[0]),row[1]))

The first is

Quote:
float(row[0])

, the second -

Quote:
row[1]

Re: Can't Insert data into Access Table from Python

Richard MacCutchan6-Dec-18 1:21

Re: Can't Insert data into Access Table from Python

6-Dec-18 1:21

Thanks, my eyesight missed that.

Peter R. Fletcher6-Dec-18 2:11

Re: Can't Insert data into Access Table from Python

6-Dec-18 2:11

Richard MacCutchan wrote:
Having looked at the documentation, I guess that the extra parentheses round your values may be the problem. The example is:

Python
cursor.execute("insert into products(id, name) values (?, ?)", 'pyodbc', 'awesome library')
So perhaps your code should be:

Python
cur2.execute('INSERT INTO RawValues (Millitime, PowerCost) VALUES (?,?)', float(row[0]), row[1])

No, the 'extra' parentheses turn the two values to be passed into a single (2-element) Python Tuple, which is what the call wants passed. If this were the problem, Python should be throwing an error. I have written a fair bit of other code, adding data to SQLite tables, which is exactly parallel and works as it should.

Richard MacCutchan6-Dec-18 3:16

Re: Can't Insert data into Access Table from Python

6-Dec-18 3:16

You will have to do some debugging as it is impossible to guess what is happening on your system. Does the execute command return a value, such as the number of rows inserted?

Peter R. Fletcher6-Dec-18 4:37

Re: Can't Insert data into Access Table from Python

6-Dec-18 4:37

It returns a cursor. Fetching the contents of that gets the following:

pyodbc.ProgrammingError: No results.  Previous SQL was not a query.

That does make a certain amount of sense, given that nothing is happening Smile | :)

, but doesn't really help, particularly since the syntax I am using appears valid and works with Sqlite. I was hoping that someone else had run into and solved this specific problem, since I am out of ideas.

[EDIT] Further research suggests that this 'error' is even less helpful, and may well be the normal result when executing something that is not a SELECT query. [/EDIT]

modified 6-Dec-18 11:41am.

Richard MacCutchan6-Dec-18 5:11

Re: Can't Insert data into Access Table from Python

6-Dec-18 5:11

Sorry, I am out of ideas, and the documentation is not a lot of help. Although a number of similar questions suggest that it should return a row count.

Richard MacCutchan8-Dec-18 4:46

Re: Can't Insert data into Access Table from Python

8-Dec-18 4:46

I have been playing with pyodbc and have successfully added records to my pre-built access database. Unfortunately that does not give any clues as to why your code fails.

[edit]
Created a RawValues table and successfully added a record with 2 values.
[/edit]

modified 8-Dec-18 11:41am.

Peter R. Fletcher8-Dec-18 5:04

Re: Can't Insert data into Access Table from Python

8-Dec-18 5:04

Can you post your working Python code, and what version/'bitness' of Access are you running?

Richard MacCutchan8-Dec-18 5:58

Re: Can't Insert data into Access Table from Python

8-Dec-18 5:58

No need, I just discovered I am blind (and maybe you are). The following calls are missing the parentheses that make them into function calls:

Python

conn2.commit
conn2.close
conn1.close

# should be:
conn2.commit()
conn2.close()
conn1.close()

Peter R. Fletcher8-Dec-18 7:36

Re: Can't Insert data into Access Table from Python

8-Dec-18 7:36

Of course! Thanks!!

Mycroft Holmes8-Dec-18 11:52

Mycroft Holmes

8-Dec-18 11:52

I see it happens to the best of us.

Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP

Re: Can't Insert data into Access Table from Python

Richard MacCutchan8-Dec-18 21:09

Error reading data from SQL Server ?

8-Dec-18 21:09

It happens to me embarrassingly too often. Blush | :O

Member 24584673-Dec-18 22:25

Member 2458467

3-Dec-18 22:25

Previously used winXP operating system running normally (very good) when switching to win7 operating system, this error, read errors from the SQL Server data, what is the error ? http://www.mediafire.com/view/4q5ux6e66ds4avb/ErrorOpenSQL.jpg/file

ZurdoDev4-Dec-18 2:22

ZurdoDev

4-Dec-18 2:22

That is a SELECT statement. What are we supposed to do with it?

Everyone is born right handed. Only the strongest overcome it.

Fight for left-handed rights and hand equality.

Eddy Vluggen4-Dec-18 2:55

Eddy Vluggen

4-Dec-18 2:55

Member 2458467 wrote:
what is the error

We don't know; the programmer choose to not display the exception he caught, but the command he issued.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.

Member 24584674-Dec-18 17:15

Member 2458467

4-Dec-18 17:15

The software runs fine in winXP but not good in win7, as I think it's good connection in win7 but lost some permissions so can not read