How to use MySQL with Python: A beginners guide
SQL and Python are two of the most valuable tools for data analytics. The real-world data used in Machine Learning applications are usually not available in CSV formats like they
are in Kaggle competitions but rather in databases. The structured data usually are stored in relational databases. And to pull data from these Relational Database Systems(RDBMS) SQL (Structured Query Language) is used. But we can only do so much with SQL but with the help of Python, we can do many more things from analysis to visualization of these data. So, in this article, we are going to discuss how we can connect and use Mysql databases from Python. But before that let’s get to know the advantages of using SQL in Python.
Why SQL with Python?
At this point, it’s trivial to discuss the impact of Python on Machine Learning. Vast library support with a relatively easy learning curve makes it ideal for Machine Learning. As it lets us spend less time juggling with codes and more time optimizing algorithms. Libraries like Numpy, and pandas make data analysis so much easier, while Matplotlib, Seaborn, and Plotly can make attractive plots with few lines of code. For any kind of statistical analysis there are Scipy and Statsmodels and for machine learning TensorFlow, Pytorch, and Scikit Learn. On top of all these things a vast ever-growing community. SQL on the other hand is imperative if you are working with structured data. Like I said before real-world data usually gets stored in databases so retrieving those data requires a specific scripting language and for relational databases it is SQL. Combining Python with SQL makes it even more flexible to work with data. For example, with Python, we can do any statistical analysis such as Hypothesis testing quite easily and also can fetch data for different Machine Learning applications directly from databases. So, let’s get started.
Getting Started
Before proceeding to the code part let’s set up our system. In this tutorial, we will need MySQL and MySQL connectors. If you haven’t already installed MySQL in your system then download the MySQL installer and take care of the entire setup. For MySQL connector type the following code in your Jupyter Notebook or you can do it within the shell as well by just skipping ‘!’.
`!pip install mysql-connector-python` `import mysql.connector import pandas as pd` Here, the MySQL connector will help us connect to our database and Pandas as usual will be used for some data modifications.
Connecting to MySQL
The next thing is to connect to the MySQL server. This will help us communicate with the MySQL server to work with relational databases.
`mydb = mysql.connector.connect( host="localhost", user="root", password="******", ) print(mydb)`
Be sure to use the correct username and password. To make it more elegant you can always use it inside a function to make the code reusable. Now, we will initialize a cursor object. It can be thought of as a bridge between queries and MySQL databases. They are bound to the current connection for a lifetime until the connection is terminated. This help executes MySQL queries. mycursor = mydb.cursor(buffered=True)
The buffering argument is by default set to False but we set it to true as it will return the outcome rows after being executed. SQL Queries
Now, let’s get to the main thing. In this section, we will be executing SQL queries to create, alter and delete database elements. We can simply create our database by executing the following code.
`mycursor.execute("create database testdb")` `mycursor.execute("SHOW DATABASES") `for x in mycursor:` ` print(x)`` `mycursor.execute("use mydatabase")` `mycursor.execute("show tables") `for x in mycursor: `print(x)` Creating a Table
`mycursor.execute("create table student (SId int(10), Name varchar(20), City varchar(20) )")` If you are not already familiar with SQL, refer to this article for an introduction to different types of queries. The above code creates a table named student in your selected database with field or column names with a specified data type and length.
Adding Data to Table
Data into the table can easily be fed through the following code snippet
`mycursor.execute("""insert into student values ("1","Alex", "Patna"), ("2","Alexa", "Georgia") ,("3","Frieza", "Pearl Harbour"), ("4","Siri", "Pretoria")""")`` Here, we used triple quotation marks for multi-line string commands. Let’s now see how our data table looks like
`mycursor.execute("select * from student") st = mycursor.fetchall() for i in st: print(i) ('1', 'Alex', 'Patna') ('2', 'Alexa', 'Georgia') ('3', 'Frieza', 'Pearl Harbour') ('4', 'Siri', 'Pretoria')`