def __init__(self):
Simulation.__init__(self)
self.con = ceODBC.connect('DSN=SAPHANA;UID=BPANIGRA;PWD=Bimsap123')
#cleaning the previous data
self.con.cursor().execute("delete from BPANIGRA.INVENTORY_DEMO")
self.con.commit()
self.con.autocommit;
self.i = 0;
def __init__(self):
Simulation.__init__(self)
self.con = ceODBC.connect('DSN=SAPHANA;UID=BPANIGRA;PWD=Bimsap123')
#cleaning the previous data
self.con.cursor().execute("delete from BPANIGRA.INVENTORY_DEMO")
self.con.commit()
self.con.autocommit
self.i = 0
def sqlceread():
startTime = datetime.now()
cecnxn = ceODBC.connect("""DRIVER={SQL Server Native Client 11.0}; SERVER=localhost;
Trusted_Connection=yes;""", autocommit=True)
tempsql = ("""SELECT top 10000000 *
FROM [AdventureWorks2012].[dbo].[Frompandastosql]
""")
df = pd.read_sql(tempsql, cecnxn)
print(datetime.now() - startTime)
return df
def insertceODBC(df):
"""Timing Test: Use ceODBC to insert df into SQL table"""
startTime = datetime.now()
data = list(zip(df['First']
,df['Second']
,df['Third']
,df['Fourth']))
cecnxn = ceODBC.connect("""SERVER=localhost; DRIVER={SQL Server Native Client 11.0};
Trusted_Connection=yes;""", autocommit=True)
cursor = cecnxn.cursor()
cursor.executemany("""INSERT INTO [AdventureWorks2012].[dbo].[FromceODBCtosql]
values (?,?,?,?)""", data)
cursor.close()
print(datetime.now() - startTime)
def hello(connect_string):
try:
db = odbc.connect(connect_string)
cursor = db.cursor()
cursor.execute(SQL_VERSION_INFO)
for row in cursor:
print("Connected to the Teradata {} database".format(row[0]))
cursor.execute("SELECT 'Hello, world!';")
for row in cursor:
print("> {}".format(row[0]))
cursor.close()
except odbc.DatabaseError as ex:
print("Oops: {}".format(ex))
finally:
db.close()
print("Connection closed!")
def hello(connect_string):
try:
db = odbc.connect(connect_string)
cursor = db.cursor()
cursor.execute(SQL_VERSION_INFO)
for row in cursor:
print("Connected to the Teradata {} database".format(row[0]))
cursor.execute("SELECT 'Hello, world!';")
for row in cursor:
print("> {}".format(row[0]))
cursor.close()
except odbc.DatabaseError as ex:
print("Oops: {}".format(ex))
finally:
db.close()
print("Connection closed!")
def insert_rows(self, table_name, rows):
if table_name.lower() not in self.insert_statements.keys():
raise Exception(table_name, 'Do not know how to insert this type of record')
connection = odbc.connect(self.connection_string)
cursor = connection.cursor()
command = self.insert_statements[table_name.lower()]
i = 1
start = 0
end = self.batch_size
try:
print 'total rows to insert {}'.format(len(rows))
while start < len(rows):
batched_rows = rows[start:end]
cursor.executemany(command, batched_rows)
connection.commit()
i = i + 1
start = end
end = i * self.batch_size + 1
except:
# import pprint
# pp = pprint.PrettyPrinter(indent=4)
# pp.pprint(batched_rows)
print '{} {}-{}'.format(table_name, start, end)
from nose.tools import set_trace
set_trace()
raise
finally:
cursor.close()
connection.close()
def __init__(self):
Simulation.__init__(self)
self.con = ceODBC.connect('DSN=SAPHANA;UID=BPANIGRA;PWD=Bimsap123')
self.con.autocommit
self.i = 0
# import ceODBC module, get it from
# http://ceodbc.sourceforge.net/
import ceODBC
# connect to your database using your DSN and credentials
# this method uses your Windows credentials, an option available
# when setting up your dsn
db = ceODBC.connect('DSN=Wells')
# create a cursor object
c = db.cursor()
# execute your sql on cursor object
c.execute('SELECT wellname, slat, slon FROM wells WHERE wellid IN (1,2,3)')
# iterate through your resultset and print to stdout
# this method uses fetchall() since we know we are
# only getting back 3 wells
for each in c.fetchall():
print each
def __init__(self):
Simulation.__init__(self)
self.con = ceODBC.connect('DSN=SAPHANA;UID=BPANIGRA;PWD=Bimsap123')
self.con.autocommit;
self.i = 0;
insert_query = "insert into {} ({}) values ({})".format(table, cols, wildcards)
data = [tuple(x) for x in frame.values]
cursor.executemany(insert_query, data)
except:
raise
else:
cursor.close()
conn.commit()
if __name__ == '__main__':
try:
conn = ceODBC.connect('driver={{sql server}};server={};database={}'.format(SERVER,DATABASE))
# parse spreadsheet
spreadsheet = pd.ExcelFile(EXCEL_FILE)
df = spreadsheet.parse(spreadsheet.sheet_names[0])
# create mapping tables
stage_substage_map = df[['StageID', 'SubStageID']].drop_duplicates()
write_table(stage_substage_map, conn, 'StageSubStageMap')
substage_substatus_map = df[['SubStageID','SubStatusID']].drop_duplicates()
write_table(substage_substatus_map, conn, 'SubStageSubStatusMap')
substatusowner_substatus_map = df[['SubStatusOwnerID','SubStatusID']].drop_duplicates()
write_table(substatusowner_substatus_map, conn, 'SubStatusOwnerSubStatusMap')
import io
import os
import csv
import sys
import ceODBC
year = sys.argv[1]
quarter = sys.argv[2]
# Download index files and write content into SQLite
import pypyodbc
#connection = pypyodbc.connect('Driver={SQL Server};Server=PRAMENDRA-PC\SQLEXPRESS01;Database=edgar;Trusted_Connection=yes;')
connection = ceODBC.connect('Driver={SQL Server};Server=PRAMENDRA-PC\SQLEXPRESS01;Database=edgar;Trusted_Connection=yes;', autocommit = True)
cur = connection.cursor()
cur.execute("if not exists (select * from sysobjects where name='XBRLTaxonomyTags' and xtype='U') CREATE TABLE XBRLTaxonomyTags (tag varchar(512), version varchar(40), custom varchar(2),abstract varchar(2), datatype varchar(40), iord varchar(2),crdr varchar(2), tlabel varchar(1024), doc varchar(4096))")
outputDir = '%s%s' % (year , quarter)
# numTxtFile = '.\%s\\num.txt' % (outputDir)
# preTxtFile = '.\%s\\pre.txt' % (outputDir)
# subTxtFile = '.\%s\\sub.txt' % (outputDir)
tagTxtFile = '.\%s\\tag.txt' % (outputDir)
print(outputDir)
print(tagTxtFile)
# TODO: Exit with error if the num.txt file does not exist
#process num.txt.file
with open(tagTxtFile , newline='', encoding='latin-1') as csvfile:
reader = csv.reader(csvfile, delimiter='\t')
# import ceODBC module, get it from
# http://ceodbc.sourceforge.net/
import ceODBC
# connect to your database using your DSN and credentials
db = ceODBC.connect('DSN=dsn;UID=uid;PWD=pwd')
# create a cursor object
c = db.cursor()
# execute your sql on cursor object
c.execute('SELECT wellname FROM wells')
# iterate through your resultset and print to stdout
# this method uses fetchmany(), because fetchall()
# would print out thousands of records
for each in c.fetchmany(3):
print each
#!env python
import ceODBC as input_db_driver
input_filename = "S:\\TW5\\60080.MDB"
input_connection_string_template = ';'.join(
["Driver={Microsoft Access Driver (*.mdb)}",
"Dbq=%s",
"File Mode=Read Only"])
icon = input_db_driver.connect(input_connection_string_template % input_filename)
icur = icon.tables(type='TABLE')
print "Tables in %s:" % input_filename
print icur.fetchall()
#icon.close()