{"id":1112,"date":"2021-03-17T14:36:01","date_gmt":"2021-03-17T12:36:01","guid":{"rendered":"https:\/\/www.scalahosting.com\/kb\/?p=1112"},"modified":"2021-03-29T13:00:24","modified_gmt":"2021-03-29T10:00:24","slug":"how-to-connect-to-mysql-using-python","status":"publish","type":"post","link":"https:\/\/www.scalahosting.com\/kb\/how-to-connect-to-mysql-using-python\/","title":{"rendered":"How to Connect to MySQL Using Python"},"content":{"rendered":"<p><span style=\"font-weight: 400;\">Python is a <\/span><b>popular and open-source programming language<\/b><span style=\"font-weight: 400;\"> that lets people integrate systems more effectively. It can connect to database systems, read and also, modify files.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Connecting Python to MySQL databases enables webmasters to <\/span><b>access their servers remotely<\/b><span style=\"font-weight: 400;\"> on their local computers. Using Python also allows them to have <\/span><b>multiple separate working environments<\/b><span style=\"font-weight: 400;\"> through the same connection to the database.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">We\u2019ve put together this article to provide <\/span><b>a quick and straightforward guide<\/b><span style=\"font-weight: 400;\"> on connecting to your MySQL database using Python.\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400;\">The article will be guiding you through <\/span><b>all you\u2019ll need to know on:<\/b><\/p>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><i><span style=\"font-weight: 400;\">Setting up a Python virtual environment<\/span><\/i><\/li>\n<\/ul>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><i><span style=\"font-weight: 400;\">Installing MySQL packages, and<\/span><\/i><\/li>\n<\/ul>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><i><span style=\"font-weight: 400;\">Creating the MySQL connection<\/span><\/i><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400;\">Let\u2019s dive in.<\/span><\/p>\n<h2><b>Install a Pip Python Module<\/b><\/h2>\n<p><span style=\"font-weight: 400;\">To connect MySQL to Python, you\u2019d need <\/span><b>to install the Pip Python module<\/b><span style=\"font-weight: 400;\">.\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400;\">A Pip module is the<\/span><i><span style=\"font-weight: 400;\"> standard package manager for Python<\/span><\/i><span style=\"font-weight: 400;\">. It allows you to install and manage additional packages and extensions that are part of the standard Python library distribution.<\/span><\/p>\n<p><b>Install at least one<\/b><span style=\"font-weight: 400;\"> of these packages in a virtual environment to connect MySQL using Python.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Mysql-client:<\/b><span style=\"font-weight: 400;\"> this client package allows you to connect to a MySQL server and access the command-line program. It comes with utilities that enable you to easily backup data, restore and administer the server. This package contains the MySQLdb module.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Mysql-connector-python: <\/b><span style=\"font-weight: 400;\">this package is a MySQL driver that enables Python programs to access <\/span><b>MySQL databases using an API<\/b><span style=\"font-weight: 400;\">. The package contains the <\/span><i><span style=\"font-weight: 400;\">mysql.connector<\/span><\/i><span style=\"font-weight: 400;\"> module.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>PyMySQL:<\/b><span style=\"font-weight: 400;\"> this package provides an interface for connecting to the MySQL database server. It contains the pymysql module.<\/span><\/li>\n<\/ul>\n<h2><b>Setting Up a Python Virtual Environment<\/b><\/h2>\n<p><span style=\"font-weight: 400;\">A virtual environment allows webmasters <\/span><b>to create and manage separate environments<\/b><span style=\"font-weight: 400;\"> for Python projects. Each environment uses a different version of Python for execution.<\/span><\/p>\n<p><b>Follow these steps <\/b><span style=\"font-weight: 400;\">to set up a python virtual environment.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Log in to your server using secure shell <\/span><a href=\"https:\/\/www.scalahosting.com\/kb\/access-server-ssh\/\"><span style=\"font-weight: 400;\">(SSH) access<\/span><\/a><span style=\"font-weight: 400;\">. Doing this allows you to access the command line remotely.<\/span><\/p>\n<p><b>Type this command<\/b><span style=\"font-weight: 400;\"> to create a python virtual environment.<\/span><\/p>\n<p><i><span style=\"font-weight: 400;\">cd ~<\/span><\/i><\/p>\n<p><i><span style=\"font-weight: 400;\">virtualenv envname<\/span><\/i><\/p>\n<p><b>Replace <\/b><b><i>envname<\/i><\/b> <span style=\"font-weight: 400;\">with the name of the virtual environment you intend to create.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">After creating the virtual environment, you\u2019d need to activate it. Use command to execute the task.<\/span><\/p>\n<p><i><span style=\"font-weight: 400;\">source envname\/bin\/activate<\/span><\/i><\/p>\n<p><span style=\"font-weight: 400;\">Upon activation, the command prompt will now start with <\/span><i><span style=\"font-weight: 400;\">envname<\/span><\/i><span style=\"font-weight: 400;\"> , indicating you are working on a Python virtual environment named <\/span><i><span style=\"font-weight: 400;\">envname<\/span><\/i><span style=\"font-weight: 400;\">.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Any time you feel like updating pip in the virtual environment, <\/span><b>then run this command<\/b><span style=\"font-weight: 400;\"> to execute the task<\/span><\/p>\n<p><i><span style=\"font-weight: 400;\">pip install -U pip<\/span><\/i><\/p>\n<h2><b>Installing The MySQL Packages<\/b><\/h2>\n<p><span style=\"font-weight: 400;\">After creating and activating the python virtual environment, <\/span><b>the next thing is to install the MySQL packages<\/b><span style=\"font-weight: 400;\">. It\u2019s advisable to install all three packages.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Installing multiple modules allows you <\/span><b>to switch between modules anytime<\/b><span style=\"font-weight: 400;\">. The modules use the <\/span><span style=\"font-weight: 400;\">portable SQL database API interface; this will enable you to reuse codes without any modification.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Run these codes to install the packages.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Run this code to install the mysqlclient package <\/span><i><span style=\"font-weight: 400;\">pip install mysqlclient<\/span><\/i><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Use this code to install the mysql-connector-python package <\/span><i><span style=\"font-weight: 400;\">pip install mysql-connector-python<\/span><\/i><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">And, this to install the pymysql packages <\/span><i><span style=\"font-weight: 400;\">pip install pymysql<\/span><\/i><\/li>\n<\/ul>\n<h2><b>Creating The Connection<\/b><\/h2>\n<p><span style=\"font-weight: 400;\">After installing the packages, <\/span><b>you can connect to your MySQL databases<\/b><span style=\"font-weight: 400;\"> and run commands through any of those modules.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Run this code to establish a MySQL Python connection using the MySQL connector module.<\/span><\/p>\n<p><i><span style=\"font-weight: 400;\">mydb = mysql.connector.connect(<\/span><\/i><\/p>\n<p><i><span style=\"font-weight: 400;\">\u00a0\u00a0host=<\/span><\/i><i><span style=\"font-weight: 400;\">&#8220;localhost&#8221;<\/span><\/i><i><span style=\"font-weight: 400;\">,<\/span><\/i><\/p>\n<p><i><span style=\"font-weight: 400;\">\u00a0\u00a0user=<\/span><\/i><i><span style=\"font-weight: 400;\">&#8220;yourusername&#8221;<\/span><\/i><i><span style=\"font-weight: 400;\">,<\/span><\/i><\/p>\n<p><i><span style=\"font-weight: 400;\">\u00a0\u00a0password=<\/span><\/i><i><span style=\"font-weight: 400;\">&#8220;yourpassword&#8221;<\/span><\/i><\/p>\n<p><i><span style=\"font-weight: 400;\">)<\/span><\/i><\/p>\n<p><i><span style=\"font-weight: 400;\">print(mydb)<\/span><\/i><\/p>\n<p><span style=\"font-weight: 400;\">Replace <\/span><i><span style=\"font-weight: 400;\">yourusername<\/span><\/i><span style=\"font-weight: 400;\"> and <\/span><i><span style=\"font-weight: 400;\">yourpassword<\/span><\/i><span style=\"font-weight: 400;\"> with those of the database you want to connect to.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">To set up series of Python connection that opens the same database using the different MySQL packages, then <\/span><b>run the below sample code<\/b><span style=\"font-weight: 400;\">.<\/span><\/p>\n<p>&nbsp;<\/p>\n<p><i><span style=\"font-weight: 400;\">#!\/usr\/bin\/python<\/span><\/i><\/p>\n<p><i><span style=\"font-weight: 400;\">from __future__ import print_function<\/span><\/i><\/p>\n<p><i><span style=\"font-weight: 400;\">hostname = &#8216;<\/span><\/i><i><span style=\"font-weight: 400;\">localhost<\/span><\/i><i><span style=\"font-weight: 400;\">&#8216;<\/span><\/i><\/p>\n<p><i><span style=\"font-weight: 400;\">username = &#8216;<\/span><\/i><i><span style=\"font-weight: 400;\">yourusername<\/span><\/i><i><span style=\"font-weight: 400;\">&#8216;<\/span><\/i><\/p>\n<p><i><span style=\"font-weight: 400;\">password = &#8216;<\/span><\/i><i><span style=\"font-weight: 400;\">yourpassword<\/span><\/i><i><span style=\"font-weight: 400;\">&#8216;<\/span><\/i><\/p>\n<p><i><span style=\"font-weight: 400;\">database = &#8216;<\/span><\/i><i><span style=\"font-weight: 400;\">yourdbname<\/span><\/i><i><span style=\"font-weight: 400;\">&#8216;<\/span><\/i><\/p>\n<p><i><span style=\"font-weight: 400;\"># Simple routine to run a query on a database and print the results:<\/span><\/i><\/p>\n<p><i><span style=\"font-weight: 400;\">def doQuery( conn ) :<\/span><\/i><\/p>\n<p><i><span style=\"font-weight: 400;\">\u00a0\u00a0\u00a0\u00a0cur = conn.cursor()<\/span><\/i><\/p>\n<p><i><span style=\"font-weight: 400;\">\u00a0\u00a0\u00a0\u00a0cur.execute( &#8220;SELECT fname, lname FROM employee&#8221; )<\/span><\/i><\/p>\n<p><i><span style=\"font-weight: 400;\">\u00a0\u00a0\u00a0\u00a0for firstname, lastname in cur.fetchall() :<\/span><\/i><\/p>\n<p><i><span style=\"font-weight: 400;\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0print( firstname, lastname )<\/span><\/i><\/p>\n<p><i><span style=\"font-weight: 400;\">print( &#8220;Using mysqlclient (MySQLdb):&#8221; )<\/span><\/i><\/p>\n<p><i><span style=\"font-weight: 400;\">import MySQLdb<\/span><\/i><\/p>\n<p><i><span style=\"font-weight: 400;\">myConnection = MySQLdb.connect( host=<\/span><\/i><i><span style=\"font-weight: 400;\">localhost<\/span><\/i><i><span style=\"font-weight: 400;\">, user=<\/span><\/i><i><span style=\"font-weight: 400;\">yourusername<\/span><\/i><i><span style=\"font-weight: 400;\">, passwd=<\/span><\/i><i><span style=\"font-weight: 400;\">yourpassword<\/span><\/i><i><span style=\"font-weight: 400;\">, db=<\/span><\/i><i><span style=\"font-weight: 400;\">yourdbname<\/span><\/i><i><span style=\"font-weight: 400;\"> )<\/span><\/i><\/p>\n<p><i><span style=\"font-weight: 400;\">doQuery( myConnection )<\/span><\/i><\/p>\n<p><i><span style=\"font-weight: 400;\">myConnection.close()<\/span><\/i><\/p>\n<p><i><span style=\"font-weight: 400;\">print( &#8220;Using mysql.connector:&#8221; )<\/span><\/i><\/p>\n<p><i><span style=\"font-weight: 400;\">import mysql.connector<\/span><\/i><\/p>\n<p><i><span style=\"font-weight: 400;\">myConnection = mysql.connector.connect( host=<\/span><\/i><i><span style=\"font-weight: 400;\">localhost<\/span><\/i><i><span style=\"font-weight: 400;\">, user=<\/span><\/i><i><span style=\"font-weight: 400;\">yourusername<\/span><\/i><i><span style=\"font-weight: 400;\">, passwd=<\/span><\/i><i><span style=\"font-weight: 400;\">yourpassword<\/span><\/i><i><span style=\"font-weight: 400;\">, db=<\/span><\/i><i><span style=\"font-weight: 400;\">yourdbname<\/span><\/i><i><span style=\"font-weight: 400;\"> )<\/span><\/i><\/p>\n<p><i><span style=\"font-weight: 400;\">doQuery( myConnection )<\/span><\/i><\/p>\n<p><i><span style=\"font-weight: 400;\">myConnection.close()<\/span><\/i><\/p>\n<p><i><span style=\"font-weight: 400;\">print( &#8220;Using pymysql:&#8221; )<\/span><\/i><\/p>\n<p><i><span style=\"font-weight: 400;\">import pymysql<\/span><\/i><\/p>\n<p><i><span style=\"font-weight: 400;\">myConnection = pymysql.connect( host=<\/span><\/i><i><span style=\"font-weight: 400;\">localhost<\/span><\/i><i><span style=\"font-weight: 400;\">, user=<\/span><\/i><i><span style=\"font-weight: 400;\">yourusername<\/span><\/i><i><span style=\"font-weight: 400;\">, passwd=<\/span><\/i><i><span style=\"font-weight: 400;\">yourpassword<\/span><\/i><i><span style=\"font-weight: 400;\">, db=<\/span><\/i><i><span style=\"font-weight: 400;\">yourdbname<\/span><\/i><i><span style=\"font-weight: 400;\"> )<\/span><\/i><\/p>\n<p><i><span style=\"font-weight: 400;\">doQuery( myConnection )<\/span><\/i><\/p>\n<p><i><span style=\"font-weight: 400;\">myConnection.close()<\/span><\/i><\/p>\n<p><span style=\"font-weight: 400;\">Replace <\/span><i><span style=\"font-weight: 400;\">yourusername<\/span><\/i><span style=\"font-weight: 400;\"> with the username of the MySQL database you want to connect to, <\/span><i><span style=\"font-weight: 400;\">yourpassword<\/span><\/i><span style=\"font-weight: 400;\"> with the database user\u2019s password, and <\/span><i><span style=\"font-weight: 400;\">yourdbname<\/span><\/i><span style=\"font-weight: 400;\"> with the database\u2019s name.<\/span><\/p>\n<h2><b>Wrapping It Up<\/b><\/h2>\n<p><span style=\"font-weight: 400;\">You can connect to your MySQL databases and <\/span><b>run commands remotely<\/b><span style=\"font-weight: 400;\"> using Python programs. This guide has described the quick steps to establish the connection between Python and MySQL.\u00a0<\/span><\/p>\n<p><b>Use it to get yourself up and running<\/b><span style=\"font-weight: 400;\">.<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Python is a popular and open-source programming language that lets people integrate systems more effectively. It can connect to database systems, read and also, modify files. Connecting Python to MySQL databases enables webmasters to access their servers remotely on their local computers. Using Python also allows them to have multiple separate working environments through the [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_seopress_titles_title":"","_seopress_titles_desc":"","_seopress_robots_index":"","_seopress_robots_follow":"","_seopress_robots_imageindex":"","_seopress_robots_snippet":"","_seopress_robots_primary_cat":"","_seopress_robots_breadcrumbs":"","_seopress_robots_freeze_modified_date":"","_seopress_robots_custom_modified_date":"","_seopress_robots_canonical":"","_seopress_social_fb_title":"","_seopress_social_fb_desc":"","_seopress_social_fb_img":"","_seopress_social_fb_img_attachment_id":0,"_seopress_social_fb_img_width":0,"_seopress_social_fb_img_height":0,"_seopress_social_twitter_title":"","_seopress_social_twitter_desc":"","_seopress_social_twitter_img":"","_seopress_social_twitter_img_attachment_id":0,"_seopress_social_twitter_img_width":0,"_seopress_social_twitter_img_height":0,"_seopress_redirections_value":"","_seopress_redirections_enabled":"","_seopress_redirections_enabled_regex":"","_seopress_redirections_logged_status":"","_seopress_redirections_param":"","_seopress_redirections_type":0,"_seopress_analysis_target_kw":"","_seopress_news_disabled":"","_seopress_video_disabled":"","_seopress_video":[],"_seopress_pro_schemas_manual":[],"_seopress_pro_rich_snippets_disable_all":"","_seopress_pro_rich_snippets_disable":[],"_seopress_pro_schemas":[],"footnotes":""},"categories":[34],"tags":[],"class_list":["post-1112","post","type-post","status-publish","format-standard","hentry","category-web-hosting"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.scalahosting.com\/kb\/wp-json\/wp\/v2\/posts\/1112","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.scalahosting.com\/kb\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.scalahosting.com\/kb\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.scalahosting.com\/kb\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.scalahosting.com\/kb\/wp-json\/wp\/v2\/comments?post=1112"}],"version-history":[{"count":1,"href":"https:\/\/www.scalahosting.com\/kb\/wp-json\/wp\/v2\/posts\/1112\/revisions"}],"predecessor-version":[{"id":1113,"href":"https:\/\/www.scalahosting.com\/kb\/wp-json\/wp\/v2\/posts\/1112\/revisions\/1113"}],"wp:attachment":[{"href":"https:\/\/www.scalahosting.com\/kb\/wp-json\/wp\/v2\/media?parent=1112"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.scalahosting.com\/kb\/wp-json\/wp\/v2\/categories?post=1112"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.scalahosting.com\/kb\/wp-json\/wp\/v2\/tags?post=1112"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}