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;
Example #2
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
Example #3
0
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)
Example #4
0
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!")
Example #5
0
    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()
# 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;
Example #8
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')
Example #9
0
# 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()