CODEWITHSUNDEEP
pythonsqlrodbc
Feng Chen
Feng Chen

Reputation: 2253

How to use ODBC to link SQL database and do SQL queries in Python

I usually use R to do SQL queries by using ODBC to link to a SQL database. The code generally looks like this:

library(RODBC)
ch<-odbcConnect('B1P HANA',uid='****',pwd='****')
myOffice <- c(0)
office_clause = ""
if (myOffice != 0) {
  office_clause = paste(
    'AND "_all"."/BIC/ZSALE_OFF" IN (',paste(myOffice, collapse=", "),')'
  )
}
a <- sqlQuery(ch,paste(' SELECT "_all"."CALDAY" AS "ReturnDate                         FROM "SAPB1P"."/BIC/AZ_RT_A212" "_all"
                         WHERE "_all"."CALDAY"=20180101
                               ',office_clause,'
                         GROUP BY "_all"."CALDAY
                       '))

The workflow is:

  1. odbcConnect is to link R and SQL using ODBC.

  2. myOffice is an array for achieving data from R. Those data will be used as filter conditions in WHERE clause in SQL.

  3. a stores the query result from SQL database.

So, how to do all of these in Python, i.e., do SQL queries in Python by using ODBC to link SQL database and Python? I am new to Python. All I know is like:

import pyodbc
conn = pyodbc.connect(r'DSN=B1P HANA;UID=****;PWD=****')

Then I do not know how to continue. And I cannot find an overall example online. Could anyone help by providing a comprehensive example? From link SQL database in Python unitl retrieving the result?

Upvotes: 1

Views: 886

Answers (1)

Jerod Johnson
Jerod Johnson

Reputation: 784

Execute SQL from python

Instantiate a Cursor and use the execute method of the Cursor class to execute any SQL statement.

cursor = cnxn.cursor()

Select

You can use fetchall, fetchone, and fetchmany to retrieve rows returned from SELECT statements:

import pyodbc

cursor = cnxn.cursor()
cnxn = pyodbc.connect('DSN=myDSN;UID=***;PWD=***')
cursor.execute("SELECT Col1, Col2 FROM MyTable WHERE Col1= 'SomeValue'")
rows = cursor.fetchall()
for row in rows:
  print(row.Col1, row.Col2 )

You can provide parameterized queries in a sequence or in the argument list:

cursor.execute("SELECT Col1, Col2, Col3, ... FROM MyTable WHERE Col1 = ?", 'SomeValue',1)

Insert

INSERT commands also use the execute method; however, you must subsequently call the commit method after an insert or you will lose your changes:

cursor.execute("INSERT INTO MyTable (Col1) VALUES ('SomeValue')")
cnxn.commit()

Update and Delete

As with an insert, you must also call commit after calling execute for an update or delete:

cursor.execute("UPDATE MyTable SET Col1= 'SomeValue'")
cnxn.commit()

Metadata Discovery

You can use the getinfo method to retrieve data such as information about the data source and the capabilities of the driver. The getinfo method passes through input to the ODBC SQLGetInfo method.

cnxn.getinfo(pyodbc.SQL_DATA_SOURCE_NAME)

Upvotes: 2

Related Questions