{"id":532,"date":"2022-10-05T17:47:38","date_gmt":"2022-10-05T15:47:38","guid":{"rendered":"https:\/\/ilmarkerm.eu\/blog\/?p=532"},"modified":"2022-10-06T17:21:43","modified_gmt":"2022-10-06T15:21:43","slug":"connect-to-autonomous-database-using-python-thin-driver","status":"publish","type":"post","link":"https:\/\/ilmarkerm.eu\/blog\/2022\/10\/connect-to-autonomous-database-using-python-thin-driver\/","title":{"rendered":"Connect to Autonomous database using python thin driver"},"content":{"rendered":"\n<p>Oracle recently released a thin driver for Python, meaning it does not require any Oracle client installation (like cx_Oracle does) and it is written natively in Python &#8211; one benefit of which is that it works on all platforms where Python is present, including ARM64.<\/p>\n\n\n\n<p>You can read more about it here: <a href=\"https:\/\/levelup.gitconnected.com\/open-source-python-thin-driver-for-oracle-database-e82aac7ecf5a\">https:\/\/levelup.gitconnected.com\/open-source-python-thin-driver-for-oracle-database-e82aac7ecf5a<\/a><\/p>\n\n\n\n<p>Interestingly it is not yet trivial to find examples, on how to use the new thin driver to connect to Oracle Autonomous Database.<\/p>\n\n\n\n<p>By default Autonomous database requites mTLS connections, so first create and download the Instance wallet for your Autonomous database (zip file), <strong>do remember the password you set<\/strong>. Unzip and transfer <strong>ewallet.pem<\/strong> and <strong>tnsnames.ora<\/strong> to your Python host.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code># Create directory for wallet files\n$ mkdir wallet\n$ pwd\n\/home\/ilmar\/wallet\n\n# Transport ewallet.pem and tnsnames.ora to that directory\n$ ls \/home\/ilmar\/wallet\/\newallet.pem\ntnsnames.ora<\/code><\/pre>\n\n\n\n<p>If you have not yet done so, install the pythoin thin driver.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ pip3.9 install --user oracledb\nCollecting oracledb\n  Downloading oracledb-1.1.1-cp39-cp39-manylinux_2_17_aarch64.manylinux2014_aarch64.whl (6.7 MB)\n     |\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588| 6.7 MB 24.1 MB\/s \nCollecting cryptography&gt;=3.2.1\n  Downloading cryptography-38.0.1-cp36-abi3-manylinux_2_17_aarch64.manylinux2014_aarch64.manylinux_2_24_aarch64.whl (3.7 MB)\n     |\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588| 3.7 MB 34.9 MB\/s \nCollecting cffi&gt;=1.12\n  Downloading cffi-1.15.1-cp39-cp39-manylinux_2_17_aarch64.manylinux2014_aarch64.whl (448 kB)\n     |\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588| 448 kB 90.9 MB\/s \nCollecting pycparser\n  Downloading pycparser-2.21-py2.py3-none-any.whl (118 kB)\n     |\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588\u2588| 118 kB 119.8 MB\/s \nInstalling collected packages: pycparser, cffi, cryptography, oracledb\nSuccessfully installed cffi-1.15.1 cryptography-38.0.1 oracledb-1.1.1 pycparser-2.21<\/code><\/pre>\n\n\n\n<p>Connect from Python code to Autonomous database, connect.py<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>import oracledb, os\n\ndb = oracledb.connect(\n       user=os.environ&#91;'ORACLE_USER'],\n       password=os.environ&#91;'ORACLE_PASSWORD'],\n       dsn=os.environ&#91;'ORACLE_TNS'],\n       config_dir=\"\/home\/ilmar\/wallet\",\n       wallet_location=\"\/home\/ilmar\/wallet\",\n       wallet_password=\"wallet_password_here\")\n\nwith db.cursor() as c:\n    for row in c.execute(\"SELECT owner, object_name FROM all_objects FETCH FIRST 10 ROWS ONLY\"):\n        print(row)\n\ndb.close()<\/code><\/pre>\n\n\n\n<p>Execute<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>export ORACLE_USER=my_database_username\nexport ORACLE_PASSWORD=my_database_password\nexport ORACLE_TNS=connection_identifier_from_tnsnames.ora\n\n$ python3.9 connect.py \n('SYS', 'ORA$BASE')\n('SYS', 'DUAL')\n('PUBLIC', 'DUAL')\n('PUBLIC', 'MAP_OBJECT')\n('SYS', 'SYSTEM_PRIVILEGE_MAP')\n('SYS', 'I_SYSTEM_PRIVILEGE_MAP')\n('PUBLIC', 'SYSTEM_PRIVILEGE_MAP')\n('SYS', 'TABLE_PRIVILEGE_MAP')\n('SYS', 'I_TABLE_PRIVILEGE_MAP')\n('PUBLIC', 'TABLE_PRIVILEGE_MAP')<\/code><\/pre>\n\n\n\n<p>Here I used Oracle Linux 8 sever running on Ampere (ARM64) in Oracle Cloud Always Free Tier. No Oracle database client or Instantclient was installed.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Oracle recently released a thin driver for Python, meaning it does not require any Oracle client installation (like cx_Oracle does) and it is written natively in Python &#8211; one benefit of which is that it works on all platforms where Python is present, including ARM64. You can read more about it here: https:\/\/levelup.gitconnected.com\/open-source-python-thin-driver-for-oracle-database-e82aac7ecf5a Interestingly it [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[4,6],"class_list":["post-532","post","type-post","status-publish","format-standard","hentry","category-blog-entry","tag-oracle","tag-python"],"_links":{"self":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/532","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/comments?post=532"}],"version-history":[{"count":5,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/532\/revisions"}],"predecessor-version":[{"id":538,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/532\/revisions\/538"}],"wp:attachment":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/media?parent=532"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/categories?post=532"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/tags?post=532"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}