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 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_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_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 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 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 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_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 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_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_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 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 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 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_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_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 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)
