def main():
    print("write the path to the folder where the mdb file will be outputed to.(write it as C:/.../.../.../")
    orig_path=input()
    path_list=glob.glob(orig_path+'/*.mdb')
    for i in path_list:
        print(i)
    #find the driver
    DataSource=pyodbc.dataSources()
    #for i in DataSource:
    #    print(i)
    access_driver=pyodbc.dataSources()['MS Access Database']
    printit(path_list,access_driver,orig_path)
    proc(path_list,access_driver,orig_path)
    while(True):
        a="a" #a line for stalling
def show_odbc_sources():
	sources = pyodbc.dataSources()
	dsns = sources.keys()
	sl = []
	for dsn in dsns:
		sl.append('%s [%s]' % (dsn, sources[dsn]))
	print('\n'.join(sl))
 def getDataSourceNames(cls):
     result = grt.List(grt.STRING)
     import pyodbc
     sources = pyodbc.dataSources()
     for key, value in list(sources.items()):
         result.append("%s|%s (%s)" % (key, key, value))
     return result
Example #4
0
    def __init__(self):
        super().__init__()
        gui.label(self.controlArea, self, "from pandas:")
        self.setSizePolicy(QSizePolicy.Maximum, QSizePolicy.Maximum)
        self.allSQLSelectWidgets.append(self)  # set default settings
        self.domain = None
        self.recentConnections = list()
        self.recentConnections.append("(none)")

        self.queryFile = None
        self.query = ""
        self.lastQuery = None
        if self.lastQuery is not None:
            self.query = self.lastQuery

        sources = pyodbc.dataSources()
        dsns = list(sources.keys())
        dsns.sort()

        for dsn in dsns:
            self.recentConnections.append("DSN={dsn}".format(dsn=dsn))

        self.connectString = self.recentConnections[0]

        self.connectBox = gui.widgetBox(self.controlArea, "Database")

        self.connectLineEdit = gui.lineEdit(self.connectBox, self, "connectString", callback=None)
        self.connectCombo = gui.comboBox(
            self.connectBox, self, "connectString", items=self.recentConnections, valueType=str, sendSelectedValue=True
        )
        self.button = gui.button(self.connectBox, self, "connect", callback=self.connectDB, disabled=0)
        # query
        self.splitCanvas = QSplitter(QtCore.Qt.Vertical, self.mainArea)
        self.mainArea.layout().addWidget(self.splitCanvas)

        self.textBox = gui.widgetBox(self, "HiveQL")
        self.splitCanvas.addWidget(self.textBox)
        self.queryTextEdit = QPlainTextEdit(self.query, self)
        self.textBox.layout().addWidget(self.queryTextEdit)

        self.selectBox = gui.widgetBox(self.controlArea, "Select statement")
        # self.selectSubmitBox = QHGroupBox("", self.selectBox)
        # self.queryTextEdit.setSizePolicy(QSizePolicy(QSizePolicy.Preferred, QSizePolicy.Preferred))
        # self.queryTextEdit.setMinimumWidth(300)
        # self.connect(self.queryTextEdit, SIGNAL('returnPressed()'), self.executeQuery)
        gui.button(self.selectBox, self, "Open...", callback=self.openScript)
        gui.button(self.selectBox, self, "Save...", callback=self.saveScript)
        self.selectBox.setSizePolicy(QSizePolicy(QSizePolicy.MinimumExpanding, QSizePolicy.MinimumExpanding))
        gui.button(self.selectBox, self, "format SQL!", callback=self.format_sql, disabled=0)
        gui.button(self.selectBox, self, "execute!", callback=self.executeQuery, disabled=0)
        self.domainBox = gui.widgetBox(self.controlArea, "Domain")
        self.domainLabel = gui.label(self.domainBox, self, "")
        # info
        self.infoBox = gui.widgetBox(self.controlArea, "Info")
        self.info = []
        self.info.append(gui.label(self.infoBox, self, "No data loaded."))
        self.info.append(gui.label(self.infoBox, self, ""))
        self.resize(300, 300)

        self.cnxn = None
def getDataSourceNames():
    result = grt.List(grt.STRING)
    import pyodbc
    sources = pyodbc.dataSources()
    for key, value in sources.items():
        result.append("%s|%s (%s)" % (key, key, value))
    return result
Example #6
0
    def check_data_sources(self):
	sources = pyodbc.dataSources()
	dsns = sources.keys()
	dsns.sort()
	for dsn in dsns:
	    if self.env['migration.import.odbc.dsn'].search_count([('name','=',dsn)]) == 0:
		self.env['migration.import.odbc.dsn'].create({'name': dsn, 'driver': sources[dsn]})
