How do I connect to a MySQL Database in Python?

Connecting to MYSQL with Python 2 in three steps

1 - Setting

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.)

For Mac, you can install MySQLdb using Macport.

2 - Usage

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.

Here's one way to do it, using MySQLdb, which only supports Python 2:

#!/usr/bin/python
import MySQLdb

# Connect
db = MySQLdb.connect(host="localhost",
                     user="appuser",
                     passwd="",
                     db="onco")

cursor = db.cursor()

# Execute SQL select statement
cursor.execute("SELECT * FROM location")

# Commit your changes if writing
# In this case, we are only reading data
# db.commit()

# Get the number of rows in the resultset
numrows = cursor.rowcount

# Get and display one row at a time
for x in range(0, numrows):
    row = cursor.fetchone()
    print row[0], "-->", row[1]

# Close the connection
db.close()

Reference here

Try using MySQLdb. MySQLdb only supports Python 2.

There is a how to page here: http://www.kitebird.com/articles/pydbapi.html

From the page:

# server_version.py - retrieve and display database server version

import MySQLdb

conn = MySQLdb.connect (host = "localhost",
                        user = "testuser",
                        passwd = "testpass",
                        db = "test")
cursor = conn.cursor ()
cursor.execute ("SELECT VERSION()")
row = cursor.fetchone ()
print "server version:", row[0]
cursor.close ()
conn.close ()

Run this command in your terminal to install mysql connector:

pip install mysql-connector-python

And run this in your python editor to connect to MySQL:

import mysql.connector

mydb = mysql.connector.connect(
      host="localhost",
      user="username",
      passwd="password",
      database="database_name"
)

Samples to execute MySQL Commands (in your python edior):

mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))")    
mycursor.execute("SHOW TABLES")

mycursor.execute("INSERT INTO customers (name, address) VALUES ('John', 'Highway 21')")    
mydb.commit() # Use this command after insert, update, delete commands

For more commands: https://www.w3schools.com/python/python_mysql_getstarted.asp

Best way to connect to MySQL from python is to Use MySQL Connector/Python because it is official Oracle driver for MySQL for working with Python and it works with both Python 3 and Python 2.

follow the steps mentioned below to connect MySQL

1. install connector using pip

   pip install mysql-connector-python

or you can download the installer from https://dev.mysql.com/downloads/connector/python/

2. Use connect() method of mysql connector python to connect to MySQL.pass the required argument to connect() method. i.e. Host, username, password, and database name.

3. Create cursor object from connection object returned by connect()method to execute SQL queries.

4. close the connection after your work completes.

Example:

import mysql.connector
 from mysql.connector import Error
 try:
     conn = mysql.connector.connect(host='hostname',
                         database='db',
                         user='root',
                         password='passcode')
     if conn.is_connected():
       cursor = conn.cursor()
       cursor.execute("select database();")
       record = cursor.fetchall()
       print ("You're connected to - ", record)
 except Error as e :
    print ("Print your error msg", e)
 finally:
    #closing database connection.
    if(conn.is_connected()):
       cursor.close()
       conn.close()

Reference - https://pynative.com/python-mysql-database-connection/

Important API of MySQL Connector Python

• For DML operations - Use cursor.execute() and cursor.executemany() to run query. and after this use connection.commit() to persist your changes to DB

• To fetch data - Use cursor.execute() to run query and cursor.fetchall(), cursor.fetchone(), cursor.fetchmany(SIZE) to fetch data

Even though some of you may mark this as a duplicate and get upset that I am copying someone else's answer, I would REALLY like to highlight an aspect of Mr. Napik's response. Because I missed this, I caused nationwide website downtime (9min). If only someone shared this information, I could have prevented it!

Here is his code:

import mysql.connector    
cnx = mysql.connector.connect(user='scott', password='tiger',
                              host='127.0.0.1',
                              database='employees')
try:
   cursor = cnx.cursor()
   cursor.execute("""select 3 from your_table""")
   result = cursor.fetchall()
   print(result)
finally:
    cnx.close()

