def test_autocommit_switching(dsn, configuration): table_name = unique_table_name() connection = connect(dsn, **get_credentials(configuration)) connection.autocommit = True # <--- connection.cursor().execute(f"CREATE TABLE {table_name} (a INTEGER)") connection.close() options = make_options(autocommit=True) connection = connect( dsn, turbodbc_options=options, **get_credentials(configuration) ) connection.autocommit = False # <--- connection.cursor().execute(f"INSERT INTO {table_name} VALUES (?)", [42]) connection.close() # table is there, but data was not persisted connection = connect(dsn, **get_credentials(configuration)) cursor = connection.cursor() cursor.execute(f"SELECT * FROM {table_name}") results = cursor.fetchall() assert results == [] cursor.execute(f"DROP TABLE {table_name}") connection.commit()
def test_no_autocommit(dsn, configuration): connection = connect(dsn, **get_credentials(configuration)) connection.cursor().execute('CREATE TABLE test_no_autocommit (a INTEGER)') connection.close() connection = connect(dsn, **get_credentials(configuration)) with pytest.raises(DatabaseError): connection.cursor().execute('SELECT * FROM test_no_autocommit')
def test_no_autocommit(dsn, configuration): connection = connect(dsn, **get_credentials(configuration)) connection.cursor().execute("CREATE TABLE test_no_autocommit (a INTEGER)") connection.close() connection = connect(dsn, **get_credentials(configuration)) with pytest.raises(DatabaseError): connection.cursor().execute("SELECT * FROM test_no_autocommit")
def test_no_autocommit(dsn, configuration): connection = connect(dsn) connection.cursor().execute('CREATE TABLE test_no_autocommit (a INTEGER)') connection.close() connection = connect(dsn) with pytest.raises(DatabaseError): connection.cursor().execute('SELECT * FROM test_no_autocommit')
def __init__(self): """ :param config: """ try: #[].[dbo].[big_table] self.database = 'AdventureWorks2016' #[AdventureWorks2016].[Person].[Person] user = '******' pwd = 'trusted' server = 'CRPC015162\SQLDEV' driver = 'ODBC+Driver+17+for+SQL+Server' driver = "{" + driver.replace("+", " ") + "}" options = make_options(read_buffer_size=Megabytes(100), parameter_sets_to_buffer=1000, varchar_max_character_limit=10000, use_async_io=True, prefer_unicode=True, autocommit=True, large_decimals_as_64_bit_types=True, limit_varchar_results_to_max=True) if pwd == 'trusted': self.cursor = turbodbc.connect( driver=driver, server=server, database=self.database, uid=user, Trusted_Connection='yes', turbodbc_options=options).cursor() else: self.cursor = turbodbc.connect( driver=driver, server=server, database=self.database, uid=user, pwd=pwd, turbodbc_options=options).cursor() con_string = f"DRIVER={driver};SERVER={server};DATABASE={self.database};Trusted_Connection=yes;" \ if pwd == 'trusted' \ else f"DRIVER={driver};" \ f"SERVER={server};" \ f"DATABASE={self.database};" \ f"UID={user};" \ f"PWD={pwd}" quoted = urllib.parse.quote_plus(con_string) self.engine = sa.create_engine( 'mssql+pyodbc:///?odbc_connect={}'.format(quoted)) except turbodbc.exceptions.DatabaseError as ex: logging.exception( f"[Exception][database_connector][init][{str(ex)}]")
def establish_connection(self, dsn: str = 'NO_DATA'): """ Establish connection for specified dsn with autocommit = True. """ self.connect = connect(dsn=dsn) self.cursor = self.connect.cursor() self.connect.autocommit = True
def test_connect_buffer_sizes_default_values(dsn, configuration): connection = connect(dsn=dsn, rows_to_buffer=317, parameter_sets_to_buffer=123) assert connection.impl.rows_to_buffer == 317 assert connection.impl.parameter_sets_to_buffer == 123
def test_connect_with_connection_string(dsn, configuration): connection_string = "DSN=%s;" % dsn for para, val in get_credentials(configuration).items(): connection_string = connection_string + f"{para}={val};" connection = connect(connection_string=connection_string) connection.cursor().execute("SELECT 'foo'") connection.close()
def create_connection_string_turbo(server, database): options = turbodbc.make_options(prefer_unicode=True) constr = 'Driver={ODBC Driver 13 for SQL Server};Server=' + \ server + ';Database=' + database + ';Trusted_Connection=yes;' con = turbodbc.connect(connection_string=constr, turbodbc_options=options) return con
def pg(): """Start docker container for MS SQL and cleanup connection afterward.""" stop_docker('intake-postgres', let_fail=False) start_postgres() kwargs = dict(dsn="PG") timeout = 5 try: while True: try: conn = turbodbc.connect(**kwargs) break except Exception as e: print(e) time.sleep(0.2) timeout -= 0.2 if timeout < 0: raise curs = conn.cursor() curs.execute("""CREATE TABLE testtable (productid int PRIMARY KEY NOT NULL, productname varchar(25) NOT NULL, price float NULL, productdescription text NULL)""") for i, row in df0.iterrows(): curs.execute("INSERT INTO testtable " "VALUES ({}, '{}', {}, '{}')".format(*([i] + row.tolist()))) conn.commit() conn.close() yield kwargs finally: stop_docker('intake-postgres')
def __init__(self, table_name, dsn_str, idx_cols, table1, table2, temp_table='temp_blocking'): ''' table_name(str): name of SQL table that blocked pairs will be uploaded to dsn_str(str): dsn ODBC name to connect to SQL through turbodbc driver idx_cols(tuple, listlike): columns names for unique identifiers of each record we are blocking from table1(str): table name from first table we are blocking from table2(str): table name from second table we are blocking from ''' self.conn = turbodbc.connect(dsn=dsn_str) self.cursor = self.conn.cursor() self.table_name = table_name self.idx_cols = idx_cols if not self.table_exists(table_name): self.create_table() self.table1 = table1 self.table2 = table2 self.temp_table = temp_table self.extra_joins = None self.blocks = None
def test_connect_with_connection_string(dsn, configuration): connection_string = "DSN=%s;" % dsn for para, val in get_credentials(configuration).items(): connection_string = connection_string + "%s=%s;" % (para, val) connection = connect(connection_string=connection_string) connection.cursor().execute("SELECT 'foo'") connection.close()
def __initialise(self): """Connect to a specified db.""" self.__connection = turbodbc.connect( driver="ODBC Driver 13 for SQL Server", # Change this for driver server=self.__server, database=self.__dbName, trusted_connection='yes')
def test_commit(dsn, configuration): connection = connect(dsn) connection.cursor().execute('CREATE TABLE test_commit (a INTEGER)') connection.commit() connection.close() connection = connect(dsn) cursor = connection.cursor() cursor.execute('SELECT * FROM test_commit') results = cursor.fetchall() assert results == [] cursor.execute('DROP TABLE test_commit') connection.commit()
def run(self, dispatcher, tracker, domain): # what your action should do meter = str(tracker.get_slot('meter')) #start_date = str(tracker.get_slot('start_date')) #end_date = str(tracker.get_slot('end_date')) #meter = '50775' #start_date = '2018-12-01' #end_date = '2018-12-05' today = datetime.date.today() first = today.replace(day=1) lastMonth = first - datetime.timedelta(days=1) start_date = lastMonth.strftime("%Y-%m-")+'01' end_date = lastMonth.strftime("%Y-%m-%d") connection = connect(dsn='AradTechNew',uid='OriKronfeld',pwd='Basket76&Galil') sql = 'select MeterCount,tDate,LastReadTime,LastRead,MeterStatus,EstimatedCons from ori.DailyReadingsData where MeterCount='+meter data = pd.read_sql(sql,connection) connection.close() data = data[(data.tDate>=start_date) & (data.tDate<=end_date)] if data.EstimatedCons.size==0: dispatcher.utter_message("Meter: "+meter+" is not availble") else: dispatcher.utter_message("Meter: "+meter+" consumption from "+start_date+" to "+end_date+" is "+str(data.EstimatedCons.sum().round())+" cubes") return []
def test_setinputsizes_does_not_raise(dsn, configuration): """ It is legal for setinputsizes() to do nothing, so anything except raising an exception is ok """ cursor = connect(dsn).cursor() cursor.setinputsizes([10, 20])
def open_connection(configuration, rows_to_buffer=100, parameter_sets_to_buffer=100): dsn = configuration['data_source_name'] connection = turbodbc.connect(dsn, rows_to_buffer=rows_to_buffer, parameter_sets_to_buffer=parameter_sets_to_buffer) yield connection connection.close()
def test_autocommit_enabled_at_start(dsn, configuration): table_name = unique_table_name() options = make_options(autocommit=True) connection = connect(dsn, turbodbc_options=options, **get_credentials(configuration)) connection.cursor().execute('CREATE TABLE {} (a INTEGER)'.format(table_name)) connection.close() connection = connect(dsn, **get_credentials(configuration)) cursor = connection.cursor() cursor.execute('SELECT * FROM {}'.format(table_name)) results = cursor.fetchall() assert results == [] cursor.execute('DROP TABLE {}'.format(table_name)) connection.commit()
def test_closed_cursor_raises_when_used(dsn, configuration): connection = connect(dsn, **get_credentials(configuration)) cursor = connection.cursor() cursor.close() with pytest.raises(InterfaceError): cursor.execute("SELECT 42") with pytest.raises(InterfaceError): cursor.executemany("SELECT 42") with pytest.raises(InterfaceError): cursor.executemanycolumns("SELECT 42", []) with pytest.raises(InterfaceError): cursor.fetchone() with pytest.raises(InterfaceError): cursor.fetchmany() with pytest.raises(InterfaceError): cursor.fetchall() with pytest.raises(InterfaceError): six.next(cursor)
def open_connection(configuration, rows_to_buffer=None, parameter_sets_to_buffer=100, use_async_io=False): dsn = configuration['data_source_name'] if rows_to_buffer: connection = turbodbc.connect(dsn, rows_to_buffer=rows_to_buffer, parameter_sets_to_buffer=parameter_sets_to_buffer, use_async_io=use_async_io, **get_credentials(configuration)) else: connection = turbodbc.connect(dsn, read_buffer_size=turbodbc.Megabytes(1), parameter_sets_to_buffer=parameter_sets_to_buffer, use_async_io=use_async_io, **get_credentials(configuration)) yield connection connection.close()
def test_setinputsizes_does_not_raise(dsn, configuration): """ It is legal for setinputsizes() to do nothing, so anything except raising an exception is ok """ cursor = connect(dsn, **get_credentials(configuration)).cursor() cursor.setinputsizes([10, 20])
def insert_tables(self, model_name='', segment_id=1, segment_name=''): """Insert tables. """ connection_prd_dm = turbodbc.connect(dsn=self.dsn_prd_dm) cursor = connection_prd_dm.cursor() sql="INSERT INTO PRD_DM.MODEL_DESC VALUES (?, ?)" cursor.executemanycolumns(sql, [np.array([model_id]), np.array([model_name])]) connection_prd_dm.commit() cursor.close() print('Inserted into MODEL_DESC.') cursor = connection_prd_dm.cursor() sql="INSERT INTO PRD_DM.segment_desc VALUES (?, ?, ?)" cursor.executemanycolumns(sql, [np.array([model_id]), np.array([segment_id]),np.array([segment_name])]) connection_prd_dm.commit() cursor.close() print('Inserted into segment_desc.') cursor = connection_prd_dm.cursor() sql=f"INSERT INTO prd_dm.scoring SELECT * FROM uat_dm.{self.bucket_table_name}" cursor.execute(sql) connection_prd_dm.commit() cursor.close() print('Inserted into scoring.') cursor = connection_prd_dm.cursor() sql="INSERT INTO PRD_DM.scoring_to_load VALUES (?, ?, ?)" cursor.executemanycolumns(sql, [np.array([self.report_date]), np.array([self.model_id]),np.array([self.model_version])]) connection_prd_dm.commit() cursor.close() connection_prd.close() print('Inserted into scoring_to_load.')
def test_new_cursor_properties(dsn, configuration): connection = connect(dsn, **get_credentials(configuration)) cursor = connection.cursor() # https://www.python.org/dev/peps/pep-0249/#rowcount assert cursor.rowcount == -1 assert None == cursor.description assert cursor.arraysize == 1
def test_pep343_with_statement(dsn, configuration): with connect(dsn, **get_credentials(configuration)) as connection: with connection.cursor() as cursor: cursor.execute("SELECT 42") # cursor should be closed with pytest.raises(InterfaceError): cursor.execute("SELECT 42")
def test_rollback(dsn, configuration): connection = connect(dsn) connection.cursor().execute('CREATE TABLE test_rollback (a INTEGER)') connection.rollback() with pytest.raises(DatabaseError): connection.cursor().execute('SELECT * FROM test_rollback')
def test_commit(dsn, configuration): table_name = unique_table_name() connection = connect(dsn, **get_credentials(configuration)) connection.cursor().execute('CREATE TABLE {} (a INTEGER)'.format(table_name)) connection.commit() connection.close() connection = connect(dsn, **get_credentials(configuration)) cursor = connection.cursor() cursor.execute('SELECT * FROM {}'.format(table_name)) results = cursor.fetchall() assert results == [] cursor.execute('DROP TABLE {}'.format(table_name)) connection.commit()
def test_pep343_with_statement(dsn, configuration): with connect(dsn, **get_credentials(configuration)) as connection: cursor = connection.cursor() # connection should be closed, test it with the cursor with pytest.raises(InterfaceError): cursor.execute("SELECT 42")
def connect(api, dsn): if api == "pyodbc": return pyodbc.connect(dsn=dsn) else: return turbodbc.connect(dsn, parameter_sets_to_buffer=100000, rows_to_buffer=100000, use_async_io=True)
def test_new_cursor_properties(dsn, configuration): connection = connect(dsn) cursor = connection.cursor() # https://www.python.org/dev/peps/pep-0249/#rowcount assert cursor.rowcount == -1 assert None == cursor.description assert cursor.arraysize == 1
def test_commit(dsn, configuration): table_name = unique_table_name() connection = connect(dsn, **get_credentials(configuration)) connection.cursor().execute(f"CREATE TABLE {table_name} (a INTEGER)") connection.commit() connection.close() connection = connect(dsn, **get_credentials(configuration)) cursor = connection.cursor() cursor.execute(f"SELECT * FROM {table_name}") results = cursor.fetchall() assert results == [] cursor.execute(f"DROP TABLE {table_name}") connection.commit()
def test_rollback(dsn, configuration): table_name = unique_table_name() connection = connect(dsn, **get_credentials(configuration)) connection.cursor().execute(f"CREATE TABLE {table_name} (a INTEGER)") connection.rollback() with pytest.raises(DatabaseError): connection.cursor().execute(f"SELECT * FROM {table_name}")
def test_setoutputsize_does_not_raise(dsn, configuration): """ It is legal for setinputsizes() to do nothing, so anything except raising an exception is ok """ cursor = connect(dsn).cursor() cursor.setoutputsize(1000, 42) # with column cursor.setoutputsize(1000, column=42) # with column cursor.setoutputsize(1000) # without column
def test_setoutputsize_does_not_raise(dsn, configuration): """ It is legal for setinputsizes() to do nothing, so anything except raising an exception is ok """ cursor = connect(dsn, **get_credentials(configuration)).cursor() cursor.setoutputsize(1000, 42) # with column cursor.setoutputsize(1000, column=42) # with column cursor.setoutputsize(1000) # without column
def test_rollback(dsn, configuration): table_name = unique_table_name() connection = connect(dsn, **get_credentials(configuration)) connection.cursor().execute('CREATE TABLE {} (a INTEGER)'.format(table_name)) connection.rollback() with pytest.raises(DatabaseError): connection.cursor().execute('SELECT * FROM {}'.format(table_name))
def connect(api, dsn): if api == "pyodbc": return pyodbc.connect(dsn=dsn) if api == "PyGreSQL": return pgdb.connect(database='test_db', host='localhost:5432', user='******', password='******') if api == "psycopg2": return psycopg2.connect("dbname='test_db' user='******' host='localhost' password='******'") else: return turbodbc.connect(dsn, parameter_sets_to_buffer=100000, rows_to_buffer=100000, use_async_io=True)
def _get_used_date(self): """Automatically get data which is loaded into tables.""" connection = turbodbc.connect(dsn=self.dsn) cursor = connection.cursor() sql = f'select report_date from UAT_DM.{self.bucket_table_name}' cursor.execute(sql) self.report_date = cursor.fetchall()[0][0] cursor.close() connection.close()
def open_connection(configuration, rows_to_buffer=100, parameter_sets_to_buffer=100): dsn = configuration['data_source_name'] connection = turbodbc.connect( dsn, rows_to_buffer=rows_to_buffer, parameter_sets_to_buffer=parameter_sets_to_buffer) yield connection connection.close()
def test_connect_performance_settings(dsn, configuration): connection = connect(dsn=dsn, rows_to_buffer=317, parameter_sets_to_buffer=123, use_async_io=True, **get_credentials(configuration)) assert connection.impl.get_buffer_size().rows == 317 assert connection.impl.parameter_sets_to_buffer == 123 assert connection.impl.use_async_io == True
def test_closing_connection_closes_all_cursors(dsn, configuration): connection = connect(dsn, **get_credentials(configuration)) cursor_1 = connection.cursor() cursor_2 = connection.cursor() connection.close() with pytest.raises(InterfaceError): cursor_1.execute("SELECT 42") with pytest.raises(InterfaceError): cursor_2.execute("SELECT 42")
def _get_max_model_id(self): """Get max model_id from table and increment.""" connection_prd_dm = turbodbc.connect(dsn=self.dsn_prd_dm) cursor = connection_prd_dm.cursor() sql = 'select max(model_id) from PRD_DM.MODEL_DESC' cursor.execute(sql) model_id = cursor.fetchall()[0][0] self.model_id = model_id + 1 cursor.close() connection_prd_dm.close()
def execute(self, query): """Execute SQL query on Teradata database. Parameters: query (str): SQL query """ con = turbodbc.connect( dsn=self.dsn, turbodbc_options=turbodbc.make_options(prefer_unicode=True, autocommit=True) ) cur = con.cursor() cur.execute(query) con.close()
def test_autocommit_switching(dsn, configuration): table_name = unique_table_name() connection = connect(dsn, **get_credentials(configuration)) connection.autocommit = True # <--- connection.cursor().execute('CREATE TABLE {} (a INTEGER)'.format(table_name)) connection.close() options = make_options(autocommit=True) connection = connect(dsn, turbodbc_options=options, **get_credentials(configuration)) connection.autocommit = False # <--- connection.cursor().execute('INSERT INTO {} VALUES (?)'.format(table_name), [42]) connection.close() # table is there, but data was not persisted connection = connect(dsn, **get_credentials(configuration)) cursor = connection.cursor() cursor.execute('SELECT * FROM {}'.format(table_name)) results = cursor.fetchall() assert results == [] cursor.execute('DROP TABLE {}'.format(table_name)) connection.commit()
def open_connection(configuration, rows_to_buffer=None, parameter_sets_to_buffer=100, **turbodbc_options): dsn = configuration['data_source_name'] prefer_unicode = configuration.get('prefer_unicode', False) read_buffer_size = turbodbc.Rows(rows_to_buffer) if rows_to_buffer else turbodbc.Megabytes(1) options = turbodbc.make_options(read_buffer_size=read_buffer_size, parameter_sets_to_buffer=parameter_sets_to_buffer, prefer_unicode=prefer_unicode, **turbodbc_options) connection = turbodbc.connect(dsn, turbodbc_options=options, **get_credentials(configuration)) yield connection connection.close()
def test_closed_cursor_raises_when_used(dsn, configuration): connection = connect(dsn) cursor = connection.cursor() cursor.close() with pytest.raises(InterfaceError): cursor.execute("SELECT 42") with pytest.raises(InterfaceError): cursor.executemany("SELECT 42") with pytest.raises(InterfaceError): cursor.fetchone() with pytest.raises(InterfaceError): cursor.fetchmany() with pytest.raises(InterfaceError): cursor.fetchall() with pytest.raises(InterfaceError): cursor.next()
def test_cursor_on_closed_connection_raises(dsn, configuration): connection = connect(dsn, **get_credentials(configuration)) connection.close() with pytest.raises(InterfaceError): connection.cursor()
def test_open_cursor_without_result_set_raises(dsn, configuration): connection = connect(dsn, **get_credentials(configuration)) cursor = connection.cursor() with pytest.raises(InterfaceError): cursor.fetchone()
def test_closing_twice_does_not_raise(dsn, configuration): connection = connect(dsn, **get_credentials(configuration)) cursor = connection.cursor() cursor.close() cursor.close()
def test_autocommit_querying(dsn, configuration): connection = connect(dsn, **get_credentials(configuration)) assert connection.autocommit == False connection.autocommit = True assert connection.autocommit == True connection.close()
def test_connection_does_not_strongly_reference_cursors(dsn, configuration): connection = connect(dsn, **get_credentials(configuration)) cursor = connection.cursor() import sys assert sys.getrefcount(cursor) == 2
def test_closing_twice_is_ok(dsn, configuration): connection = connect(dsn, **get_credentials(configuration)) connection.close() connection.close()
def test_nextset_unsupported(dsn, configuration): cursor = connect(dsn).cursor() with pytest.raises(AttributeError): cursor.nextset()
def test_callproc_unsupported(dsn, configuration): cursor = connect(dsn).cursor() with pytest.raises(AttributeError): cursor.callproc()
def test_closing_twice_does_not_raise(dsn, configuration): connection = connect(dsn) cursor = connection.cursor() cursor.close() cursor.close()