Example #7
0
def DatabaseEnvParams(processId):
    # TODO: We could use the process id to check if the process executable is linked
    # with the SQLServer shareable library.

    # We do not list sources in lib_credentials because some ODBC sources
    # can be accessed without pass word (With Windows validation).
    sources = pyodbc.dataSources()
    # {
    # 	'MyNativeSqlServerDataSrc': 'SQL Server Native Client 11.0',
    # 	'Excel Files': 'Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)',
    # 	'SqlSrvNativeDataSource': 'SQL Server Native Client 11.0',
    # 	'mySqlServerDataSource': 'SQL Server',
    # 	'MyOracleDataSource': 'Oracle in XE',
    # 	'SysDataSourceSQLServer': 'SQL Server',
    # 	'dBASE Files': 'Microsoft Access dBASE Driver (*.dbf, *.ndx, *.mdx)',
    # 	'OraSysDataSrc' : 'Oracle in XE',
    # 	'MS Access Database': 'Microsoft Access Driver (*.mdb, *.accdb)'
    # }

    dsnList = ({survol_odbc.CgiPropertyDsn(): "DSN=" + dsn} for dsn in sources)

    # Maybe this must be adjusted as key-value pairs ??
    return ("sqlserver/query", dsnList)


# TODO: Add a link to https://sqlwebadmin.codeplex.com/ sqlwebadmin
Example #8
0
def show_odbc_sources():
	sources = pyodbc.dataSources()
	dsns = sources.keys()
	dsns.sort()
	sl = []
	for dsn in dsns:
		sl.append('%s [%s]' % (dsn, sources[dsn]))
	print('\n'.join(sl))
 def openODBCmanager(self):
     try:
         subprocess.call(["odbcad32.exe"])
         self.connections = pyodbc.dataSources()
         self.connectionsComboBox.clear()
         self.fillcombobox()
         self.setpassword()
     except Exception as exc:
         warningMessage("Could not open ODBC Manager", unicode(exc.args))
Example #10
0
 def check_for_dsn(self, dsn: str) -> bool:
 #   Perform a case-insensitive search for the dsn name.
     if dsn.lower() in [k.lower() for k in pyodbc.dataSources().keys()]:
         return True
     else:
         err = 'The dsn ' + dsn + ' was not found.'
         self.logger.error(err)
         self.status = err
         return False
Example #11
0
def show_odbc_sources():
    
    src = pyodbc.dataSources()
    dsns = list(src.keys())
    dsns.sort()
    sl = []
    for dsn in dsns:
        sl.append('%s [%s]' % (dsn, src[dsn]))
    print('\n'.join(sl))
Example #12
0
def verify_dsn_source():
    sources = pyodbc.dataSources()
    dsns = sources.keys()
    data_source_list = []
    for key in dsns:
        data_source_list.append(key)
    if data_source_list.count("Delta ODBC 4") == 1:
        return True
    elif data_source_list.count("Delta ODBC 4") == 0:
        return False
Example #13
0
    def list_values(self, connection_alias, connection_type, list_value):
        """
        commit the results from a previous command.  This is always used in conjunction
        with the execute_command() function, either implicitly or explicitly.

        :param connection_alias: the plain english name to associate with this connection
        :param connection_type: the type of connection to use
        :param list_value: the thing inside the database to list

        """

        logging.debug(" -- Working with connection '%s' of type '%s'" %
                      (connection_alias, connection_type))

        if connection_type.lower() == 'odbc':

            if not self.is_connected(
                    connection_alias) and list_value != 'sources':
                logging.debug(" --- The alias '%s' is not connected" %
                              connection_alias)
                return
            try:
                logging.debug(" --- The list value is '%s'" % list_value)
                results = []
                if list_value == 'tables':
                    logging.debug(" -- Listing tables in '%s'" %
                                  connection_alias)
                    cursor = self._conn_info[connection_alias]['cursor']
                    for row in cursor.tables():
                        results.append(row)
                elif list_value == 'procedures':
                    logging.debug(" --- Listing procedures in '%s'" %
                                  connection_alias)
                    cursor = self._conn_info[connection_alias]['cursor']
                    for row in cursor.procedures():
                        results.append(row)
                elif list_value == 'sources':
                    logging.debug(" --- Listing sources in '%s'" %
                                  connection_alias)

                    for row in pyodbc.dataSources():
                        results.append(row)
                else:
                    raise Exception(
                        stack()[0][3],
                        "The list value provided ('%s') is not valid.  No connection made"
                        % list_value)

                logging.debug(" --- Listing '%s'" % results)
                return results

            except pyodbc.Error, err:
                logging.error(err)
                raise err
