Python pyodbc.connect() Examples

def connection_maker(loop, dsn):
    cleanup = []

    async def make(**kw):
        if kw.get('executor', None) is None:
            executor = ThreadPoolExecutor(max_workers=1)
            kw['executor'] = executor
        else:
            executor = kw['executor']

        conn = await aioodbc.connect(dsn=dsn, loop=loop, **kw)
        cleanup.append((conn, executor))
        return conn

    try:
        yield make
    finally:
        for conn, executor in cleanup:
            await conn.close()
            executor.shutdown(True) 

def __init__(self, *args, **kwargs):
            QtGui.QComboBox.__init__(self)
            Custom.__init__(self, *args, **kwargs)
            self.currentIndexChanged.connect(self.interactivechange) 

def connect_to_database(driver,host, database, user, password):
    try:
        connection = pyodbc.connect('DRIVER='+driver+';SERVER='+host+';DATABASE='+database+';UID='+user+';PWD='+ password)
        return connection
    except Exception as e:
        print ("Error while connecting to database", e) 

def setUp(self):
        if sys.version_info[0:2] != expected_version:
            self.skipTest('createScript expects Python %s' % '.'.join(map(str, expected_version)))
        self.odbc_kwargs = {
                #'DSN':'EXtest_start_external_service_get_dataAODBC_TEST',
                'Driver': 'EXAODBC',
                'EXAHOST': os.environ['ODBC_HOST'],
                'EXAUID': os.environ['EXAUSER'],
                'EXAPWD': os.environ['EXAPW']
                }
        if 'ODBC_LOG' in os.environ:
            self.odbc_kwargs['LOGFILE'] = os.environ['ODBC_LOG']

        with pyodbc.connect(**self.odbc_kwargs) as con:
            try:
                con.cursor().execute('CREATE SCHEMA foo')
            except pyodbc.ProgrammingError:
                # schema FOO exists
                pass 

def test_redirect_output_given_port(self):
        buffer = StringIO()
        with exasol.connect(
                clientAddress=(None, self.port),
                outputFile=buffer,
                scriptSchema='foo',
                useCSV=True,
                **self.odbc_kwargs) as ecn:

            @ecn.createScript(**self.script_kwargs)
            def echo(ctx):
                print(ctx.a)
                return 'no output'

            out = echo("'foobar'", table='dual')

        self.assertEqual('no output', out[0][0])
        self.assertIn('foobar', buffer.getvalue()) 

def test_start_external_service_get_data(self):
        eos = ExecBackground(self.interpreter, self.exatoolbox, '--port', str(self.port))
        eos.start()
        time.sleep(5)
        buffer = StringIO()
        with exasol.connect(
                clientAddress=(None, self.port),
                externalClient=True,
                outputFile=buffer,
                scriptSchema='foo',
                useCSV=True,
                **self.odbc_kwargs) as ecn:

            @ecn.createScript(**self.script_kwargs)
            def echo(ctx):
                print(ctx.a)
                return 'no output'

            out = echo("'foobar'", table='dual')
        eos.stop()

        self.assertEqual('no output', out[0][0])
        self.assertEqual('', buffer.getvalue())
        self.assertIn('foobar', eos.output) 

def _build_deploy_mssql_tables(server, table_name):
    """
    Using pyodbc directly, create a table on the given server.
    Args:
        server (str): the server name
        table_name (str): the table name
    """
    conn = pyodbc.connect(
        "driver={SQL Server Native Client 11.0};server=" + server + "; database=master; trusted_connection=yes;",
        autocommit=True)

    query = """
        CREATE
        TABLE {} (
            [BindingID][int],
            [BindingNM][varchar](255),
            [LastLoadDTS][datetime2](7),
            [PatientEncounterID][decimal](38, 0),
        [PredictedValueNBR][decimal](38, 2),
        [Factor1TXT][varchar](255),
        [Factor2TXT][varchar](255),
        [Factor3TXT][varchar](255));""".format(table_name)

    cursor = conn.execute(query)
    cursor.close() 

