{"id":239,"date":"2022-01-26T16:28:26","date_gmt":"2022-01-26T15:28:26","guid":{"rendered":"https:\/\/alessandromasciadri.com\/?p=239"},"modified":"2022-01-31T23:48:50","modified_gmt":"2022-01-31T22:48:50","slug":"come-collegare-python-ad-un-database-access-usando-pyodbc","status":"publish","type":"post","link":"https:\/\/alessandromasciadri.com\/come-collegare-python-ad-un-database-access-usando-pyodbc\/","title":{"rendered":"Come collegare Python ad un database Access usando Pyodbc"},"content":{"rendered":"\t\t<div data-akihiro-type=\"ama-post\" data-akihiro-id=\"239\" class=\"akihiro akihiro-239\" data-akihiro-post-type=\"post\">\n\t\t\t\t\t\t<section class=\"akihiro-section akihiro-top-section akihiro-element akihiro-element-bad72f8 akihiro-section-boxed akihiro-section-height-default akihiro-section-height-default\" data-id=\"bad72f8\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"akihiro-container akihiro-column-gap-default\">\n\t\t\t\t\t<div class=\"akihiro-column akihiro-col-100 akihiro-top-column akihiro-element akihiro-element-0d3e8a4\" data-id=\"0d3e8a4\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"akihiro-widget-wrap akihiro-element-populated\">\n\t\t\t\t\t\t<div class=\"akihiro-element akihiro-element-52fc507 akihiro-widget akihiro-widget-text-editor\" data-id=\"52fc507\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>Da quando l&#8217;analitica dei dati \u00e8 diventata una materia di vitale importanza, Python e le sue prodigiose librerie si sono affermate come punto di riferimento tra i Data Analyst. Parlare di dati vuol dire anche essere in grado di interrogare database. In questo scenario dobbiamo integrare un database Access all&#8217;interno di un nostro progetto Python. Per fare ci\u00f2 ricorriamo alla libreria <a href=\"https:\/\/pypi.org\/project\/pyodbc\/\">pyodbc<\/a>.<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-4ae10f6 akihiro-widget akihiro-widget-heading\" data-id=\"4ae10f6\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t<h2 class=\"akihiro-heading-title akihiro-size-default\">Soluzione<\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-4a27b0b akihiro-widget akihiro-widget-text-editor\" data-id=\"4a27b0b\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>Apriamo il command prompt ed installiamo la libreria pyodbc con il seguente comando:<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-2fc1acc akihiro-widget akihiro-widget-code-highlight\" data-id=\"2fc1acc\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"code-highlight.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t<div class=\"prismjs-okaidia copy-to-clipboard \">\n\t\t\t<pre data-line=\"\" class=\"highlight-height language-bash line-numbers\">\n\t\t\t\t<code readonly=\"true\" class=\"language-bash\">\n\t\t\t\t\t<xmp>pip install pyodbc<\/xmp>\n\t\t\t\t<\/code>\n\t\t\t<\/pre>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-725611d akihiro-widget akihiro-widget-text-editor\" data-id=\"725611d\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>Una cosa da notare in anticipo \u00e8 che se si ha MS Access a 64 bit \u00e8 necessario utilizzare Python a 64 bit. La combinazione di Python a 64 bit con Access a 32 bit generer\u00e0 un errore quando ci si tenta di connettere. Il motivo \u00e8 che ci sono due diversi driver Access ODBC di Microsoft:<\/p><ol><li>Old Driver (32-bit) \u2013 Microsoft Access Driver (*.mdb): funziona con 32-bit Python<\/li><li>New Driver (64-bit) \u2013 Microsoft Access Driver (*.mdb, *.accdb): funziona con 64-bit Python<\/li><\/ol>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-ac77d82 akihiro-widget akihiro-widget-text-editor\" data-id=\"ac77d82\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>Se sulla propria macchina si ha una versione di Microsoft Office installata, si dovrebbe gi\u00e0 avere a bordo uno dei driver. Nel caso in cui non si dovesse disporre del driver, si pu\u00f2 scaricarne una versione standalone dal sito di Microsoft: <a href=\"https:\/\/www.microsoft.com\/en-us\/download\/details.aspx?id=54920\">https:\/\/www.microsoft.com\/en-us\/download\/details.aspx?id=54920<\/a><\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-c066cf2 akihiro-widget akihiro-widget-text-editor\" data-id=\"c066cf2\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>Per verificare quale versione \u00e8 installata sul proprio computer eseguire i seguenti comandi nell&#8217;interprete Python:<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-6f1374b akihiro-widget akihiro-widget-code-highlight\" data-id=\"6f1374b\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"code-highlight.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t<div class=\"prismjs-okaidia copy-to-clipboard \">\n\t\t\t<pre data-line=\"\" class=\"highlight-height language-bash line-numbers\">\n\t\t\t\t<code readonly=\"true\" class=\"language-bash\">\n\t\t\t\t\t<xmp>>>>import pyodbc\r\n>>>[i for i in pyodbc.drivers() if i.startswith('Microsoft Access Driver')]\r\n['Microsoft Access Driver (*.mdb, *.accdb)']<\/xmp>\n\t\t\t\t<\/code>\n\t\t\t<\/pre>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-948ddfd akihiro-widget akihiro-widget-text-editor\" data-id=\"948ddfd\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>Vengono scansiti tutti i driver ODBC disponibili e vengono restituiti solo quelli che iniziano con &#8220;Driver Microsoft Access&#8221;. Il risultato precedente mostra che sul mio computer c&#8217;\u00e8 installato il driver Access a 64 bit.<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-3436fd1 akihiro-widget akihiro-widget-heading\" data-id=\"3436fd1\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t<h2 class=\"akihiro-heading-title akihiro-size-default\">Connettere Python ad un Database Access<\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-c9f9c3a akihiro-widget akihiro-widget-text-editor\" data-id=\"c9f9c3a\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>Per connettersi a un database, abbiamo bisogno di una stringa di connessione, ossia un puntatore di testo che dice a Python dove trovare il database. Per MS Access, \u00e8 necessario specificare anche il tipo di driver ODBC (32 bit o 64 bit) nella stringa di connessione.<\/p><p>Assicurarsi inoltre di chiudere il database di MS Access prima di effettuare la connessione, altrimenti si verificher\u00e0 un errore.<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-1a28a30 akihiro-widget akihiro-widget-code-highlight\" data-id=\"1a28a30\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"code-highlight.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t<div class=\"prismjs-okaidia copy-to-clipboard \">\n\t\t\t<pre data-line=\"\" class=\"highlight-height language-python line-numbers\">\n\t\t\t\t<code readonly=\"true\" class=\"language-python\">\n\t\t\t\t\t<xmp>conn_str = (r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'\r\n            r'DBQ=E:\\PythonInOffice\\python_connect_to_ms_access\\database.accdb;')\r\nconn = pyodbc.connect(conn_str)<\/xmp>\n\t\t\t\t<\/code>\n\t\t\t<\/pre>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-2ca8ef2 akihiro-widget akihiro-widget-heading\" data-id=\"2ca8ef2\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t<h2 class=\"akihiro-heading-title akihiro-size-default\">Trovare tutte le tabelle e le query nel database di MS Access<\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-48eabe8 akihiro-widget akihiro-widget-text-editor\" data-id=\"48eabe8\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>Possiamo scorrere tutte le tabelle all&#8217;interno del database di Access e filtrare per tabelle di dati (&#8220;TABLE&#8221;) o query (&#8220;VIEW&#8221;). Ma seriamente, se possiamo usare Python perch\u00e9 anche preoccuparsi delle query di Access \u00af\\_(\u30c4)_\/\u00af<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-920bbdd akihiro-widget akihiro-widget-code-highlight\" data-id=\"920bbdd\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"code-highlight.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t<div class=\"prismjs-okaidia copy-to-clipboard \">\n\t\t\t<pre data-line=\"\" class=\"highlight-height language-python line-numbers\">\n\t\t\t\t<code readonly=\"true\" class=\"language-python\">\n\t\t\t\t\t<xmp>cursor = conn.cursor()\r\nfor i in cursor.tables(tableType='TABLE'):\r\n    print(i.table_name)\r\n\r\nfor i in cursor.tables(tableType='VIEW'):\r\n    print(i.table_name)<\/xmp>\n\t\t\t\t<\/code>\n\t\t\t<\/pre>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-1fa2f04 akihiro-widget akihiro-widget-heading\" data-id=\"1fa2f04\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t<h2 class=\"akihiro-heading-title akihiro-size-default\">Interrogare il database di MS Access<\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-63058ec akihiro-widget akihiro-widget-text-editor\" data-id=\"63058ec\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>Per interagire con un database, dobbiamo usare un linguaggio che esso comprende. Ci\u00f2 significa che dobbiamo usare SQL. Questo non \u00e8 un tutorial SQL, quindi non tratteremo i dettagli. Se hai bisogno di aiuto con SQL, <a href=\"https:\/\/www.w3schools.com\/sql\/\">w3schools<\/a> \u00e8 un ottimo sito a cui fare riferimento.<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-8bc7e6a akihiro-widget akihiro-widget-code-highlight\" data-id=\"8bc7e6a\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"code-highlight.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t<div class=\"prismjs-okaidia copy-to-clipboard \">\n\t\t\t<pre data-line=\"\" class=\"highlight-height language-python line-numbers\">\n\t\t\t\t<code readonly=\"true\" class=\"language-python\">\n\t\t\t\t\t<xmp>#Eseguire una query SQL\r\ncursor.execute('SELECT * FROM table_name')\r\n\r\n#Ottenere una riga\r\none_row = cursor.fetchone()\r\n\r\n#Ottenere tutte le righe\r\nrows = cursor.fetchall()<\/xmp>\n\t\t\t\t<\/code>\n\t\t\t<\/pre>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-965cc02 akihiro-widget akihiro-widget-heading\" data-id=\"965cc02\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t<h2 class=\"akihiro-heading-title akihiro-size-default\">Case Study<\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-5ca2333 akihiro-widget akihiro-widget-text-editor\" data-id=\"5ca2333\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>Ipotiziamo di avere una tabella <strong>products<\/strong> cos\u00ec strutturata<\/p><table><tbody><tr><td><strong>product_id<\/strong><\/td><td><strong>product_name<\/strong><\/td><td><strong>price<\/strong><\/td><\/tr><tr><td>1<\/td><td>Computer<\/td><td>800<\/td><\/tr><tr><td>2<\/td><td>Printer<\/td><td>150<\/td><\/tr><tr><td>3<\/td><td>Desk<\/td><td>400<\/td><\/tr><tr><td>4<\/td><td>Chair<\/td><td>120<\/td><\/tr><tr><td>5<\/td><td>Tablet<\/td><td>300<\/td><\/tr><\/tbody><\/table>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-c7ef8f4 akihiro-widget akihiro-widget-text-editor\" data-id=\"c7ef8f4\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>Se volessimo selezionare tutti i dati in essa contenuti, \u00e8 sufficiente utilizzare questa query SQL<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-a9902ed akihiro-widget akihiro-widget-code-highlight\" data-id=\"a9902ed\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"code-highlight.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t<div class=\"prismjs-okaidia copy-to-clipboard \">\n\t\t\t<pre data-line=\"\" class=\"highlight-height language-python line-numbers\">\n\t\t\t\t<code readonly=\"true\" class=\"language-python\">\n\t\t\t\t\t<xmp>import pyodbc\r\n\r\nconn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\\Users\\MyUser\\Desktop\\test_database.accdb;')\r\ncursor = conn.cursor()\r\ncursor.execute('SELECT * FROM products')\r\n\r\nfor row in cursor.fetchall():\r\n    print(row)<\/xmp>\n\t\t\t\t<\/code>\n\t\t\t<\/pre>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-930a5eb akihiro-widget akihiro-widget-heading\" data-id=\"930a5eb\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t<span class=\"akihiro-heading-title akihiro-size-default\">Output:<\/span>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-e218b67 akihiro-widget akihiro-widget-text-editor\" data-id=\"e218b67\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>Come risultato otterremo nella nostra shell un output di questo tipo<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-70e52b5 akihiro-widget akihiro-widget-code-highlight\" data-id=\"70e52b5\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"code-highlight.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t<div class=\"prismjs-okaidia  \">\n\t\t\t<pre data-line=\"\" class=\"highlight-height language-bash \">\n\t\t\t\t<code readonly=\"true\" class=\"language-bash\">\n\t\t\t\t\t<xmp>(1, 'Computer', 800)\r\n(2, 'Printer', 150)\r\n(3, 'Desk', 400)\r\n(4, 'Chair', 120)\r\n(5, 'Tablet', 300)<\/xmp>\n\t\t\t\t<\/code>\n\t\t\t<\/pre>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<\/div>\n\t\t","protected":false},"excerpt":{"rendered":"<p>Da quando l&#8217;analitica dei dati \u00e8 diventata una materia di vitale importanza, Python e le sue prodigiose librerie si sono affermate come punto di riferimento tra i Data Analyst. Parlare di dati vuol dire anche essere in grado di interrogare database. In questo scenario dobbiamo integrare un database Access all&#8217;interno di un nostro progetto Python. [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[12],"class_list":["post-239","post","type-post","status-publish","format-standard","hentry","category-programmazione","tag-python"],"_links":{"self":[{"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/posts\/239","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/comments?post=239"}],"version-history":[{"count":28,"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/posts\/239\/revisions"}],"predecessor-version":[{"id":498,"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/posts\/239\/revisions\/498"}],"wp:attachment":[{"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/media?parent=239"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/categories?post=239"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/tags?post=239"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}