Database

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

Eddy Vluggen5-Dec-18 1:44

Eddy Vluggen

5-Dec-18 1:44

It is better to catch the exception and CHECK what is the matter, as opposed to guessing.

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 24584679-Dec-18 21:28

Member 2458467

9-Dec-18 21:28

This is an old application with no code

Eddy Vluggen9-Dec-18 23:27

Eddy Vluggen

9-Dec-18 23:27

Without knowing the exception, you're basically guessing what the problem might be. Guessing is not my department.

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.

An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown

CHill6011-Dec-18 5:18

CHill60

11-Dec-18 5:18

Tell us exactly what the error says

simpledeveloper30-Nov-18 13:06

simpledeveloper

30-Nov-18 13:06

Hi,

I am getting the following error when I am trying to run a SQL Query with larger Data, any help how can I run the select statement.

SELECT     
	CASE WHEN PTT.POSTransactionTypeCode = 'A' THEN 'A' 
		WHEN PTT.POSTransactionTypeCode = 'C' THEN 'C'
		WHEN PTT.POSTransactionTypeCode = 'R' THEN 'R'
		WHEN PTT.POSTransactionTypeCode = 'I' THEN 'C' 
	END AS TransactionType, 
	CASE WHEN PTT.POSTransactionTypeCode = 'A' THEN 'A'
		WHEN PTT.POSTransactionTypeCode = 'C' THEN 'A'
		WHEN PTT.POSTransactionTypeCode = 'R' THEN 'A'
		WHEN PTT.POSTransactionTypeCode = 'I' THEN 'I' 
	END AS ProviderStatus, 
-- right format. uncomment when the PIN numbers are fixed.
'00000' + C.ProviderNbr + SPACE(1) AS ContractNbr,
-- right format. uncomment when sending the provider numbers that were added wrongly.
--'000000' + C.ContractNbr AS ContractNbr,
	'01' AS Other, 
	'000000' AS PIN, 
	UPPER(LEFT(C.ProviderName, 28)) AS ContractName,
	'PROGRAM DIRECTOR' + SPACE(8) AS ContractAddressAttention,
	UPPER(LEFT(A.StreetAddress1, 24)) AS Address1, 
	UPPER(LEFT(A.StreetAddress2, 24)) AS Address2,
	UPPER(LEFT(A.City, 17)) AS City,
	UPPER(S.PK_Geographic_Location_Code) AS State,
	LEFT(A.Zip1, 9) AS ZipCd,
	SPACE(12) AS ErrorCode2,
	SPACE(8) AS Filler
FROM         
	POSPINTransaction AS E INNER JOIN
	Provider AS C ON E.FKProviderId = C.PKProviderId INNER JOIN
	ProviderDate AS PD ON dbo.Udf_GetPKProviderDate(C.PKProviderId) = PD.PKContractDate INNER JOIN
	ProviderAddress AS PA ON PA.FKProviderId=C.PKProviderId INNER JOIN
	[Address] AS A ON A.PKAddressId = PA.FKAddressId INNER JOIN
	--ParentTypeLKP AS PT ON A.FKParentType = PT.PKParentType AND PT.ParentTypeCd = 'usrContract' INNER JOIN
	[State] AS S ON A.FK_State = S.PKStateId INNER JOIN
	POSTransactionTypeLKP PTT ON E.FKPOSTransactionTypeId=PTT.PKPOSTransactionTypeId
WHERE     
	(PA.IsCurrentAddress = 1) AND (E.TransactionSentDate IS NULL) AND (E.TransactionReceivedDate IS NULL) AND (E.FKPOSTransactionTypeId IN (SELECT PKPOSTransactionTypeId FROM POSTransactionTypeLKP WHERE POSTransactionTypeCode IN ('A', 'C', 'R'))) OR
	(PA.IsCurrentAddress = 1) AND (E.TransactionSentDate IS NULL) AND (E.TransactionReceivedDate IS NULL) AND (E.FKPOSTransactionTypeId = (SELECT Top 1 PKPOSTransactionTypeId FROM POSTransactionTypeLKP WHERE POSTransactionTypeCode = 'I')) AND (PD.EffectiveDateTo IS NULL) OR
	(PA.IsCurrentAddress = 1) AND (E.TransactionSentDate IS NULL) AND (E.TransactionReceivedDate IS NULL) AND (PD.EffectiveDateTo <= GETDATE())