def test_redirect_output_anyport(self):
        buffer = StringIO()
        with exasol.connect(clientAddress=(None, 0),
                            outputFile=buffer,
                            scriptSchema='foo',
                            useCSV=True,
                            **self.odbc_kwargs) as ecn:

            @ecn.createScript(**self.script_kwargs)
            def echo(ctx):
                print(ctx.a)
                return 'no output'

            out = echo("'foobar'", table='dual')

        self.assertEqual('no output', out[0][0])
        self.assertIn('foobar', buffer.getvalue()) 

def test_created_script_is_persistent(self):
        with exasol.connect(**self.odbc_kwargs) as ecn:
            ecn.execute('OPEN SCHEMA foo')

            @ecn.createScript(
                    inArgs=[('a', INT)],
                    outArgs=[('a', INT)],
                    )
            def foobar(ctx):
                ctx.emit(4)

            ecn.commit()

        with pyodbc.connect(**self.odbc_kwargs) as con:
            self.assertEqual(1, len(con.cursor().execute(
                 "SELECT * FROM EXA_ALL_SCRIPTS WHERE script_name = 'FOOBAR'").fetchall())) 

def read_auto_connect():
	"""
---------------------------------------------------------------------------
Automatically creates a connection using the one created when using the 
function new_auto_connection.

Returns
-------
conn
	Database connection

See Also
--------
new_auto_connection : Saves a connection to automatically create DB cursors.
vertica_cursor      : Creates a Vertica Database cursor using the input method.
	"""
	path = os.path.dirname(verticapy.__file__) + "/connections"
	try:
		file = open(path + "/auto_connection", "r")
		name = file.read()
		path += "/all/{}.vertica".format(name)
		file = open(path, "r")
	except:
		raise Exception("No auto connection is available. To create an auto connection, use the new_auto_connection function of the verticapy.connections.connect module.")
	dsn = file.read()
	dsn = dsn.split("\n")
	if (dsn[0] == "vertica_python"):
		import vertica_python
		conn = vertica_python.connect(** {"host": dsn[1], "port": dsn[2], "database": dsn[3], "user": dsn[4], "password": dsn[5]}, autocommit = True)
	elif (dsn[0] == "pyodbc"):
		import pyodbc
		conn = pyodbc.connect(dsn[1], autocommit = True)
	elif (dsn[0] == "jaydebeapi"):
		import jaydebeapi
		jdbc_driver_name = "com.vertica.jdbc.Driver"
		jdbc_driver_loc = os.path.dirname(verticapy.__file__) + "/connections/vertica-jdbc-9.3.1-0.jar"
		conn = jaydebeapi.connect(jdbc_driver_name, dsn[1], {'user': dsn[2], 'password': dsn[3]}, jars = jdbc_driver_loc)
	else:
		raise Exception("The auto connection format is incorrect. To create a new auto connection, use the new_auto_connection function of the verticapy.connections.connect module.")
	return(conn)
#---# 

def __getConnection(self):
        if (self.__connection == None):
            application_name = ";APP={0}".format(socket.gethostname())  
            self.__connection = pyodbc.connect(os.environ['SQLAZURECONNSTR_WWIF'] + application_name)                  
        
        return self.__connection 

def get_connection(self, db_name=None):
        connstr = """DRIVER=ODBC Driver 17 for SQL Server;SERVER={0},{1};UID={2};PWD={3};
client charset = UTF-8;connect timeout=10;CHARSET={4};""".format(self.host, self.port, self.user, self.password,
                                                                 self.instance.charset or 'UTF8')
        if self.conn:
            return self.conn
        self.conn = pyodbc.connect(connstr)
        return self.conn 

def connect(self):
        if not IS_WIN:
            errMsg = "currently, direct connection to Microsoft Access database(s) "
            errMsg += "is restricted to Windows platforms"
            raise SqlmapUnsupportedFeatureException(errMsg)

        self.initConnection()
        self.checkFileDb()

        try:
            self.connector = pyodbc.connect('Driver={Microsoft Access Driver (*.mdb)};Dbq=%s;Uid=Admin;Pwd=;' % self.db)
        except (pyodbc.Error, pyodbc.OperationalError), msg:
            raise SqlmapConnectionException(getSafeExString(msg)) 