def show_odbc_sources():
    """
    displaying the available database 
    on the system
    """
    sources = pyodbc.dataSources()
    dsns = sources.keys()
    dsns.sort()
    sl = []
    for dsn in dsns:
        sl.append('%s [%s]' % (dsn, sources[dsn]))
    print('\n'.join(sl))
Example #15
0
def display_data_sources(grph):
    sources = pyodbc.dataSources()

    for dsn in sources:
        driver = sources[dsn]

        DEBUG("dsn=%s driver=%s", dsn, driver)

        # This creates a connection string.
        nodeDsn = survol_odbc_dsn.MakeUri("DSN=" + dsn)
        grph.add((lib_common.nodeMachine, pc.property_odbc_dsn, nodeDsn))
        grph.add(
            (nodeDsn, pc.property_odbc_driver, lib_common.NodeLiteral(driver)))
Example #16
0
    def __init__(self):
        QtGui.QWizardPage.__init__(self)
        self.setCommitPage(False)
        self.setTitle("Datasource")
        self.setSubTitle("Please select a datasource")

        self.sourceCombo = QtGui.QComboBox()
        sources = [source for source in pyodbc.dataSources().keys()]
        self.sourceCombo.addItems(sources)

        layout = QtGui.QGridLayout()
        layout.addWidget(self.sourceCombo, 0, 0)
        self.setLayout(layout)
    def __init__(self, parent=None, settings=None):
        super(NewConnectionDialog, self).__init__(parent)
        self.connections = pyodbc.dataSources()
        self.settings = settings

        #self.connectionsComboBox = QtGui.QListWidget(self)
        self.connectionsComboBox = self.createcombobox()
        self.connectionsComboBox.setAutoCompletion(True)
        self.connectionsComboBox.setAutoCompletionCaseSensitivity(True)
        self.connectionsComboBox.setFont(getFont(14))
        self.fillcombobox()
        self.connectionsComboBox.setFocus()

        #
        self.usernameEdit = QtGui.QLineEdit()
        self.schemaEdit = QtGui.QLineEdit()

        self.passwordEdit = QtGui.QLineEdit()
        self.passwordEdit.setEchoMode(QtGui.QLineEdit.Password)
        self.savePassword = QtGui.QCheckBox("Save password")
        self.savePassword.setChecked(True)
        self.odbcManagerButton = self.createbutton("&ODBC Manager", self.openODBCmanager)

        self.buttonBox = QtGui.QDialogButtonBox(QtGui.QDialogButtonBox.Ok | QtGui.QDialogButtonBox.Cancel)
        self.buttonBox.accepted.connect(self.accept)
        self.buttonBox.rejected.connect(self.reject)

        self.connectionsComboBox.activated.connect(self.setpassword)

        self.mainLayout = QtGui.QGridLayout()

        self.mainLayout.addWidget(QtGui.QLabel("ODBC connection:"), 0, 0)
        self.mainLayout.addWidget(self.connectionsComboBox, 0, 1)
        self.mainLayout.addWidget(QtGui.QLabel("Username:"******"Password:"******"Shema:"), 4, 0)
        self.mainLayout.addWidget(self.schemaEdit, 4, 1)


        self.mainLayout.addWidget(QtGui.QLabel("Open ODBC manager:"), 5, 0)
        self.mainLayout.addWidget(self.odbcManagerButton, 5, 1)
        self.mainLayout.addWidget(self.buttonBox, 6, 1)
        self.setLayout(self.mainLayout)

        self.setWindowTitle("Open new odbc connection")
        self.setpassword()
Example #18
0
def query_database(filepath, query, cursor_dict):

    # Access the library and call this function dataSources
    # From GitHub Returns a dictionary mapping available DSNs to their descriptions.
    # On Windows, these will be the ones defined in the ODBC Data Source Administrator.
    myDataSources = pyodbc.dataSources()
    print(myDataSources)

    # Passing the type of database that we want to access (This may need a driver if your dataSources dictionary is empty)
    access_driver = myDataSources['MS Access Database']

    # This is using the library and calling the connect function to access the database
    # The two imports are the driver (type of file) of the database and where the database is located
    connection = pyodbc.connect(driver=access_driver, dbq=filepath)

    # Cursor is an object that is created once the connection is successful
    # From documentation on SQLite3 A Cursor instance has attributes and methods
    cursor = connection.cursor()

    # This is a method for the cursor object, you can execute a query which injects the SQL code using python
    cursor.execute(query)

    # This pulls off the names of from the header
    columns = [column[0] for column in cursor.description]

    # Show the output of the columns to make sure it looks correct
    print(columns)

    # Message to user because the conversion is slow
    print('Starting Conversion')

    # This code turns the columns into the dictionary name, row is the data
    for row in cursor.fetchall():
        cursor_dict.append(dict(zip(columns, row)))

    # Message to user because this is a slow process
    print('Setting dataframe')

    # Load the cursor object as a DataFrame object
    df = DataFrame(cursor_dict)

    # Check that the DataFrame loads correctly
    print(df)

    # Save the dataframe as a pickle file (compresses the database to load much faster)
    df.to_pickle("BIRD_STRIKE.pkl")
