{"id":29,"date":"2023-11-26T19:27:38","date_gmt":"2023-11-27T00:27:38","guid":{"rendered":"https:\/\/coding101.xyz\/?p=29"},"modified":"2023-12-02T20:02:12","modified_gmt":"2023-12-03T01:02:12","slug":"python-and-mysql","status":"publish","type":"post","link":"https:\/\/coding101.xyz\/?p=29","title":{"rendered":"Python and MySQL"},"content":{"rendered":"\n
This article shows how to connect to mysql from python. <\/p>\n\n\n\n\n\n\n\n
The steps are as follows<\/p>\n\n\n\n
\n
create your venv<\/li>\n\n\n\n
install the driver package<\/li>\n\n\n\n
confirm the connectivity parameters<\/li>\n\n\n\n
proceed<\/li>\n<\/ul>\n\n\n\n
The best recommendation is to use a connection pool. The driver uses connection pooling, you just need to use it. This will allow avoiding keeping creating a new connection and destroying it, while you can use connections maintained alive as part of a pool. <\/p>\n\n\n\n
Install Driver<\/h2>\n\n\n\n
The driver is installed with the following command<\/p>\n\n\n\n
There are other drivers, however this one seems to be easiest to install<\/p>\n\n\n\n
Confirm Connectivity Parameters<\/h2>\n\n\n\n
So we use a database installed locally (localhost) on mariadb server lisening to standard port 3306, a database called test, user test and password ‘test1’. The table we created is tinf with fields id and comments, both varchar; <\/p>\n\n\n\n
OK so now we want to connect to it. This is how we do it. The config below contains all the parameters, although we could have omitted the host and the port, since they are the standard ones. <\/p>\n\n\n\n
import<\/span> mysql.connector<\/span>\n\ndbconfig =<\/span> {\n 'database'<\/span>: 'test'<\/span>,\n 'user'<\/span>: 'test'<\/span>,\n 'password'<\/span>: 'test1'<\/span>,\n 'host'<\/span>: 'localhost'<\/span>,\n 'port'<\/span>: 3306<\/span>\n}\n\npool =<\/span> mysql.<\/span>connector.<\/span>pooling.<\/span>MySQLConnectionPool(pool_name=<\/span>'starter_pool'<\/span>, pool_size=<\/span>3<\/span>, **<\/span>dbconfig)\ncnx =<\/span> pool.<\/span>get_connection()\ncursor =<\/span> cnx.<\/span>cursor()\ncursor.<\/span>execute('select id, comments from tinf where id like %s'<\/span>, ('1i%'<\/span>,))\nfor<\/span> row in<\/span> cursor.<\/span>fetchall():\n print<\/span>(row[0<\/span>], row[1<\/span>])\n\ncursor.<\/span>close()\ncnx.<\/span>close()\n<\/pre><\/div>\n\n\n\n
Note a couple of things<\/p>\n\n\n\n
\n
pool should be long lived<\/li>\n\n\n\n
each time you create a connection<\/li>\n\n\n\n
then you need a cursor. This is like a Statement at java, you post the commands against this object, not the connection<\/li>\n\n\n\n
At the end you close both the cursor and the connection, although I suspect that closing the connection (which in this case means releasing resources and returning the connection to the pool) will also close the cursor<\/li>\n<\/ul>\n\n\n\n
Transactions<\/h2>\n\n\n\n
Of course there are transactions in this context. If you need transactions, there are two steps<\/p>\n\n\n\n
Set autocommit to false to the connection, otherwise it is by default true<\/p>\n\n\n\n<\/figure>\n\n\n\n
Then you can call either commit() method or rollback() also on the connection. Of course this is written in some context that takes exceptions into account etc. <\/p>\n","protected":false},"excerpt":{"rendered":"
This article shows how to connect to mysql from python.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-29","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/coding101.xyz\/index.php?rest_route=\/wp\/v2\/posts\/29","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/coding101.xyz\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/coding101.xyz\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/coding101.xyz\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/coding101.xyz\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=29"}],"version-history":[{"count":3,"href":"https:\/\/coding101.xyz\/index.php?rest_route=\/wp\/v2\/posts\/29\/revisions"}],"predecessor-version":[{"id":98,"href":"https:\/\/coding101.xyz\/index.php?rest_route=\/wp\/v2\/posts\/29\/revisions\/98"}],"wp:attachment":[{"href":"https:\/\/coding101.xyz\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=29"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/coding101.xyz\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=29"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/coding101.xyz\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=29"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"\"](\"https:\/\/coding101.xyz\/wp-content\/uploads\/2023\/11\/image-2.png\")
<\/figure>\n\n\n\n