Advertisement
Advertisement


How do I connect to a MySQL Database in Python?


Question

How do I connect to a MySQL database using a python program?

2011/07/28
1
1172
7/28/2011 5:48:49 PM


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

2019/04/18

If you do not need MySQLdb, but would accept any library, I would very, very much recommend MySQL Connector/Python from MySQL: http://dev.mysql.com/downloads/connector/python/.

It is one package (around 110k), pure Python, so it is system independent, and dead simple to install. You just download, double-click, confirm license agreement and go. There is no need for Xcode, MacPorts, compiling, restarting …

Then you connect like:

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()
2014/12/05

Oracle (MySQL) now supports a pure Python connector. That means no binaries to install: it's just a Python library. It's called "Connector/Python".

http://dev.mysql.com/downloads/connector/python/

2013/05/28

Stop Using MySQLDb if you want to avoid installing mysql headers just to access mysql from python.

Use pymysql. It does all of what MySQLDb does, but it was implemented purely in Python with NO External Dependencies. This makes the installation process on all operating systems consistent and easy. pymysql is a drop in replacement for MySQLDb and IMHO there is no reason to ever use MySQLDb for anything... EVER! - PTSD from installing MySQLDb on Mac OSX and *Nix systems, but that's just me.

Installation

pip install pymysql

That's it... you are ready to play.

Example usage from pymysql Github repo

import pymysql.cursors
import pymysql

# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='user',
                             password='passwd',
                             db='db',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

try:
    with connection.cursor() as cursor:
        # Create a new record
        sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
        cursor.execute(sql, ('[email protected]', 'very-secret'))

    # connection is not autocommit by default. So you must commit to save
    # your changes.
    connection.commit()

    with connection.cursor() as cursor:
        # Read a single record
        sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"
        cursor.execute(sql, ('[email protected]',))
        result = cursor.fetchone()
        print(result)
finally:
    connection.close()

ALSO - Replace MySQLdb in existing code quickly and transparently

If you have existing code that uses MySQLdb, you can easily replace it with pymysql using this simple process:

# import MySQLdb << Remove this line and replace with:
import pymysql
pymysql.install_as_MySQLdb()

All subsequent references to MySQLdb will use pymysql transparently.

2017/09/08

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 ()
2019/04/18

Source: https://stackoverflow.com/questions/372885
Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]