Python: How To Connect To MySQL

Submitted by razormist on Wednesday, April 12, 2017 - 16:47.
In this tutorial we will try to connect Python to a MySQL database server. Python is a widely used advance-level programming language for a general technique to the developer. Beginners find the clean syntax and indentation structure easy to learn because of its less semi colon problem. That's why most of the new developer started to code with python. So let's now do the coding. Getting started: First you will have to download & install the Python IDLE's, here the link for the Integrated Development And Learning Environment for Python https://www.python.org/downloads/. Installing pymsql After you installed the Pythons IDLE's, open the windows command prompt and type pip install pymsql then run pip Creating Database To create database, open any kind of database server such as (wamp, xamp, etc.). Then go to phpmyadmin, after that click SQL then copy / paste it in the empty box and run.
  1. -- phpMyAdmin SQL Dump
  2. -- version 4.6.5.2
  3. -- https://www.phpmyadmin.net/
  4. --
  5. -- Host: 127.0.0.1
  6. -- Generation Time: Apr 12, 2017 at 05:51 AM
  7. -- Server version: 10.1.21-MariaDB
  8. -- PHP Version: 7.1.1
  9.  
  10. SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
  11. SET time_zone = "+00:00";
  12.  
  13.  
  14. /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
  15. /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
  16. /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
  17. /*!40101 SET NAMES utf8mb4 */;
  18.  
  19. --
  20. -- Database: `sample`
  21. --
  22.  
  23. -- --------------------------------------------------------
  24.  
  25. --
  26. -- Table structure for table `member`
  27. --
  28.  
  29. CREATE TABLE `member` (
  30.   `mem_id` int(11) NOT NULL,
  31.   `Name` varchar(50) NOT NULL
  33.  
  34. --
  35. -- Dumping data for table `member`
  36. --
  37.  
  38. INSERT INTO `member` (`mem_id`, `Name`) VALUES
  39. (1, 'Monkey D. Luffy'),
  40. (2, 'Kurosaki Ichigo'),
  41. (3, 'Albert Einstein'),
  42. (4, 'Son Goku'),
  43. (5, 'Clea Hela'),
  44. (6, 'Ellie Campbell'),
  45. (7, 'Uzumaki Naruto'),
  46. (8, 'Uchiha Sasuke'),
  47. (9, 'Haruna Sakura'),
  48. (10, 'Fuuka Akitsuki');
  49.  
  50. --
  51. -- Indexes for dumped tables
  52. --
  53.  
  54. --
  55. -- Indexes for table `member`
  56. --
  57. ALTER TABLE `member`
  58.   ADD PRIMARY KEY (`mem_id`);
  59.  
  60. --
  61. -- AUTO_INCREMENT for dumped tables
  62. --
  63.  
  64. --
  65. -- AUTO_INCREMENT for table `member`
  66. --
  67. ALTER TABLE `member`
  69. /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
  70. /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
  71. /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
Importing Modules After setting up the installation and the database, run the IDLE and click file and then new file. After that a new window will appear containing a black file this will be the text editor for the python. To create the application you need to import first a module called tkinter. Tkinter is the standard toolkit in python for creating GUI(Graphical User Interface). Then copy code that I provided below and paste it inside the IDLE text editor
  1. from tkinter import *
  2. from tkinter import ttk
  3. import pymysql
Creating The Main Function This is where all the logic happens, this function will import the MySQL database to Python when the bind button is clicked. To do that just copy the code below then paste it inside the IDLE text editor.
  1. app = Tk()
  2. app.title("Anime Character")
  3. app.geometry("300x200")
  4. app.resizable(0, 0)
  5. yscroll = Scrollbar(app, orient=VERTICAL)
  6. yscroll.pack(side=RIGHT, fill=Y)
  7. list1 = Listbox(app, height = 300, width = 300, yscrollcommand=yscroll.set)
  8. lbl = Label(app)
  9. btn_submit = ttk.Button(app, text = "Connect")
  10.  
  11. def dbConnect():
  12.     conn = pymysql.connect(host = 'localhost', user = 'root', password = '', db = 'sample')
  13.     cursor = conn.cursor()
  14.     cursor.execute('SELECT * FROM `member` ORDER BY `mem_id` ASC;')
  15.     res = cursor.fetchall()
  16.     for el in res:
  17.         list1.insert(1, el[1])
  18.     btn_submit.config(state = DISABLED)
  19.     lbl.config(text = "Connected to database")    
  20.     conn.close()
  21.     cursor.close()
  22.    
  23. lbl_title = Label(app, text = "Press button to connect to Database", fg = "red").pack()
  24. btn_submit.config(command = dbConnect)
  25. btn_submit.pack()
  26. lbl.pack(side = BOTTOM)
  27. yscroll.config(command=list1.yview)
  28. list1.pack()
Initializing the Application This structure of code will run the code and check if the main is initialize properly. To do that copy the code below and paste it inside the IDLE text editor.
  1. if __name__ == "__main__":
  2.     app.mainloop()
There you have it we enable to connect python to the MySQL database server that easily. I hope that this tutorial help you for what you are looking for. For more updates and tutorials just kindly visit this site. Enjoy Coding!!
Tags
Python
MYSQL

Add new comment

About text formats
  • 197 views