def check_type(self, type_):
        with pyodbc.connect(**self.odbc_kwargs) as con:
            rows = con.cursor().execute(dedent("""\
                    SELECT script_text
                    FROM EXA_ALL_SCRIPTS
                    WHERE script_name = 'FOOBAR'
                    """)).fetchall()
        self.assertIn('("a" %s) EMITS' % type_, rows[0][0]) 

def DoImport(self):
        if not self.InitEnvironment():
            self.target_doc.ChangeState(self.target_doc.state_importfailed)
            return

        @public.wth(tm.ext.importing_doc.format(self.target_doc.name), self.target_doc)
        def f():

            @public.mth
            def f2():
                if not self.iiptpl_ver1.CanView() or not self.iiptpl_ver2.CanView() or not self.mmttpl_ver.CanView():
                    self.Clear()
                    self.target_doc.ChangeState(self.target_doc.state_importfailed)
                    return

                @public.wth(tm.ext.importing_doc.format(self.target_doc.name), self.target_doc)
                def f3():                
                    try:
                        DRV = '{Microsoft Access Driver (*.mdb)}'
                        PWD = 'pw'
                        self.con = pyodbc.connect('DRIVER={};DBQ={};PWD={}'.format(DRV,self.source_path,PWD))
                        self.cur = self.con.cursor()
                        self.ProcessModel()
                        @public.mth
                        def f4():
                            self.Clear()
                            self.target_doc.ChangeState(self.target_doc.state_changed)
                    except:
                        log.exception()
                        @public.mth
                        def f5():
                            self.Clear()
                            self.target_doc.ChangeState(self.target_doc.state_importfailed) 

def test_check_invalid_password(aggregator, init_config, instance_docker):
    instance_docker['password'] = 'FOO'

    sqlserver_check = SQLServer(CHECK_NAME, init_config, [instance_docker])

    with pytest.raises(SQLConnectionError) as excinfo:
        sqlserver_check.check(instance_docker)
        assert excinfo.value.args[0] == 'Unable to connect to SQL Server'
    aggregator.assert_service_check(
        'sqlserver.can_connect',
        status=sqlserver_check.CRITICAL,
        tags=['host:localhost,1433', 'db:master', 'optional:tag1'],
    ) 

def setup_db_connect(driver,name):
    """Returns a function object that can be used to connect to the DB. The function doesn't require any additional parameters as it stores the DB name/connection string using closure."""
    logger.info('Using %s DB with DSN=%s',driver,name)
    def connect_sqlite():
        con = sqlite3.connect(name,isolation_level=None)
        con.execute('PRAGMA journal_mode=WAL')
        return con
    def connect_odbc():
        return pyodbc.connect(name)
    if driver == 'sqlite':
        import sqlite3
        return connect_sqlite
    elif driver == 'odbc':
        import pyodbc
        return connect_odbc 

def test_raise_typeerror_no_table(self):
        with exasol.connect(useCSV=True, **self.odbc_kwargs) as ecn:
            ecn.execute('OPEN SCHEMA foo')

            @ecn.createScript(inArgs=[('dummy', INT)],
                              outArgs=[('dummy', INT)])
            def foo(dummy):
                return

            with self.assertRaises(TypeError):
                foo(42) 

def check_typespec(self, inargs='a', outargs='a'):
        with pyodbc.connect(**self.odbc_kwargs) as con:
            rows = con.cursor().execute(dedent("""\
                    SELECT script_text
                    FROM EXA_ALL_SCRIPTS
                    WHERE script_name = 'FOOBAR'
                    """)).fetchall()
        typespec = (
                '(' +
                ', '.join(['"%s" DOUBLE' % x for x in inargs]) +
                ') EMITS (' +
                ', '.join(['"%s" DOUBLE' % x.upper() for x in outargs]) +
                ') AS')

        self.assertIn(typespec, rows[0][0]) 