Example #19
0
    def list_values(self, connection_alias, connection_type,list_value):
        """
        commit the results from a previous command.  This is always used in conjunction
        with the execute_command() function, either implicitly or explicitly.

        :param connection_alias: the plain english name to associate with this connection
        :param connection_type: the type of connection to use
        :param list_value: the thing inside the database to list

        """

        logging.debug(" -- Working with connection '%s' of type '%s'" % (connection_alias, connection_type))

        if connection_type.lower() == 'odbc':

            if not self.is_connected(connection_alias) and list_value != 'sources':
                logging.debug(" --- The alias '%s' is not connected" % connection_alias)
                return
            try:
                logging.debug(" --- The list value is '%s'" % list_value)
                results = []
                if list_value == 'tables':
                    logging.debug(" -- Listing tables in '%s'" % connection_alias)
                    cursor = self._conn_info[connection_alias]['cursor']
                    for row in cursor.tables():
                        results.append(row)
                elif list_value == 'procedures':
                    logging.debug(" --- Listing procedures in '%s'" % connection_alias)
                    cursor = self._conn_info[connection_alias]['cursor']
                    for row in cursor.procedures():
                        results.append(row)
                elif list_value == 'sources':
                    logging.debug(" --- Listing sources in '%s'" % connection_alias)

                    for row in pyodbc.dataSources():
                        results.append(row)
                else:
                    raise Exception(stack()[0][3], "The list value provided ('%s') is not valid.  No connection made" % list_value)

                logging.debug(" --- Listing '%s'" % results)
                return results

            except pyodbc.Error, err:
                logging.error(err)
                raise err
Example #20
0
def main():
    data = pyodbc.dataSources()
    #    connect = pyodbc.connect('DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\\sakila.accdb')
    # connect = pyodbc.connect('DSN=MS Access Database;Dbq=C:\\sakila.accdb')
    connect = pyodbc.connect("DSN=MS Access Database;Dbq=C:\\sakila.accdb")
    cursor = connect.cursor()
    cursor.execute("SELECT * FROM actor")
    rows = cursor.fetchall()
    actors = dict()
    for row in rows:
        lname = row[2].capitalize()
        fname = row[1].capitalize()
        if lname in actors:
            actors[lname] = actors[lname] + [fname]
        else:
            actors[lname] = [fname]

    connect.close()
def display_data_sources(grph):
    # https://stackoverflow.com/questions/41591287/driver-not-found-even-though-its-listed-in-pyodbc-datasources
    # The list produced by pyodbc.dataSources() is misleading because it shows the results
    # from both the 32-bit and 64-bit "Platform".
    # A more reliable way to get the list of available drivers is to use pyodbc.drivers()
    logging.debug("Before pyodbc.dataSources")
    sources = pyodbc.dataSources()

    for dsn in sources:
        driver = sources[dsn]

        logging.debug("dsn=%s driver=%s", dsn, driver)

        # This creates a connection string.
        node_dsn = survol_odbc_dsn.MakeUri("DSN=" + dsn)
        grph.add((lib_common.nodeMachine, pc.property_odbc_dsn, node_dsn))
        grph.add(
            (node_dsn, pc.property_odbc_driver, lib_util.NodeLiteral(driver)))
Example #22
0
def m_yahia():
    import pyodbc
    #import os
    #import rarfile

    # file = rarfile.RarFile('./36097.rar')
    # book_name=file.namelist()[0]
    # file.extractall()
    # os.rename(book_name, '36097.mdb')
    sources = pyodbc.dataSources()
    keys = sources.keys()
    for key in keys:
        print(key)
        r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=.\Books\4512.mdb;'
    conn_str = (
        r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=.\Books\4512.mdb;'
        #r'DRIVER={MS Access Daatbase}; DBQ=.\Books\4512.mdb;'
    )
    cnxn = pyodbc.connect(conn_str)
    crsr = cnxn.cursor()
    for table_info in crsr.tables(tableType='TABLE'):
        print(table_info.table_name)
