How do I connect to a MySQL Database in Python?

First, install python-mysql connector from

[To see links please register here]

on Python console enter:

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

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

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:
[![enter image description here][1]][1]





<br>
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:
[![enter image description here][2]][2]


So, it seems that mysqlclient is much faster than pymysql


[1]:

[2]:

SqlAlchemy
==========
----------

> SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that
> gives application developers the full power and flexibility of SQL.
> SQLAlchemy provides a full suite of well known enterprise-level
> persistence patterns, designed for efficient and high-performing
> database access, adapted into a simple and Pythonic domain language.

Installation
------------

pip install sqlalchemy

RAW query
-----

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, scoped_session

engine = create_engine("mysql://<user_name>:<password>@<host_name>/<db_name>")
session_obj = sessionmaker(bind=engine)
session = scoped_session(session_obj)

# insert into database
session.execute("insert into person values(2, 'random_name')")
session.flush()
session.commit()


ORM way
-----

from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, scoped_session

Base = declarative_base()
engine = create_engine("mysql://<user_name>:<password>@<host_name>/<db_name>")
session_obj = sessionmaker(bind=engine)
session = scoped_session(session_obj)

# Bind the engine to the metadata of the Base class so that the
# declaratives can be accessed through a DBSession instance
Base.metadata.bind = engine

class Person(Base):
__tablename__ = 'person'
# Here we define columns for the table person
# Notice that each column is also a normal Python instance attribute.
id = Column(Integer, primary_key=True)
name = Column(String(250), nullable=False)

# insert into database
person_obj = Person(id=12, name="name")
session.add(person_obj)
session.flush()
session.commit()

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

<!-- language-all: lang-py -->

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][1] 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][2].

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][3].

**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][4].

**3 - More advanced usage**

Once you know how it works, You may want to use an [ORM][5] to avoid writing SQL manually and manipulate your tables as they were Python objects. The most famous ORM in the Python community is [SQLAlchemy][6].

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][7]. 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.



[1]:

[To see links please register here]

[2]:

[To see links please register here]

[3]:

[To see links please register here]

[4]:

[To see links please register here]

[5]:

[To see links please register here]

[6]:

[To see links please register here]

[7]:

[To see links please register here]

Here's one way to do it, using [MySQLdb][1], 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()

<a href="http://it.toolbox.com/blogs/lim/connecting-to-mysql-from-python-4529">Reference here</a>


[1]:

[To see links please register here]

Try using [MySQLdb][1]. MySQLdb only supports Python 2.

There is a how to page here: [

[To see links please register here]

][2]

---

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


[1]:

[To see links please register here]

[2]:

[To see links please register here]

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