The important thing here is the Try and Finally clause. This allows connections to ALWAYS be closed, regardless of what happens in the cursor/sqlstatement portion of the code. A lot of active connections cause DBLoadNoCPU to spike and could crash a db server.

I hope this warning helps to save servers and ultimately jobs! :D

MySQLdb is the straightforward way. You get to execute SQL queries over a connection. Period.

My preferred way, which is also pythonic, is to use the mighty SQLAlchemy instead. Here is a query related tutorial, and here is a tutorial on ORM capabilities of SQLALchemy.

for Python3.6 I found two driver: pymysql and mysqlclient. I tested the performance between them and got the result: the mysqlclient is faster.

below is my test process(need install python lib profilehooks to analyze time elapse：

raw sql: select * from FOO;

immediatly execute in mysql terminal: 46410 rows in set (0.10 sec)

pymysql (2.4s):

from profilehooks import profile
import pymysql.cursors
import pymysql
connection = pymysql.connect(host='localhost', user='root', db='foo')
c = connection.cursor()

@profile(immediate=True)
def read_by_pymysql():
    c.execute("select * from FOO;")
    res = c.fetchall()

read_by_pymysql()

here's the pymysql profile:

mysqlclient (0.4s)

from profilehooks import profile
import MySQLdb

connection = MySQLdb.connect(host='localhost', user='root', db='foo')
c = connection.cursor()

@profile(immediate=True)
def read_by_mysqlclient():
    c.execute("select * from FOO;")
    res = c.fetchall()

read_by_mysqlclient()

here's the mysqlclient profile:

So, it seems that mysqlclient is much faster than pymysql

mysqlclient is the best as others only provide support to specific versions of python

 pip install mysqlclient

example code

    import mysql.connector
    import _mysql
    db=_mysql.connect("127.0.0.1","root","umer","sys")
    #db=_mysql.connect(host,user,password,db)
    # Example of how to insert new values:
    db.query("""INSERT INTO table1 VALUES ('01', 'myname')""")
    db.store_result()
    db.query("SELECT * FROM new1.table1 ;") 
    #new1 is scheme table1 is table mysql 
    res= db.store_result()
    for i in range(res.num_rows()):
        print(result.fetch_row())

see https://github.com/PyMySQL/mysqlclient-python

This is Mysql DB connection

from flask import Flask, render_template, request
from flask_mysqldb import MySQL

app = Flask(__name__)


app.config['MYSQL_HOST'] = 'localhost'
app.config['MYSQL_USER'] = 'root'
app.config['MYSQL_PASSWORD'] = 'root'
app.config['MYSQL_DB'] = 'MyDB'

mysql = MySQL(app)


@app.route('/', methods=['GET', 'POST']) 
def index():
    if request.method == "POST":
        details = request.form
        cur = mysql.connection.cursor()
        cur.execute ("_Your query_")
        mysql.connection.commit()
        cur.close()
        return 'success'
    return render_template('index.html')


if __name__ == '__main__':
    app.run()

PyMySQL 0.10.1 - Released: Sep 10, 2020, has support for python3 as well.

python3 -m pip install PyMySQL

Simple code:

import pymysql

# Connect to the database
conn = pymysql.connect(host='127.0.0.1',user='root',passwd='root',db='fax')

# Create a Cursor object
cur = conn.cursor()

# Execute the query
cur.execute("SELECT * FROM fax.student")

# Read and print records
for row in cur.fetchall():
    print(row)

output:

(1, 'Petar', 'Petrovic', 1813, 'Njegusi')
(2, 'Donald', 'Tramp', 1946, 'New York')
(3, 'Bill', 'Gates', 1955, 'Seattle')

you can connect your python code to mysql in this way.

import MySQLdb
db = MySQLdb.connect(host="localhost",
                 user="appuser",
                 passwd="",
                 db="onco")

cursor = db.cursor()

First step to get The Library: Open terminal and execute pip install mysql-python-connector. After the installation go the second step.

Second Step to import the library: Open your python file and write the following code: import mysql.connector

Third step to connect to the server: Write the following code:

conn = mysql.connector.connect(host=you host name like localhost or 127.0.0.1, username=your username like root, password = your password)

Third step Making the cursor: Making a cursor makes it easy for us to run queries. To make the cursor use the following code: cursor = conn.cursor()

Executing queries: For executing queries you can do the following: cursor.execute(query)

If the query changes any thing in the table you need to add the following code after the execution of the query: conn.commit()

Getting values from a query: If you want to get values from a query then you can do the following: cursor.excecute('SELECT * FROM table_name') for i in cursor: print(i) #Or for i in cursor.fetchall(): print(i)

The fetchall() method returns a list with many tuples that contain the values that you requested ,row after row .

Closing the connection: To close the connection you should use the following code: conn.close()

Handling exception: To Handel exception you can do it Vai the following method: try: #Logic pass except mysql.connector.errors.Error: #Logic pass To use a database: For example you are a account creating system where you are storing the data in a database named blabla, you can just add a database parameter to the connect() method ,like

mysql.connector.connect(database = database name)

don't remove other informations like host,username,password.

First install the driver (Ubuntu)

• sudo apt-get install python-pip

• sudo pip install -U pip

• sudo apt-get install python-dev libmysqlclient-dev

• sudo apt-get install MySQL-python

MySQL database connection codes

import MySQLdb
conn = MySQLdb.connect (host = "localhost",user = "root",passwd = "pass",db = "dbname")
cursor = conn.cursor ()
cursor.execute ("SELECT VERSION()")
row = cursor.fetchone ()
print "server version:", row[0]
cursor.close ()
conn.close ()

Python does not come with an inbuilt Library to interact with MySQL, so in order to make a connection between the MySQL database and Python we need to install the MySQL driver or module for our Python Environment.

pip install mysql-connector-python

the mysql-connecter-python is an open source Python library that can connect your python code to the MySQL data base in a few lines of code. And it is very compatible with the latest version of Python.

After install the mysql-connector-python, you can connect to your MySQL database using the the following code snippet.

import mysql.connector

Hostname = "localhost"
Username = "root"
Password ="admin"   #enter your MySQL password
 
#set connection
set_db_conn = mysql.connector.connect(host= Hostname, user=Username, password=Password)

if set_db_conn:
    print("The Connection between has been set and the Connection ID is:")
    #show connection id
    print(set_db_conn.connection_id)

Connect Django with MySQL

In Django, to connect your model or project to the MySQL data base, you need to install the mysqlclient library.

pip install mysqlclient

And to configure your Django setting so your project can connect to the MySQL database, you can use the following setting.

DATABASES = {
        'default': {
            'ENGINE': 'django.db.backends.mysql',
            'NAME': 'database_name',
            'USER': 'username',
            'PASSWORD': 'databasepassword@123',
            'HOST': 'localhost',   # Or an IP Address that your DB is hosted on
            'PORT': '3306',
            }

I have written a dedicated Python tutorial on my blog that covers how you can connect to a MySQL database and create tables using Python. To know more about it, click here.

Here's what I do, if the database doesn't exist yet, it creates for you:

import sqlite3

conn = sqlite3.connect('data.db')
c = conn.cursor()

def create_table():
    c.execute('CREATE TABLE IF NOT EXISTS usersdata(idpedido INTEGER PRIMARY KEY ,cliente TEXT ,telefone TEXT ,prioridade TEXT ,data_entrada DATE ,data_prevista DATE ,horario_entrada TEXT , horario_saida TEXT , status TEXT)')


def add_data(idpedido,cliente,telefone,prioridade,data_entrada,data_prevista,horario_entrada,horario_saida,status):
    c.execute('INSERT INTO usersdata(idpedido,cliente,telefone,prioridade,data_entrada,data_prevista,horario_entrada,horario_saida,status ) VALUES (?,?,?,?,?,?,?,?,?)',(idpedido,cliente,telefone,prioridade,data_entrada,data_prevista,horario_entrada,horario_saida, status))
    conn.commit()

First, install python-mysql connector from https://dev.mysql.com/downloads/connector/python/

on Python console enter:

pip install mysql-connector-python-rf
import mysql.connector