Example #23
0
    def list_values(self, list_value):
        """
        commit the results from a previous command.  This is always used in conjunction
        with the execute_command() function, either implicitly or explicitly.

        :param list_value: the thing inside the database to list

        """

        if not self._is_connected and list_value != 'sources':
            logging.debug("This object is not connected")
            return
        try:
            logging.debug("The list value is '%s'" % list_value)
            results = []
            if list_value == 'tables':
                logging.debug("Listing tables.")
                cursor = self._cursor
                for row in cursor.tables():
                    results.append(row)
            elif list_value == 'procedures':
                logging.debug("Listing procedures.")
                cursor = self._conn_info[connection_alias]['cursor']
                for row in cursor.procedures():
                    results.append(row)
            elif list_value == 'sources':
                logging.debug("Listing sources")

                for row in pyodbc.dataSources():
                    results.append(row)
            else:
                raise Exception(
                    stack()[0][3],
                    "The list value provided ('%s') is not valid.  No connection made"
                    % list_value)

        except pyodbc.Error, err:
            logging.error(err)
            raise err
    def enable_dialect(self, dialect):
        """Enable the given dialect in the item controls."""
        if dialect == 'sqlite':
            self.enable_sqlite()
        elif dialect == 'mssql':
            import pyodbc  # pylint: disable=import-outside-toplevel

            dsns = pyodbc.dataSources()
            # Collect dsns which use the msodbcsql driver
            mssql_dsns = list()
            for key, value in dsns.items():
                if 'msodbcsql' in value.lower():
                    mssql_dsns.append(key)
            if mssql_dsns:
                self._properties_ui.comboBox_dsn.clear()
                self._properties_ui.comboBox_dsn.addItems(mssql_dsns)
                self._properties_ui.comboBox_dsn.setCurrentIndex(-1)
                self.enable_mssql()
            else:
                msg = "Please create a SQL Server ODBC Data Source first."
                self._logger.msg_warning.emit(msg)
        else:
            self.enable_common()
Example #25
0
def DatabaseEnvParams(processId):
    # TODO: We could use the process id to check if the process executable is linked
    # with the SQLServer shareable library.

    # We do not list sources in lib_credentials because some ODBC sources
    # can be accessed without pass word (With Windows validation).
    sources_data = pyodbc.dataSources()

    # {
    #     'MyNativeSqlServerDataSrc': 'SQL Server Native Client 11.0',
    #     'Excel Files': 'Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)',
    #     'SqlSrvNativeDataSource': 'SQL Server Native Client 11.0',
    #     'mySqlServerDataSource': 'SQL Server',
    #     'MyOracleDataSource': 'Oracle in XE',
    #     'SysDataSourceSQLServer': 'SQL Server',
    #     'dBASE Files': 'Microsoft Access dBASE Driver (*.dbf, *.ndx, *.mdx)',
    #     'OraSysDataSrc' : 'Oracle in XE',
    #     'MS Access Database': 'Microsoft Access Driver (*.mdb, *.accdb)'
    # }

    dsn_list = ({"Dsn": "DSN=" + dsn} for dsn in sources_data)

    return "sqlserver/query", dsn_list
Example #26
0
    def list_values(self, list_value):
        """
        commit the results from a previous command.  This is always used in conjunction
        with the execute_command() function, either implicitly or explicitly.

        :param list_value: the thing inside the database to list

        """

        if not self._is_connected and list_value != 'sources':
            logging.debug("This object is not connected")
            return
        try:
            logging.debug("The list value is '%s'" % list_value)
            results = []
            if list_value == 'tables':
                logging.debug("Listing tables.")
                cursor = self._cursor
                for row in cursor.tables():
                    results.append(row)
            elif list_value == 'procedures':
                logging.debug("Listing procedures.")
                cursor = self._conn_info[connection_alias]['cursor']
                for row in cursor.procedures():
                    results.append(row)
            elif list_value == 'sources':
                logging.debug("Listing sources")

                for row in pyodbc.dataSources():
                    results.append(row)
            else:
                raise Exception(stack()[0][3], "The list value provided ('%s') is not valid.  No connection made" % list_value)

        except pyodbc.Error, err:
            logging.error(err)
            raise err
Example #27
0
def verify_dsn_source():
    sources = pyodbc.dataSources()
    dsns = sources.keys()
    data_source_list = []
    for key in dsns:
        data_source_list.append(key)
    if data_source_list.count("Delta ODBC 4") == 1:
        print("Valid Source available")
        print(
            "Please verify that the proper 'Site' is setup in the ODBC Data Source Administrator (32-bit)."
        )
        print("Failure to do so could cause unwanted results.")
        while True:
            user_input = input("Continue? (Yes/No)")
            if user_input.upper() == 'YES':
                return True
                break
            elif user_input.upper() == 'NO':
                sys.exit("terminating application")
            else:
                print("Invalid selection")
    elif data_source_list.count("Delta ODBC 4") == 0:
        print("No valid datasource.")
        return False
