Python->CX_Oracle + Dash -> Tables

Dash Python
1

Hi all

Looking if someone can share a example and safe me some time.

Am a noob at Python, well make that development… Haven’t written code for a good 15yrs,
Been asked to create a dashboard,
My data comes from a Oracle Database, accessed via CX_Oracle, Currently I’m using FLASK and old style div+

tags.
Want to change this to a DASH table.

Anyone have some code/examples they willing to share.

G

2

… might have found the perfect example, and it happens to be from a friend of mine…

http://dominicgiles.com/blog/files/bbffdb638932620b3182980fbd0e3d5b-146.html

Enjoy.

G

1 Like
3

And some examples how to inject bind variables into cx_Oracle/sql query exposed via a pandas dataframe:

Bind variables - https://gist.github.com/mvaz/2006493

import cx_Oracle
import pandas
connection = cx_Oracle.connect('username/pwd@host:port/dbname')
def read_query(connection, query):
cursor = connection.cursor()
try:
    cursor.execute( query )
    names = [ x[0] for x in cursor.description]
    rows = cursor.fetchall()
    return pandas.DataFrame( rows, columns=names)
finally:
    if cursor is not None:
        cursor.close()

Or (https://stackoverflow.com/questions/14884686/creating-a-pandas-dataframe-from-a-database-query-that-uses-bind-variables)

import pandas as pd
import pandas.io.sql as psql
import cx_Oracle as odb
conn = odb.connect(_user +'/'+ _pass +'@'+ _dbenv)
	sqlStr = """SELECT * FROM customers 
            WHERE id BETWEEN :v1 AND :v2
"""
pars = {"v1":1234, "v2":5678}
df = psql.frame_query(sqlStr, conn, params=pars)

Or

curs = conn.cursor()
curs.execute(sqlStr, params)
df = pd.DataFrame(curs.fetchall())
df.columns = [rec[0] for rec in curs.description]

Or (https://sites.google.com/site/kittipat/programming-with-python/connecttooracledatabasefrompython)

import pandas as pd
import cx_Oracle
## db parameters
user_name = 'myusernamehere'
password = 'mypasswordhere'
query = 'select * from mydbdb.table_name where rownum < 20 and creation_date > sysdate - 2'
## connect to the DW and query the data
dsn = cx_Oracle.makedsn('Thehostname', portnumber, 'Thedbname')
con = cx_Oracle.connect(user_name, password, dsn)
curs = con.cursor()
curs.execute(query)

## Convert the cx_Oracle object to pandas dataframe
headers = [ x[0] for x in curs.description]
data = curs.fetchall()
df = pd.DataFrame( data, columns=headers)
## display the dataframe
df
## The type conversion is also pretty accurate
df.dtypes
2 Likes