UNION
SELECT     
	CASE WHEN PTT.POSTransactionTypeCode = 'A' THEN 'A' 
		WHEN PTT.POSTransactionTypeCode = 'C' THEN 'C' 
		WHEN PTT.POSTransactionTypeCode = 'R' THEN 'R' 
		WHEN PTT.POSTransactionTypeCode= 'I' THEN 'C' 
	END AS TransactionType, 
	CASE WHEN PTT.POSTransactionTypeCode = 'A' THEN 'A' 
		WHEN PTT.POSTransactionTypeCode = 'C' THEN 'A' 
		WHEN PTT.POSTransactionTypeCode = 'R' THEN 'A' 
		WHEN PTT.POSTransactionTypeCode = 'I' THEN 'I' 
	END AS ProviderStatus, SP.NPINumber AS ContractNbr,
	'01' AS Other,
	'000000' AS PIN,
	UPPER(LEFT(SP.FirstName, 28)) AS ContractName,
	'PROGRAM DIRECTOR' + SPACE(8) AS ContractAddressAttention,
	UPPER(LEFT(ADDR.StreetAddress1, 24)) AS Address1, 
	UPPER(LEFT(ADDR.StreetAddress2, 24)) AS Address2,
	UPPER(LEFT(ADDR.City, 17)) AS City,
	UPPER(ST.PK_Geographic_Location_Code) AS State,
	LEFT(ADDR.Zip1, 9) AS ZipCd,
	SPACE(12) AS ErrorCode2,
	SPACE(8) AS Filler
FROM         
	POSPINTransaction AS EDS INNER JOIN
	NPIAssociation NA ON NA.FKParentId=EDS.FKProviderId AND NA.FKParentTypeLKPId = (SELECT Min(PKParentTypeLKPId) FROM ParentTypeLKP WHERE ParentCode='PRV') INNER JOIN 
	NPI AS SP ON SP.PKNPIId = NA.FKNPIId LEFT OUTER JOIN
	POSTransactionError AS EDSERR ON EDSERR.FKPOSPINTransactionId = EDS.PKPOSPINTransactionId LEFT OUTER JOIN
	POSTransactionErrorLKP AS EDSERRCD ON EDSERRCD.PKPOSTransactionErrorLKPId = EDSERR.FKPOSTransactionErrorLKPId LEFT OUTER JOIN
	NPIAddress AS NPIADDR ON NPIADDR.FKNPIId = SP.PKNPIId AND NPIADDR.IsCurrentAddress = 1 INNER JOIN
	[Address] AS ADDR ON ADDR.PKAddressId=NPIADDR.FKAddressId INNER JOIN
	[State] AS ST ON ADDR.FK_State = ST.PKStateId INNER JOIN
	County AS CO ON CO.PKCountyId = ADDR.FK_County INNER JOIN
	POSTransactionTypeLKP PTT ON EDS.FKPOSTransactionTypeId=PTT.PKPOSTransactionTypeId
WHERE     
	(EDS.TransactionSentDate IS NULL) AND (EDS.TransactionReceivedDate IS NULL) AND 
	(EDS.FKPOSTransactionTypeId IN (SELECT PKPOSTransactionTypeId FROM POSTransactionTypeLKP WHERE POSTransactionTypeCode IN ('A', 'C', 'R')) 
	OR	EDS.FKPOSTransactionTypeId = (SELECT Top 1 PKPOSTransactionTypeId FROM POSTransactionTypeLKP WHERE POSTransactionTypeCode = 'I'))

Let me know if I can refine the script or any other advice is also welcome please thanks in advance friends.

Re: An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown

Richard MacCutchan30-Nov-18 22:29