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
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
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]})
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
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))
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
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))
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
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))
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)))
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()
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")
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 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)))
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)
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()
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
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 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')
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()
def test_data_sources_list(self): for one_data_source in pyodbc.dataSources(): print("one_data_source=", one_data_source) self.assertTrue(False)
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):
def FuentesODBC(self, nombre): return pyodbc.dataSources().get(nombre, '')
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()
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
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)
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
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