def test_invalid_ntlm_creds(): if not LIVE_TEST: pytest.skip('LIVE_TEST is not set') with pytest.raises(pytds.OperationalError): pytds.connect(settings.HOST, auth=pytds.login.NtlmAuth(user_name='bad', password='******'))
def test_row_strategies(): kwargs = settings.CONNECT_KWARGS.copy() kwargs.update({ 'row_strategy': pytds.list_row_strategy, }) with connect(**kwargs) as conn: with conn.cursor() as cur: cur.execute("select 1") assert cur.fetchall() == [[1]] kwargs.update({ 'row_strategy': pytds.namedtuple_row_strategy, }) import collections with connect(**kwargs) as conn: with conn.cursor() as cur: cur.execute("select 1 as f") assert cur.fetchall() == [collections.namedtuple('Row', ['f'])(1)] kwargs.update({ 'row_strategy': pytds.recordtype_row_strategy, }) with connect(**kwargs) as conn: with conn.cursor() as cur: cur.execute("select 1 as e, 2 as f") row, = cur.fetchall() assert row.e == 1 assert row.f == 2 assert row[0] == 1 assert row[:] == (1, 2) row[0] = 3 assert row[:] == (3, 2)
def requires(self): plate_types = [] # connect to the RockMaker database conn = pytds.connect(self.server, self.database, self.username, self.password) c = conn.cursor() # find the directory names corresponding to plate types for all entries in the XChem folder # XChem # |- Plate Type (e.g. SwissSci3D) # |- Barcode # |- Data c.execute("SELECT TN3.Name as 'Name' From Plate " \ "INNER JOIN TreeNode TN1 ON Plate.TreeNodeID = TN1.ID " \ "INNER JOIN TreeNode TN2 ON TN1.ParentID = TN2.ID " \ "INNER JOIN TreeNode TN3 ON TN2.ParentID = TN3.ID " \ "INNER JOIN TreeNode TN4 ON TN3.ParentID = TN4.ID " \ "where TN4.Name='Xchem'") for row in c.fetchall(): plate_types.append(str(row[0])) # get all plate types plate_types = list(set(plate_types)) # lists to hold barcodes and plate types barcodes = [] plates = [] for plate in plate_types: # connect to the RockMaker DB conn = pytds.connect(self.server, self.database, self.username, self.password) c = conn.cursor() # For each plate type, find all of the relevant barcodes c.execute("SELECT Barcode FROM Plate " \ "INNER JOIN ExperimentPlate ep ON ep.PlateID = Plate.ID " \ "INNER JOIN ImagingTask it ON it.ExperimentPlateID = ep.ID " \ "INNER JOIN TreeNode as TN1 ON Plate.TreeNodeID = TN1.ID " \ "INNER JOIN TreeNode as TN2 ON TN1.ParentID = TN2.ID " \ "INNER JOIN TreeNode as TN3 ON TN2.ParentID = TN3.ID " \ "INNER JOIN TreeNode as TN4 ON TN3.ParentID = TN4.ID " \ "where TN4.Name='Xchem' AND TN3.Name like %s " \ "and it.DateImaged >= Convert(datetime, DATEADD(DD, -3, GETDATE()))", (str('%' + plate + '%'),)) rows = c.fetchall() for row in rows: # translate the name from RockMaker (UI) strange folders to 2drop or 3drop (in transfer parameter) if plate in self.translate.keys(): plates.append(self.translate[plate]) barcodes.append(str(row[0])) #else: #raise Exception(str(plate + ' definition not found in pipeline code or config file!')) # get all of the relevant info for every barcode (below) yield [GetBarcodeInfo(barcode=barcode, plate_type=plate) for (barcode, plate) in list(zip(barcodes, plates)) if barcode not in ['9557','954w', '9553', '956j', '956i', '955o']] yield [TransferImages(barcode=barcode, plate_type=plate_type, csv_file=os.path.join(os.getcwd(), str('barcodes_' + str(plate_type)), str(barcode + '.csv'))) for (plate_type, barcode) in list(zip(plates, barcodes)) if barcode not in ['9557','954w', '9553', '956j', '956i', '955o']]
def test_open_with_different_blocksize(): if not LIVE_TEST: pytest.skip('LIVE_TEST is not set') kwargs = settings.CONNECT_KWARGS.copy() # test very small block size kwargs['blocksize'] = 100 with pytds.connect(*settings.CONNECT_ARGS, **kwargs): pass # test very large block size kwargs['blocksize'] = 1000000 with pytds.connect(*settings.CONNECT_ARGS, **kwargs): pass
def test_invalid_parameters(self): with self.assertRaises(Error): with connect(server=settings.HOST + 'bad', database='master', user='******', password=settings.PASSWORD, login_timeout=1) as conn: with conn.cursor() as cur: cur.execute('select 1') with self.assertRaises(Error): with connect(server=settings.HOST, database='doesnotexist', user=settings.USER, password=settings.PASSWORD) as conn: with conn.cursor() as cur: cur.execute('select 1') with self.assertRaises(Error): with connect(server=settings.HOST, database='master', user='******', password=None) as conn: with conn.cursor() as cur: cur.execute('select 1')
def test_connection_closed_by_server(self): kwargs = settings.CONNECT_KWARGS.copy() kwargs['database'] = 'master' with connect(**kwargs) as master_conn: master_conn.autocommit = True with connect(**kwargs) as conn: conn.autocommit = False # test overall recovery with conn.cursor() as cur: cur.execute('select 1') conn.commit() kill(master_conn, get_spid(conn)) sleep(0.2) cur.execute('select 1') cur.fetchall() kill(master_conn, get_spid(conn)) sleep(0.2) with conn.cursor() as cur: cur.execute('select 1') # test cursor opening in a transaction, it should raise exception # make transaction dirty with conn.cursor() as cur: cur.execute('select 1') kill(master_conn, get_spid(conn)) sleep(0.2) # it does not have to raise this specific exception with pytest.raises(socket.error): with conn.cursor() as cur: cur.execute('select 1') # test recovery on transaction with conn.cursor() as cur: cur.execute('create table ##testtable3 (fld int)') kill(master_conn, get_spid(conn)) sleep(0.2) with self.assertRaises(Exception): cur.execute('select * from ##testtable2') cur.fetchall() conn.rollback() cur.execute('select 1') # test server closed connection on rollback with conn.cursor() as cur: cur.execute('select 1') kill(master_conn, get_spid(conn)) sleep(0.2) conn.rollback()
def test_with_sso(): if not LIVE_TEST: pytest.skip('LIVE_TEST is not set') with pytds.connect(settings.HOST, use_sso=True) as conn: with conn.cursor() as cursor: cursor.execute('select 1') cursor.fetchall()
def connection_String(self, connection_number): excel_sheet = os.getcwd() + '/Docs/info_data.xlsx' actual_path = os.path.abspath(excel_sheet) wb = load_workbook(actual_path) sheet_name = wb['sql_server'] server_name = sheet_name['A' + '' + format(connection_number)].value username = sheet_name['B' + '' + format(connection_number)].value password = sheet_name['C' + '' + format(connection_number)].value if connection_number == 1: Database_name = "AdventureWorks2014" database = "Source" elif connection_number == 2: Database_name = "AdventureWorks2014" database = "Destination" else: return False conn = pytds.connect(server_name, Database_name, username, password) try: print(database + " is connected with following address with ") print(server_name + "/" + username) print("The version of " + database + " side is " + str(conn.product_version)) except: raise ConnectionError self.conn = conn return self.conn
def update_db(self): # Format the timestamp to be in SQL Format dt = parser.parse(self.selected_data[self.configuration.db_timestamp_field]) # Adjust from UTC Time to Local Time of flag set if self.configuration.adjust_time: local_tz = get_localzone() dt = dt.replace(tzinfo=pytz.utc).astimezone(local_tz) dt = datetime.datetime.strftime(dt, "%Y%m%d %I:%M:%S %p") self.selected_data[self.configuration.db_timestamp_field] = dt fields = ",".join(str(x) for x in list(self.selected_data.keys())) values = ",".join("'" + x + "'" if isinstance(x, str) else str(x) for x in list(self.selected_data.values())) # Create insert statement as # INSERT INTO customers (Field1, Field2) VALUES ('value1',1) data_to_insert = "INSERT INTO {0} ({1}) VALUES ({2})".format(self.configuration.db_table, str(fields), str(values)) logger.debug("Running the following SQL Command: {0}".format(data_to_insert)) with pytds.connect(self.configuration.db_server, self.configuration.database, self.configuration.db_username, self.configuration.db_password) as conn: with conn.cursor() as cur: try: cur.execute(data_to_insert) except pytds.tds_base.IntegrityError as e: logger.error('Duplicate key Error') logger.error(e) conn.commit() conn.close()
def main(): conn = pytds.connect(dsn=os.getenv("HOST", "localhost"), user=os.getenv("SQLUSER", "sa"), password=os.getenv("SQLPASSWORD")) while True: try: sql = input("sql> ") except KeyboardInterrupt: return with conn.cursor() as cursor: try: cursor.execute(sql) except pytds.ProgrammingError as e: print("Error: " + str(e)) else: for _, msg in cursor.messages: print(msg.text) if cursor.description: print('\t'.join(col[0] for col in cursor.description)) print('-' * 80) count = 0 for row in cursor: print('\t'.join(str(col) for col in row)) count += 1 print('-' * 80) print("Returned {} rows".format(count)) print()
def test_closing_after_closed_by_server(self): """ You should be able to call close on connection closed by server """ kwargs = settings.CONNECT_KWARGS.copy() kwargs['database'] = 'master' kwargs['autocommit'] = True with connect(**kwargs) as master_conn: kwargs['autocommit'] = False with connect(**kwargs) as conn: with conn.cursor() as cur: cur.execute('select 1') conn.commit() kill(master_conn, get_spid(conn)) sleep(0.2) conn.close()
def test_sqlauth(self): with connect(settings.HOST, user=settings.USER, password=settings.PASSWORD) as conn: with conn.cursor() as cursor: cursor.execute('select 1') cursor.fetchall()
def main(): conn = pytds.connect(dsn=os.getenv("HOST", "localhost"), user=os.getenv("SQLUSER", "sa"), password=os.getenv("SQLPASSWORD"), cafile="/Users/denisenk/opensource/pytds/ca.pem", enc_login_only=True) while True: try: sql = input("sql> ") except KeyboardInterrupt: return with conn.cursor() as cursor: try: cursor.execute(sql) except pytds.ProgrammingError as e: print("Error: " + str(e)) else: for _, msg in cursor.messages: print(msg.text) if cursor.description: print('\t'.join(col[0] for col in cursor.description)) print('-' * 80) count = 0 for row in cursor: print('\t'.join(str(col) for col in row)) count += 1 print('-' * 80) print("Returned {} rows".format(count)) print()
def get_device_data(serials, host, user, password, db): if len(serials) == 1: barcode_cmd = "SELECT SerialNumber, CopyBarcode FROM CircCatAdmin.CopyAssetView WHERE SerialNumber = '{0}'".format( serials[0]) else: barcode_cmd = "SELECT SerialNumber, CopyBarcode FROM CircCatAdmin.CopyAssetView WHERE SerialNumber IN {}".format( tuple(serials)) db_host = host db_user = user db_password = password db_name = db try: with pytds.connect(db_host, database=db_name, user=db_user, password=db_password, as_dict=True) as conn: logging.debug("Server Connection Success") with conn.cursor() as cur: cur.execute(barcode_cmd) logging.debug("Lookup Command Executed") devicedata = (cur.fetchall()) logging.debug("Date retrieved, closing connection") except pytds.tds.LoginError: logging.error( "Unable to connect to server! Connection may have timed out!") sys.exit(2) cur.close() conn.close() return devicedata
def conn_tds(to=30, cfg=global_cfg): return pytds.connect(dsn=cfg.pyExec_host, database=cfg.pyExec_db, user=cfg.pyExec_uid, password=cfg.pyExec_upwd, port=cfg.pyExec_port, timeout=to)
def separate_db_connection(): if not LIVE_TEST: pytest.skip('LIVE_TEST is not set') kwargs = settings.CONNECT_KWARGS.copy() kwargs['database'] = 'master' conn = pytds.connect(*settings.CONNECT_ARGS, **kwargs) yield conn conn.close()
def test_invalid_block_size(): kwargs = settings.CONNECT_KWARGS.copy() kwargs.update({ 'blocksize': 4000, }) with connect(**kwargs) as conn: with conn.cursor() as cur: cur.execute_scalar("select '{}'".format('x' * 8000))
def test_readonly_connection(): kwargs = settings.CONNECT_KWARGS.copy() kwargs.update({ 'readonly': True, }) with connect(**kwargs) as conn: with conn.cursor() as cur: cur.execute_scalar("select 1")
def test_ntlm(self): conn = connect(settings.HOST, auth=pytds.login.NtlmAuth( user_name=os.getenv('NTLM_USER'), password=os.getenv('NTLM_PASSWORD'))) with conn.cursor() as cursor: cursor.execute('select 1') cursor.fetchall()
def test_as_dict(self): kwargs = settings.CONNECT_KWARGS.copy() kwargs['database'] = 'master' with connect(*settings.CONNECT_ARGS, **kwargs) as conn: conn.as_dict = True with conn.cursor() as cur: cur.execute('select 1 as a, 2 as b') self.assertDictEqual({'a': 1, 'b': 2}, cur.fetchone())
def test_instance_and_port(self): host = settings.HOST if '\\' in host: host, _ = host.split('\\') with self.assertRaisesRegexp(ValueError, 'Both instance and port shouldn\'t be specified'): with connect(server=host + '\\badinstancename', database='master', user=settings.USER, password=settings.PASSWORD, port=1212) as conn: with conn.cursor() as cur: cur.execute('select 1')
def connect_spec(self): return pytds.connect( dsn='storage', database=self.database, autocommit=True, user='******', password='******' )
def connect_with_pytds() -> pytds.Connection: return pytds.connect( db_hostname, # e.g. "127.0.0.1" user=db_user, # e.g. "my-database-user" password=db_pass, # e.g. "my-database-password" database=db_name, # e.g. "my-database-name" port=db_port, # e.g. 1433 bytes_to_unicode=False # disables automatic decoding of bytes )
def _get_connnection(self): return pytds.connect( user=self.user, password=self.password, dsn=self.host, port=self.port, as_dict=True, autocommit=True # .commit() doesn't work )
def test_connection_no_mars_no_pooling(): kwargs = settings.CONNECT_KWARGS.copy() kwargs.update({ 'use_mars': False, 'pooling': False, }) with connect(**kwargs) as conn: with conn.cursor() as cur: cur.execute("select 1") assert cur.fetchall() == [(1,)]
def db_make_secure_conn_obj(db_spec, as_dict=True, autocommit=True): ''' Returns a secure, encrypted db connection object. Caller is responsible for closing the connection. Might raise these exceptions: - NoDatabaseSpecifiedException - ConnectionNotSecureException (will close conn automatically in this case). - pytds.connect can also raise exceptions. ''' if 'database' not in db_spec: raise NoDatabaseSpecifiedException # Enforce TLS via `cafile` key-value pair. Note that pytds does not allow self-signed # certs so this will enforce cert verification as well. # See certifi documentation for more details on what `where()` does. db_spec['cafile'] = certifi.where() # Comment on 'login_timeout' and why we do both `test_conn` and `conn` below: # Create test_conn with default of 15 for login_timeout; however this also is # the connect_timeout and needs to be much larger in prod scenarios; so, # for actual conn, we'll use much larger value. # A connection that fails indicates certficate issue and long timeout in # that situation is not desirable (library will make retries). # Regarding login_timeout, see: https://github.com/denisenkom/pytds/issues/37 # It prevents random errors, which pytds otherwise seems to have an issue with. test_conn = pytds.connect(**db_spec) # if no exception thrown, so far so good. This means server's certificate is OK. # So, close test_conn and do actual conn with different login_timeout value. test_conn.close() conn = pytds.connect(**db_spec, as_dict=as_dict, autocommit=autocommit, login_timeout=1200) cur = conn.cursor() # Now, ensure encrypted connection. cur.execute(qy_check_conn_encrypted) rslt = cur.fetchone()['encrypt_option'] cur.close() if rslt != 'TRUE': # Conn not encrypted, so we bail. conn.close() raise ConnectionNotSecureException else: return conn
def test_connection_timeout_with_mars(): kwargs = settings.CONNECT_KWARGS.copy() kwargs['database'] = 'master' kwargs['timeout'] = 1 kwargs['use_mars'] = True with connect(*settings.CONNECT_ARGS, **kwargs) as conn: cur = conn.cursor() with pytest.raises(TimeoutError): cur.execute("waitfor delay '00:00:05'") cur.execute('select 1')
def _get_connnection(self): integration = self.config['integrations'][self.name] return pytds.connect( user=integration['user'], password=integration['password'], dsn=integration['host'], port=integration['port'], as_dict=True, autocommit=True # .commit() doesn't work )
def test_connection_no_mars_no_pooling(): kwargs = settings.CONNECT_KWARGS.copy() kwargs.update({ 'use_mars': False, 'pooling': False, }) with connect(**kwargs) as conn: with conn.cursor() as cur: cur.execute("select 1") assert cur.fetchall() == [(1, )]
def connect_spec(self): logger.info(f'Get connection: {self.dsn}') return pytds.connect(dsn=self.dsn, database=self.database, autocommit=True, user='******', password='******', port=1433)
def get_newop(self, op): query = "select numorp, qtdprv from e900cop where CodEmp = 1 AND CodOri = '04' AND NumOrp" \ "= {0}".format(op) rs = None try: with pytds.connect(self.server, self.database, self.user, self.password, timeout=2, login_timeout=2) as conn: with conn.cursor() as cursor: rs = cursor.execute(query).fetchone() except Exception as e: self.logger.error('Problema ao obter nova OP: ' + str(e)) return None, None, None if rs: query = """ SELECT QtdEmb FROM e900qdo, e075der where e900qdo.CodEmp = e075der.CodEmp and e900qdo.CodPro = e075der.CodPro and e900qdo.CodDer = e075der.CodDer and e900qdo.CodEmp = 1 and e900qdo.CodOri = '04' and e900qdo.NumOrp = {0} """.format(op) try: with pytds.connect(self.server, self.database, self.user, self.password, timeout=2, login_timeout=2) as conn: with conn.cursor() as cursor: qtde_fardo = list(cursor.execute(query).fetchone()) except Exception as e: self.logger.error('Problema ao alterar OP: ' + str(e)) return None, None, None return rs[0], int(rs[1]), int(qtde_fardo[0]) else: self.logger.warning('Nenhuma OP encontrada') return None, None, None
def test_connection_timeout_no_mars(): kwargs = settings.CONNECT_KWARGS.copy() kwargs.update({ 'use_mars': False, 'timeout': 1, 'pooling': True, }) with connect(**kwargs) as conn: with conn.cursor() as cur: with pytest.raises(TimeoutError): cur.execute("waitfor delay '00:00:05'") with conn.cursor() as cur: cur.execute("select 1") cur.fetchall() # test cancelling with conn.cursor() as cur: cur.execute('select 1') cur.cancel() assert cur.fetchall() == [] cur.execute('select 2') assert cur.fetchall() == [(2,)] # test rollback conn.rollback() # test callproc on non-mars connection with conn.cursor() as cur: cur.callproc('sp_reset_connection') with conn.cursor() as cur: # test spid property on non-mars cursor assert cur.spid is not None # test tzinfo_factory property r/w cur.tzinfo_factory = cur.tzinfo_factory # test non-mars cursor with connection pool enabled with connect(**kwargs) as conn: with conn.cursor() as cur: cur.execute('select 1') assert cur.fetchall() == [(1,)]
def test_connection_timeout_no_mars(): kwargs = settings.CONNECT_KWARGS.copy() kwargs.update({ 'use_mars': False, 'timeout': 1, 'pooling': True, }) with connect(**kwargs) as conn: with conn.cursor() as cur: with pytest.raises(TimeoutError): cur.execute("waitfor delay '00:00:05'") with conn.cursor() as cur: cur.execute("select 1") cur.fetchall() # test cancelling with conn.cursor() as cur: cur.execute('select 1') cur.cancel() assert cur.fetchall() == [] cur.execute('select 2') assert cur.fetchall() == [(2, )] # test rollback conn.rollback() # test callproc on non-mars connection with conn.cursor() as cur: cur.callproc('sp_reset_connection') with conn.cursor() as cur: # test spid property on non-mars cursor assert cur.spid is not None # test tzinfo_factory property r/w cur.tzinfo_factory = cur.tzinfo_factory # test non-mars cursor with connection pool enabled with connect(**kwargs) as conn: with conn.cursor() as cur: cur.execute('select 1') assert cur.fetchall() == [(1, )]
def create_report(self, worksheet_name=None): # Connect to database, create cursor, execute query, write results to xlsx file: with pytds.connect(dsn=self.credentials['dsn'], database=self.credentials['database'], user=self.credentials['user'], password=self.credentials['password'], as_dict=True) as conn: with conn.cursor() as c: c.execute(self.sql) self.create_xlsx(c.fetchall(), worksheet_name)
def run(self): while True: try: auth_obj = None logger.info("Connecting to database {server}:{port}/{database} as {user}".format(server=self.server, port=self.port, database=self.database_name, user=self.user)) # Handle NTML Authentication -- assume that's what they want if username has a domain. Otherwise use normal auth if '\\' in self.user: logger.info("Logging in via NTLM") auth_obj = pytds.login.NtlmAuth (self.user, self.password) with pytds.connect(self.server, self.database_name, self.user, self.password, port=self.port, auth=auth_obj) as conn: while True: with conn.cursor() as cursor: last_parse = self.parsing_state.get_last_parse() logger.debug( "Last parse: " + str(last_parse)) #cursor.execute('SELECT read_id, plate, device_id, read_date, lat, lon FROM reads WHERE read_date > 2010-01-01 ORDER BY read_date asc LIMIT 1000') cursor.execute('SELECT TOP 1000 read_id, plate, device_id, read_date, lat, lon FROM reads WHERE read_date > %s ORDER BY read_date ASC', (last_parse,)) results = cursor.fetchall() if len(results) == 0: # No results, sleep for a while logger.debug ("No results. Sleeping...") time.sleep(5) continue logger.info("Grabbed {count} results from db starting from time: {time}".format(count=len(results), time=last_parse)) for row in results: read_id = row[0] plate = row[1] camera = str(row[2]) read_date = row[3] lat = row[4] lng = row[5] if read_date > last_parse: self.parsing_state.set_last_parse(read_date) self.process_read(conn, read_id, plate, camera, read_date, lat, lng) # Now select each image for the given row self.parsing_state.save() except (pytds.tds_base.Error, socket.error, Exception) as e: logger.exception(e) logger.warn("Error connecting to database. Retrying in 15 seconds...") time.sleep(15)
def test_instance_and_port(self): host = settings.HOST if '\\' in host: host, _ = host.split('\\') with self.assertRaisesRegexp( ValueError, 'Both instance and port shouldn\'t be specified'): with connect(server=host + '\\badinstancename', database='master', user=settings.USER, password=settings.PASSWORD, port=1212) as conn: with conn.cursor() as cur: cur.execute('select 1')
def connect(server, database, port, user, password, timeout=30, use_transactions=None, use_mars=False, readonly=False): """Connect to a database. timeout -- A command timeout value, in seconds (default 30 seconds) """ autocommit = not use_transactions c = pytds.connect(server=server, database=database, port=port, user=user, password=password, timeout=timeout, autocommit=autocommit, use_mars=use_mars, readonly=readonly) return c
def test_connection_no_mars_autocommit(): kwargs = settings.CONNECT_KWARGS.copy() kwargs.update({ 'use_mars': False, 'timeout': 1, 'pooling': True, 'autocommit': True, }) with connect(**kwargs) as conn: with conn.cursor() as cur: # test execute scalar with empty response cur.execute_scalar('declare @tbl table(f int); select * from @tbl') cur.execute("print 'hello'") messages = cur.messages assert len(messages) == 1 assert len(messages[0]) == 2 # in following assert exception class does not have to be exactly as specified assert messages[0][0] == pytds.OperationalError assert messages[0][1].text == 'hello' assert messages[0][1].line == 1 assert messages[0][1].severity == 0 assert messages[0][1].number == 0 assert messages[0][1].state == 1 assert 'hello' in messages[0][1].message # test cursor usage after close, should raise exception cur = conn.cursor() cur.execute_scalar('select 1') cur.close() with pytest.raises(Error) as ex: cur.execute('select 1') assert 'Cursor is closed' in str(ex) # calling get_proc_return_status on closed cursor works # this test does not have to pass assert cur.get_proc_return_status() is None # calling rowcount on closed cursor works # this test does not have to pass assert cur.rowcount == -1 # calling description on closed cursor works # this test does not have to pass assert cur.description is None # calling messages on closed cursor works # this test does not have to pass assert cur.messages is None # calling description on closed cursor works # this test does not have to pass assert cur.native_description is None
def runTest(self): kwargs = settings.CONNECT_KWARGS.copy() use_tz = utc kwargs['use_tz'] = use_tz kwargs['database'] = 'master' with connect(*settings.CONNECT_ARGS, **kwargs) as conn: # Naive time should be interpreted as use_tz self.check_val(conn, '%s', datetime(2011, 2, 3, 10, 11, 12, 3000), datetime(2011, 2, 3, 10, 11, 12, 3000, utc)) # Aware time shoule be passed as-is dt = datetime(2011, 2, 3, 10, 11, 12, 3000, tzoffset(1)) self.check_val(conn, '%s', dt, dt) # Aware time should be converted to use_tz if not using datetimeoffset type dt = datetime(2011, 2, 3, 10, 11, 12, 3000, tzoffset(1)) if IS_TDS73_PLUS(conn): self.check_val(conn, 'cast(%s as datetime2)', dt, dt.astimezone(use_tz))
def create_test_database(): if not LIVE_TEST: return logger.info('in setup class') kwargs = settings.CONNECT_KWARGS.copy() kwargs['database'] = 'master' kwargs['autocommit'] = True with connect(**kwargs) as conn: with conn.cursor() as cur: try: cur.execute('drop database [{0}]'.format(settings.DATABASE)) except: logger.exception('Failed to drop database') pass try: cur.execute('create database [{0}]'.format(settings.DATABASE)) except: pass try: cur.execute('create schema myschema') except: pass try: cur.execute('create table myschema.bulk_insert_table(num int, data varchar(100))') except: pass try: cur.execute(''' create procedure testproc (@param int, @add int = 2, @outparam int output) as begin set nocount on --select @param set @outparam = @param + @add return @outparam end ''') except: pass
def execute_sql_file( server, login_id, password, database, input_file, ): dbconn = pytds.connect( server=server, database=database, user=login_id, password=password, autocommit=True, appname="python-sqlcmd", row_strategy=pytds.namedtuple_row_strategy, ) cursor = dbconn.cursor() for msgclass, msg in cursor.messages: print msg.message print "Executing", input_file with open(input_file, "r") as sqlfile: sqltext = sqlfile.read() blocks = sqltext.split("\nGO\n") # Strip trailing GO statement if not blocks[-1]: del blocks[-1] for i, block in enumerate(blocks): print ">>> executing block {} of {}".format(i + 1, len(blocks)) ##print block cursor.execute(block) for msgclass, msg in cursor.messages: print msg.message
def __init__(self): self.conn = pytds.connect(dsn='127.0.0.1', user='******', password='******', database='master', autocommit=True)
def setUp(self): kwargs = settings.CONNECT_KWARGS.copy() kwargs['bytes_to_unicode'] = False kwargs['database'] = 'master' self.conn = connect(*settings.CONNECT_ARGS, **kwargs)
def setUp(self): kwargs = settings.CONNECT_KWARGS.copy() kwargs['database'] = 'master' kwargs['use_mars'] = False self.conn = connect(*settings.CONNECT_ARGS, **kwargs)
def db_connection(): if not LIVE_TEST: pytest.skip('LIVE_TEST is not set') kwargs = settings.CONNECT_KWARGS.copy() kwargs['database'] = 'master' return pytds.connect(*settings.CONNECT_ARGS, **kwargs)
def test_ntlm(self): conn = connect(settings.HOST, auth=pytds.login.NtlmAuth(user_name=os.getenv('NTLM_USER'), password=os.getenv('NTLM_PASSWORD'))) with conn.cursor() as cursor: cursor.execute('select 1') cursor.fetchall()
def test_open_close(self): for x in xrange(3): kwargs = settings.CONNECT_KWARGS.copy() kwargs['database'] = 'master' connect(**kwargs).close()
def test_sspi(self): from pytds.login import SspiAuth with connect(settings.HOST, auth=SspiAuth()) as conn: with conn.cursor() as cursor: cursor.execute('select 1') cursor.fetchall()
def setUp(self): kwargs = settings.CONNECT_KWARGS.copy() kwargs['database'] = 'master' kwargs['tds_version'] = pytds.tds_base.TDS71 self.conn = connect(*settings.CONNECT_ARGS, **kwargs)