{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#  Testing System Data access\n",
    "\n",
    "Depending of Drivers some test will works, and other may not\n",
    "\n",
    "Drivers may be at: https://www.microsoft.com/fr-fr/download/details.aspx?id=13255"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# pyodbc \n",
    "import pyodbc\n",
    "\n",
    "# look for pyodbc providers\n",
    "sources = pyodbc.dataSources()\n",
    "dsns = list(sources.keys())\n",
    "sl = ['    %s [%s]' % (dsn, sources[dsn]) for dsn in dsns]\n",
    "print(\"pyodbc Providers: (beware 32/64 bit driver and python version must match)\\n\", '\\n'.join(sl))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# odbc to EXCEL .xls via pyodbc (beware 32/64 bit driver and pytho version must match)\n",
    "import pyodbc, os\n",
    "filename = os.path.join(os.getcwd(), 'test.xls')\n",
    "todo = \"select * from [Sheet1$]\"\n",
    "print(\"\\nusing pyodbc to read an Excel .xls file:\\n\\t\", filename)\n",
    "if os.path.exists(filename):\n",
    "    CNXNSTRING = 'Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=%s;READONLY=FALSE' % filename\n",
    "    cnxn = pyodbc.connect(CNXNSTRING, autocommit=True)\n",
    "    cursor = cnxn.cursor()\n",
    "    rows = cursor.execute(todo).fetchall()\n",
    "    print([column[0] for column in cursor.description])\n",
    "    print(rows)\n",
    "    cursor.close()\n",
    "    cnxn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# odbc to ACCESS .mdb via pyodbc (beware 32/64 bit driver and python version must match)\n",
    "import pyodbc, os\n",
    "filename = os.path.join(os.getcwd(), 'test.mdb')\n",
    "print(\"\\nusing pyodbc to read an ACCESS .mdb file:\\n\\t\", filename)\n",
    "if os.path.exists(filename):\n",
    "    CNXNSTRING = 'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=%s;READONLY=FALSE' % filename\n",
    "    cnxn = pyodbc.connect(CNXNSTRING, autocommit=False)\n",
    "    cursor = cnxn.cursor()\n",
    "    rows = cursor.execute(\"select *  from users\").fetchall()\n",
    "    print([column[0] for column in cursor.description])\n",
    "    print(rows)\n",
    "    cursor.close()\n",
    "    cnxn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# pythonnet\n",
    "import clr\n",
    "clr.AddReference(\"System.Data\")\n",
    "import System.Data.OleDb as ADONET\n",
    "import System.Data.Odbc as ODBCNET\n",
    "import System.Data.Common as DATACOM\n",
    "\n",
    "table = DATACOM.DbProviderFactories.GetFactoryClasses()\n",
    "print(\"\\n .NET Providers: (beware 32/64 bit driver and pytho version must match)\")\n",
    "for  row in table.Rows:\n",
    "    print(\"   %s\" % row[table.Columns[0]])\n",
    "    print(\"      \",[row[column] for column in table.Columns if column != table.Columns[0]])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# odbc to EXCEL .xls via pythonnet\n",
    "import clr, os\n",
    "clr.AddReference(\"System.Data\")\n",
    "import System.Data.OleDb as ADONET\n",
    "import System.Data.Odbc as ODBCNET\n",
    "import System.Data.Common as DATACOM\n",
    "\n",
    "filename = os.path.join(os.getcwd(), 'test.xls')\n",
    "todo = \"select * from [Sheet1$]\"\n",
    "print(\"\\nusing pythonnet to read an excel .xls file:\\n\\t\", filename , \"\\n\\t\", todo)\n",
    "if os.path.exists(filename):\n",
    "    CNXNSTRING = 'Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=%s;READONLY=FALSE' % filename\n",
    "    cnxn = ODBCNET.OdbcConnection(CNXNSTRING)\n",
    "    cnxn.Open()\n",
    "    command = cnxn.CreateCommand()\n",
    "    command.CommandText = \"select * from [Sheet1$]\"\n",
    "    rows = command.ExecuteReader()\n",
    "    print ([rows.GetName(i) for i in range(rows.FieldCount)])\n",
    "    for  row in rows:\n",
    "        print([row[i] for i in range(rows.FieldCount)])\n",
    "    command.Dispose()\n",
    "    cnxn.Close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# odbc to ACCESS .mdb via pythonnet\n",
    "import clr, os\n",
    "clr.AddReference(\"System.Data\")\n",
    "import System.Data.OleDb as ADONET\n",
    "import System.Data.Odbc as ODBCNET\n",
    "import System.Data.Common as DATACOM\n",
    "\n",
    "filename = os.path.join(os.getcwd(), 'test.mdb')\n",
    "todo = \"select * from users\"\n",
    "print(\"\\nusing odbc via pythonnet to read an ACCESS .mdb file:\\n\\t\", filename , \"\\n\\t\", todo)\n",
    "\n",
    "if os.path.exists(filename):\n",
    "    CNXNSTRING = 'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=%s;READONLY=FALSE' % filename\n",
    "    cnxn = ODBCNET.OdbcConnection(CNXNSTRING)\n",
    "    cnxn.Open()\n",
    "    command = cnxn.CreateCommand()\n",
    "    command.CommandText = \"select * from users\"\n",
    "    rows = command.ExecuteReader()\n",
    "    print ([rows.GetName(i) for i in range(rows.FieldCount)])\n",
    "    for  row in rows:\n",
    "        print([row[i] for i in range(rows.FieldCount)])\n",
    "    command.Dispose()\n",
    "    cnxn.Close()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# DAO via pythonnet: works ONLY if you have the 32 (or 64 bit) driver.\n",
    "import clr, os\n",
    "clr.AddReference(\"System.Data\")\n",
    "import System.Data.OleDb as ADONET\n",
    "import System.Data.Odbc as ODBCNET\n",
    "import System.Data.Common as DATACOM\n",
    "\n",
    "filename = os.path.join(os.getcwd(), 'test.accdb')\n",
    "todo = \"select * from users\"\n",
    "print(\"\\nusing DAO via pythonnet to read an ACCESS .mdb file:\\n\\t\", filename , \"\\n\\t\", todo)\n",
    "if os.path.exists(filename):\n",
    "    # needs a driver in 32 or 64 bit like your running python\n",
    "    # https://www.microsoft.com/download/details.aspx?id=13255\n",
    "    CNXNSTRING = 'Provider=Microsoft.ACE.OLEDB.12.0; Data Source=%s;READONLY=FALSE' % filename\n",
    "    cnxn = ADONET.OleDbConnection(CNXNSTRING)\n",
    "    cnxn.Open()\n",
    "    command = cnxn.CreateCommand()\n",
    "    command.CommandText = todo\n",
    "    # command.CommandText = 'select id, name from people where group_id = @group_id'\n",
    "    # command.Parameters.Add(SqlParameter('group_id', 23))\n",
    "    rows = command.ExecuteReader()\n",
    "    print ([rows.GetName(i) for i in range(rows.FieldCount)])\n",
    "    for  row in rows:\n",
    "        print([row[i] for i in range(rows.FieldCount)])\n",
    "    command.Dispose()\n",
    "    cnxn.Close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}