def create_script(self, inargs='a', outargs='a'):
        with exasol.connect(**self.odbc_kwargs) as ecn:
            ecn.execute('OPEN SCHEMA foo')

            @ecn.createScript(
                    name="foobar",
                    inArgs=[(x, DOUBLE) for x in inargs],
                    outArgs=[(x, DOUBLE) for x in outargs],
                    )
            def foo(_):
                pass
            ecn.commit() 

def test_reraise_correct_exception_without_outputservice(self):
        with self.assertRaises(ZeroDivisionError):
            with exasol.connect(**self.odbc_kwargs) as _:
                raise ZeroDivisionError('Boom!') 

def create_script(self, type_):
        with exasol.connect(**self.odbc_kwargs) as ecn:
            ecn.execute('OPEN SCHEMA foo')

            @ecn.createScript(
                    name="foobar",
                    inArgs=[('a', type_)],
                    outArgs=[('a', INT)],
                    )
            def foo(_):
                pass
            ecn.commit() 

def test_createScript_works_scalar_returns(self):
        with exasol.connect(useCSV=True, **self.odbc_kwargs) as ecn:
            ecn.execute('OPEN SCHEMA foo')

            @ecn.createScript(
                    inType=SCALAR,
                    inArgs=[('a', DOUBLE)],
                    outType=RETURNS,
                    outArgs=INT,
                    )
            def foo(ctx):
                return int(ctx.a)

            self.assertEqual([['3']], foo(3.4, table='dual')) 

def test_createScript_works_set_emits(self):
        with exasol.connect(useCSV=True, **self.odbc_kwargs) as ecn:
            ecn.execute('OPEN SCHEMA foo')

            @ecn.createScript(
                    inArgs=[('a', DOUBLE)],
                    outArgs=[('a', INT)],
                    )
            def foo(ctx):
                ctx.emit(int(ctx.a))

            self.assertEqual([['3']], foo(3.4, table='dual')) 

def test_createScript_name_overwrites_schema_default(self):
        with exasol.connect(scriptSchema='babelfish', **self.odbc_kwargs) as ecn:
            @ecn.createScript(name='foo.baz', inArgs=[('a', INT)], outArgs=[('a', INT)])
            def bar(_):
                pass

            rows = ecn.cursor().execute(dedent("""\
                    SELECT *
                    FROM EXA_ALL_SCRIPTS
                    WHERE script_name = 'BAZ' and
                        script_schema = 'FOO'
                    """)).fetchall()
        self.assertEqual(1, len(rows)) 

def test_createScript_schema_default(self):
        with exasol.connect(scriptSchema='foo', **self.odbc_kwargs) as ecn:
            @ecn.createScript(inArgs=[('a', INT)], outArgs=[('a', INT)])
            def bar(_):
                pass

            rows = ecn.cursor().execute(dedent("""\
                    SELECT *
                    FROM EXA_ALL_SCRIPTS
                    WHERE script_name = 'BAR' and
                        script_schema = 'FOO'
                    """)).fetchall()
        self.assertEqual(1, len(rows)) 

def test_createScript_overwrite_default(self):
        with exasol.connect(**self.odbc_kwargs) as ecn:
            ecn.execute('OPEN SCHEMA foo')

            @ecn.createScript(inArgs=[('a', INT)], outArgs=[('a', INT)])
            def foo(ctx):
                ctx.emit(int(ctx.a))

            self.assertIsInstance(
                    foo(3.4, table='dual',
                        readCallback=exasol.csvReadCallback),
                    list) 

def test_createScript_defaults_to_pandas(self):
        with exasol.connect(**self.odbc_kwargs) as ecn:
            ecn.execute('OPEN SCHEMA foo')

            @ecn.createScript(inArgs=[('a', INT)], outArgs=[('a', INT)])
            def foo(ctx):
                ctx.emit(int(ctx.a))

            self.assertIsInstance(foo(3.4, table='dual'), pandas.DataFrame) 

def tearDown(self):
        with pyodbc.connect(**self.odbc_kwargs) as con:
            con.cursor().execute('DROP SCHEMA foo CASCADE') 

def __enter__(self):
        """Allows to use E.connect in "with" statements"""
        if not self._connected:
            raise pyodbc.ProgrammingError("Not connected")
        return self