How do I connect to a MySQL Database in Python?
Most answers here focus on installing MySQLdb library, I would really suggest opting for MySQL Connector/Python provided by MySQL/Oracle, which makes the process much simpler: stackoverflow.com/questions/372885/…
The problem with using Oracle’s Connector/Python is that it has subtle bugs and other integration issues. It’s easy to install, but nearly impossible to get to work for all the real-world use cases I’ve tried it for. Hence why I always recommend MySQLdb.
w3schools.com/python/python_mysql_create_db.asp : I know that senior devs looks down upon w3school tutorials but they are a good started. Go though it !
26 Answers 26
Connecting to MYSQL with Python 2 in three steps
You must install a MySQL driver before doing anything. Unlike PHP, Only the SQLite driver is installed by default with Python. The most used package to do so is MySQLdb but it’s hard to install it using easy_install. Please note MySQLdb only supports Python 2.
For Windows user, you can get an exe of MySQLdb.
For Linux, this is a casual package (python-mysqldb). (You can use sudo apt-get install python-mysqldb (for debian based distros), yum install MySQL-python (for rpm-based), or dnf install python-mysql (for modern fedora distro) in command line to download.)
After installing, Reboot. This is not mandatory, But it will prevent me from answering 3 or 4 other questions in this post if something goes wrong. So please reboot.
Then it is just like using any other package :
#!/usr/bin/python import MySQLdb db = MySQLdb.connect(host="localhost", # your host, usually localhost user="john", # your username passwd="megajonhy", # your password db="jonhydb") # name of the data base # you must create a Cursor object. It will let # you execute all the queries you need cur = db.cursor() # Use all the SQL you like cur.execute("SELECT * FROM YOUR_TABLE_NAME") # print all the first cell of all the rows for row in cur.fetchall(): print row[0] db.close() Of course, there are thousand of possibilities and options; this is a very basic example. You will have to look at the documentation. A good starting point.
3 — More advanced usage
Once you know how it works, You may want to use an ORM to avoid writing SQL manually and manipulate your tables as they were Python objects. The most famous ORM in the Python community is SQLAlchemy.
I strongly advise you to use it: your life is going to be much easier.
I recently discovered another jewel in the Python world: peewee. It’s a very lite ORM, really easy and fast to setup then use. It makes my day for small projects or stand alone apps, Where using big tools like SQLAlchemy or Django is overkill :
import peewee from peewee import * db = MySQLDatabase('jonhydb', user='john', passwd='megajonhy') class Book(peewee.Model): author = peewee.CharField() title = peewee.TextField() class Meta: database = db Book.create_table() book = Book(author="me", title='Peewee is cool') book.save() for book in Book.filter(author="me"): print book.title This example works out of the box. Nothing other than having peewee ( pip install peewee ) is required.
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')`