# pyodbc
import pyodbc

# look for pyodbc providers
sources = pyodbc.dataSources()
dsns = list(sources.keys())
sl = ['    %s [%s]' % (dsn, sources[dsn]) for dsn in dsns]
print(
    "pyodbc Providers: (beware 32/64 bit driver and python version must match)\n",
    '\n'.join(sl))

# odbc to EXCEL .xls via pyodbc (beware 32/64 bit driver and pytho version must match)
import pyodbc, os
filename = os.path.join(os.getcwd(), 'test.xls')
todo = "select * from [Sheet1$]"
print("\nusing pyodbc to read an Excel .xls file:\n\t", filename)
if os.path.exists(filename):
    CNXNSTRING = 'Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=%s;READONLY=FALSE' % filename
    try:
        cnxn = pyodbc.connect(CNXNSTRING, autocommit=True)
        cursor = cnxn.cursor()
        rows = cursor.execute(todo).fetchall()
        print([column[0] for column in cursor.description])
        print(rows)
        cursor.close()
        cnxn.close()
    except:
        print("\n *** failed ***\n")
# odbc to ACCESS .mdb via pyodbc (beware 32/64 bit driver and python version must match)
import pyodbc, os
filename = os.path.join(os.getcwd(), 'test.mdb')
Example #29
0
import pyodbc

driver = '{Microsoft Access Driver(*.mdb,*.accdb)}'
filepath = r'C:\Users\weidongc\Desktop\Booking\2020\2020 CN Ads Booking v12.accdb'

myDataSource = pyodbc.dataSources()
access_drive = myDataSource['MS Access Database']

cnxn = pyodbc.connect(driver=access_drive, dbq=filepath, autocommit=True)
crsr = cnxn.cursor()

#grab all the tables

table_list = list(crsr.tables())

# for i in table_list:
#     print(i)
table_name = 'wbr'

query = 'select * from {}'.format(table_name)

crsr.execute(query)
crsr.close()
cnxn.close()

#print(result)
# df = pd.DataFrame()
#
# df.append(query)

# one_row = crsr.fetchall()
Example #30
0
 def test_data_sources_list(self):
     for one_data_source in pyodbc.dataSources():
         print("one_data_source=", one_data_source)
     self.assertTrue(False)
Example #31
0
        u"""create table pypyodbc_test_tabl (ID integer PRIMARY KEY,product_name text,数量 numeric(14,4),价格 float,日期 
                datetime,shijian varchar(20),riqi varchar(20), kong float, bin_logo varbinary(8000))""",
        ),
        ('MySQL',
        'DSN=MYSQL',
        u"""create table pypyodbc_test_tabl (ID integer PRIMARY KEY,product_name text,数量 numeric(14,4),价格 float,日期 
                datetime,shijian time,riqi date, kong float, bin_logo BLOB)""",
        
        ),
        ('PostgreSQL',
        'DSN=PostgreSQL35W',
        u"""create table pypyodbc_test_tabl (ID integer PRIMARY KEY,product_name text,数量 numeric(14,4),价格 float,日期 
                        timestamp,shijian time,riqi date, kong float, bin_logo bytea)""",
        ),
        ]
        
        
    pypyodbc.DEBUG = 0
    DSN_list = pypyodbc.dataSources()
    print (DSN_list)
        
        
    if 'profile' in sys.argv:
        import cProfile
        cProfile.run('main()')
    else:
        main()
    if hasattr(pypyodbc,'win_compact_mdb') and sys.platform in ('win32','cli'):
        mdb_file_path = '"'+mdb_path.encode('mbcs')+'"'
        pypyodbc.win_compact_mdb(mdb_file_path,mdb_file_path.replace('.mdb','_compact.mdb'))
# pyodbc 
import pyodbc

# look for pyodbc providers
sources = pyodbc.dataSources()
dsns = list(sources.keys())
sl = ['    %s [%s]' % (dsn, sources[dsn]) for dsn in dsns]
print("pyodbc Providers: (beware 32/64 bit driver and python version must match)\n", '\n'.join(sl))

