{"id":8264,"date":"2021-02-08T16:59:35","date_gmt":"2021-02-08T11:29:35","guid":{"rendered":"https:\/\/www.h2kinfosys.com\/blog\/?p=8264"},"modified":"2021-02-08T17:05:54","modified_gmt":"2021-02-08T11:35:54","slug":"python-with-mysql-connect-create-database-table-insert","status":"publish","type":"post","link":"https:\/\/www.h2kinfosys.com\/blog\/python-with-mysql-connect-create-database-table-insert\/","title":{"rendered":"Python with MySQL: Connect, Create Database, Table, Insert"},"content":{"rendered":"\n<p>An application without a database can\u2019t remember information. Once you quit the application all the information will be lost. To store information that you either take from the user or the generated through complex computations database is used. In this tutorial, we will learn how to connect your Python code with the database to store the information. We will connect the<a href=\"https:\/\/www.h2kinfosys.com\/blog\/database-setup-mysql-oraclexe\/\" class=\"rank-math-link\"> MySQL database<\/a> with our python code.<\/p>\n\n\n\n<p>So you need to install MySql first.<\/p>\n\n\n\n<p>For the Installation of MySql in Mac Os, the following command is used.<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-subtle-pale-blue-background-color has-background\"><tbody><tr><td>brew install mysql<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p>For the installation of MySql in Linux operating System the following command is used.<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-subtle-pale-blue-background-color has-background\"><tbody><tr><td>sudo apt-get install mysql<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p>The following is the output when MySql is installed in the Mac.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/lh5.googleusercontent.com\/E6eRVe0BeiqHvYP7RdyTXS2bm4C57m19KbXqUG5wPl5aA2wCuF0JU2XCuOPWHjs9WDpBIskWCKwQzamHIYji2PZhOlA_LCc7KOurToBA-_TDj9NeYhCu5qVoRwoekjCcAkf0sUk\" alt=\"\" title=\"\"><\/figure>\n\n\n\n<p>You also need to install a library that connects your Python with the MySql. The following command is used to install the library.<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-subtle-pale-blue-background-color has-background\"><tbody><tr><td>pip3 install mysql-connector<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p>If everything goes right you must be able to run the following line in your IDE.<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-subtle-pale-blue-background-color has-background\"><tbody><tr><td>import mysql.connector<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Connecting with MySQL<\/h2>\n\n\n\n<p>Let\u2019s take a look at how to connect Python with MySql.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">import mysql.connector\ndb_connection = mysql.connector.connect(\nhost=\"localhost\",\nuser=\"root\",\npasswd=\"\"\n)\nprint(db_connection)<\/pre>\n\n\n\n<p>You need to pass three parameters&nbsp;<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Host<\/li><li>User<\/li><li>passwd<\/li><\/ul>\n\n\n\n<p>The host is the IP address of the machine where your MySQL is running. In our case, we are using our localhost. User is the \u201croot\u201d. You can change the user or create a new user with a password.<\/p>\n\n\n\n<p>When you run the above code the following will be the output.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/lh5.googleusercontent.com\/WBABmdQGZlRKDHWghifQfJrdO4N_mWhtNlAG7Lq06QYaRg4ybk3qVyvIh5ZM1aNEqM0nrZWVIDGaE3cnTzpmwIfKyOdi-CgQ6f5PLQqhlmUci7cQKPqmI75OCLMvCkMUp59nscA\" alt=\"\" title=\"\"><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Creating a new Database<\/h2>\n\n\n\n<p>We will be creating a wrapper around MySQL queries that execute the MySQL commands.<\/p>\n\n\n\n<p>For example, the command to create a new database in MySQL is following.<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-subtle-pale-blue-background-color has-background\"><tbody><tr><td>CREATE DATABASE &#8220;database_name&#8221;<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p>We will use the above command in Python. Let\u2019s take a look at the code.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">import mysql.connector\ndb_connection = mysql.connector.connect(\nhost= \"localhost\",\nuser= \"root\",\npasswd= \"\"\n)\n# creating database_cursor to perform SQL operation\ndb_cursor = db_connection.cursor()\n# executing cursor with execute method and pass SQL query\ndb_cursor.execute(\"CREATE DATABASE k2kinfosys\")\n# get list of all databases\ndb_cursor.execute(\"SHOW DATABASES\")\n#print all databases\nfor db in db_cursor:\n&nbsp; print(db)<\/pre>\n\n\n\n<p>The following will be the output.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/lh5.googleusercontent.com\/Z5RiP0UotJVKxngLAOQ19B3tPnsDb-dky1-MbmN4jLOxqwSzCZwWqhLv6lDQ8EVqZR-iNj5eM_Chi8_djWrYVsq5kGgh200eroWkar7ReCGBsrrM4T_i8Lectenvl0rbqxyOO44\" alt=\"\" title=\"\"><\/figure>\n\n\n\n<p>As you can above that \u201ck2kinfosys\u201d database is created.<\/p>\n\n\n\n<p>Now let\u2019s add some data inside this database.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Creating a new Table.<\/h2>\n\n\n\n<p>Note: we will not run the \u201ccreate database command\u201d again as we have already created the database.<\/p>\n\n\n\n<p>The command to create a new table in a database is following.<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-subtle-pale-blue-background-color has-background\"><tbody><tr><td>CREATE&nbsp; TABLE articles (id num, aritcle_name VARCHAR(50))<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p>The following code will be used to create a table.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">import mysql.connector\n\ndb_connection = mysql.connector.connect(\n&nbsp; host=\"localhost\",\n&nbsp; user=\"root\",\n&nbsp; passwd=\"\",\n&nbsp; database=\"k2kinfosys\"\n)\ndb_cursor = db_connection.cursor()\n# Here creating database table as student'\ndb_cursor.execute(\"CREATE TABLE articles(aritcleId INT, articleName VARCHAR(50))\")\n# Get database table'\ndb_cursor.execute(\"SHOW TABLES\")\nfor table in db_cursor:\n&nbsp; print(table)<\/pre>\n\n\n\n<p>The following will be the output.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/lh6.googleusercontent.com\/v67V8h2N5pWz24Ig2z4DMQhEZ2mK9f4I-yN4w-0r66NAviBXxFuUD_pEXaSq6O_vh0g3qowcM2ANIQqPYXt2iywOJNGqEOyAIIXxd14c7ZubXz2_Qmi2sdQQphWnwSZQ1j6l7h4\" alt=\"\" title=\"\"><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Insert data into the table<\/h2>\n\n\n\n<p>First, we <a href=\"https:\/\/en.wikipedia.org\/wiki\/Database\" class=\"rank-math-link\" rel=\"nofollow noopener\" target=\"_blank\">created a database <\/a>then created a table inside the database let\u2019s add data into the table.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">import mysql.connector\n\ndb_connection = mysql.connector.connect(\n&nbsp; host=\"localhost\",\n&nbsp; user=\"root\",\n&nbsp; passwd=\"\",\n&nbsp; database=\"k2kinfosys\"\n)\ndb_cursor = db_connection.cursor()\narticle_sql_query = \"INSERT INTO articles(aritcleId,articleName) VALUES(01, 'Intro to Python')\"\n# Execute cursor and pass query as well as student data\ndb_cursor.execute(article_sql_query)\n\ndb_connection.commit()\nprint(db_cursor.rowcount, \"Record Inserted\")<\/pre>\n\n\n\n<p>The following will be the output.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/lh5.googleusercontent.com\/GfBh4Leq8T_bA5gP74AIfSjqfFd3dNGVT4nY7_C21NeoTzalXrll-nG9OSYOsD7TZwWhZNvmq3sZ6vkMN_xCGhbw6EL6CT_sD0qaaDFauKFol2xjoLYCXXZC8TqiUEC9WVTqVLY\" alt=\"\" title=\"\"><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>An application without a database can\u2019t remember information. Once you quit the application all the information will be lost. To store information that you either take from the user or the generated through complex computations database is used. In this tutorial, we will learn how to connect your Python code with the database to store [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":8267,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[342],"tags":[],"class_list":["post-8264","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-python-tutorials"],"_links":{"self":[{"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/posts\/8264","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/comments?post=8264"}],"version-history":[{"count":0,"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/posts\/8264\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/media\/8267"}],"wp:attachment":[{"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/media?parent=8264"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/categories?post=8264"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/tags?post=8264"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}