# odbc to EXCEL .xls via pyodbc (beware 32/64 bit driver and pytho version must match)
import pyodbc, os
filename = os.path.join(os.getcwd(), 'test.xls')
todo = "select * from [Sheet1$]"
print("\nusing pyodbc to read an Excel .xls file:\n\t", filename)
if os.path.exists(filename):
    CNXNSTRING = 'Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=%s;READONLY=FALSE' % filename
    try:
        cnxn = pyodbc.connect(CNXNSTRING, autocommit=True)
        cursor = cnxn.cursor()
        rows = cursor.execute(todo).fetchall()
        print([column[0] for column in cursor.description])
        print(rows)
        cursor.close()
        cnxn.close()
    except:
        print("\n *** failed ***\n")
# odbc to ACCESS .mdb via pyodbc (beware 32/64 bit driver and python version must match)
import pyodbc, os
filename = os.path.join(os.getcwd(), 'test.mdb')
print("\nusing pyodbc to read an ACCESS .mdb file:\n\t", filename)
if os.path.exists(filename):
Example #33
0
    def FuentesODBC(self, nombre):

        return pyodbc.dataSources().get(nombre, '')
Example #34
0
def sources():
    databases = pyodbc.dataSources().items()
    return tuple([dsn for dsn, desc in databases if 'Pervasive' in desc])
# -*- coding: utf8 -*-
__version__ = '$Id: pyodbc_sources.py 1736 2014-01-28 10:36:41Z mn $'

# shows ODBC data sources with driver info

# author: Michal Niklas

import pyodbc
import json

def show_odbc_sources():
	sources = pyodbc.dataSources()
	dsns = sources.keys()
	sl = []
	for dsn in dsns:
		sl.append('%s [%s]' % (dsn, sources[dsn]))
	print('\n'.join(sl))
	
def get_data_sources():
	src = []
	sources = pyodbc.dataSources()
    dsns = sources.keys()
	
	for dsn in dsns:
        src.append('{0} - {1}'.format(dsn, sources[dsn]))
    
	print(src)

if __name__ == '__main__':
	json.dumps(pyodbc.dataSources())
def get_data_sources():
	src = []
	sources = pyodbc.dataSources()
Example #37
0
import pyodbc

# grab the datasources we have access to
pyodbc.dataSources()

# define components of our connection string
driver = '{Microsoft Access Driver (*.mdb, *.accdb)}'
filepath = r'C:\Users\Alex\OneDrive\Career - Work Items\Petco\Financial_Analyst\Petco_Financial_Data.accdb'

# on a side note, here is another way to get the driver setup
myDataSources = pyodbc.dataSources()
access_driver = myDataSources['MS Access Database']
driver = access_driver

# create a connection to the database
cnxn = pyodbc.connect(driver=driver, dbq=filepath, autocommit=True)
crsr = cnxn.cursor()

# grab all the tables
tables_list = list(crsr.tables())

# define the components of a query
table_name = 'ACTUALS_EXPENSE'

# define query
query = "SELECT * FROM {}".format(table_name)

# execute the query
crsr.execute(query)

# fetch a single row, this returns a different type of object
Example #38
0
import pandas as pd
from sqlalchemy import create_engine
import pyodbc

pyodbc.dataSources()

df = pd.read_excel('data.xlsx')
engine = create_engine('mssql+pyodbc://sa:@cct3@m/172.16.10.15/sampleDB')
df.to_sql('new_table', con=engine, if_exists='append', index=False)
Example #39
0
    def check_dialect(self, dialect):
        """Check if selected dialect is supported. Offer to install DBAPI if not.

        Returns:
            True if dialect is supported, False if not.
        """
        if dialect == "":  # TODO: Set text when index is -1 to 'Select dialect...'
            return
        dbapi = SQL_DIALECT_API[dialect]
        try:
            if dialect == 'sqlite':
                create_engine('sqlite://')
                self.enable_sqlite()
            elif dialect == 'mssql':
                import pyodbc
                dsns = pyodbc.dataSources()
                # Collect dsns which use the msodbcsql driver
                mssql_dsns = list()
                for key, value in dsns.items():
                    if 'msodbcsql' in value.lower():
                        mssql_dsns.append(key)
                if mssql_dsns:
                    self._toolbox.ui.comboBox_dsn.clear()
                    self._toolbox.ui.comboBox_dsn.addItems(mssql_dsns)
                    self._toolbox.ui.comboBox_dsn.setCurrentIndex(-1)
                    self.enable_mssql()
                else:
                    msg = "Please create a SQL Server ODBC Data Source first."
                    self._toolbox.msg_warning.emit(msg)
            else:
                create_engine('{}://username:password@host/database'.format(
                    "+".join([dialect, dbapi])))
                self.enable_common()
            return True
        except ModuleNotFoundError:
            dbapi = SQL_DIALECT_API[dialect]
            msg = QMessageBox()
            msg.setIcon(QMessageBox.Question)
            msg.setWindowTitle("Dialect not supported")
            msg.setText("There is no DBAPI installed for dialect '{0}'. "
                        "The default one is '{1}'.".format(dialect, dbapi))
            msg.setInformativeText(
                "Do you want to install it using pip or conda?")
            pip_button = msg.addButton("pip", QMessageBox.YesRole)
            conda_button = msg.addButton("conda", QMessageBox.NoRole)
            cancel_button = msg.addButton("Cancel", QMessageBox.RejectRole)
            msg.exec_()  # Show message box
            if msg.clickedButton() == pip_button:
                if not self.install_dbapi_pip(dbapi):
                    self._toolbox.ui.comboBox_dialect.setCurrentIndex(-1)
                    return False
            elif msg.clickedButton() == conda_button:
                if not self.install_dbapi_conda(dbapi):
                    self._toolbox.ui.comboBox_dialect.setCurrentIndex(-1)
                    return False
            else:
                self._toolbox.ui.comboBox_dialect.setCurrentIndex(-1)
                msg = "Unable to use dialect '{}'.".format(dialect)
                self._toolbox.msg_error.emit(msg)
                return False
            # Check that dialect is not found
            if not self.check_dialect(dialect):
                self._toolbox.ui.comboBox_dialect.setCurrentIndex(-1)
                return False
            return True
Example #40
0
    def __init__(self):
        super().__init__()
        gui.label(self.controlArea, self, "from pandas:")
        self.setSizePolicy(QSizePolicy.Maximum, QSizePolicy.Maximum)
        self.allSQLSelectWidgets.append(self)  # set default settings
        self.domain = None
        self.recentConnections = list()
        self.recentConnections.append("(none)")

        self.queryFile = None
        self.query = ''
        self.lastQuery = None
        if self.lastQuery is not None:
            self.query = self.lastQuery

        sources = pyodbc.dataSources()
        dsns = list(sources.keys())
        dsns.sort()

        for dsn in dsns:
            self.recentConnections.append("DSN={dsn}".format(dsn=dsn))

        self.connectString = self.recentConnections[0]

        self.connectBox = gui.widgetBox(self.controlArea, "Database")

        self.connectLineEdit = gui.lineEdit(self.connectBox,
                                            self,
                                            'connectString',
                                            callback=None)
        self.connectCombo = gui.comboBox(self.connectBox,
                                         self,
                                         'connectString',
                                         items=self.recentConnections,
                                         valueType=str,
                                         sendSelectedValue=True)
        self.button = gui.button(self.connectBox,
                                 self,
                                 'connect',
                                 callback=self.connectDB,
                                 disabled=0)
        # query
        self.splitCanvas = QSplitter(QtCore.Qt.Vertical, self.mainArea)
        self.mainArea.layout().addWidget(self.splitCanvas)

        self.textBox = gui.widgetBox(self, 'HiveQL')
        self.splitCanvas.addWidget(self.textBox)
        self.queryTextEdit = QPlainTextEdit(self.query, self)
        self.textBox.layout().addWidget(self.queryTextEdit)

        self.selectBox = gui.widgetBox(self.controlArea, "Select statement")
        # self.selectSubmitBox = QHGroupBox("", self.selectBox)
        # self.queryTextEdit.setSizePolicy(QSizePolicy(QSizePolicy.Preferred, QSizePolicy.Preferred))
        # self.queryTextEdit.setMinimumWidth(300)
        # self.connect(self.queryTextEdit, SIGNAL('returnPressed()'), self.executeQuery)
        gui.button(self.selectBox, self, "Open...", callback=self.openScript)
        gui.button(self.selectBox, self, "Save...", callback=self.saveScript)
        self.selectBox.setSizePolicy(
            QSizePolicy(QSizePolicy.MinimumExpanding,
                        QSizePolicy.MinimumExpanding))
        gui.button(self.selectBox,
                   self,
                   'format SQL!',
                   callback=self.format_sql,
                   disabled=0)
        gui.button(self.selectBox,
                   self,
                   'execute!',
                   callback=self.executeQuery,
                   disabled=0)
        self.domainBox = gui.widgetBox(self.controlArea, "Domain")
        self.domainLabel = gui.label(self.domainBox, self, '')
        # info
        self.infoBox = gui.widgetBox(self.controlArea, "Info")
        self.info = []
        self.info.append(gui.label(self.infoBox, self, 'No data loaded.'))
        self.info.append(gui.label(self.infoBox, self, ''))
        self.resize(300, 300)

        self.cnxn = None