How do I connect to a MySQL database using a python program?
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, "-->", row # Close the connection db.close()
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 cursor.close () conn.close ()
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 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
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.
As a db driver, there is also oursql. Some of the reasons listed on that link, which say why oursql is better:
- oursql has real parameterization, sending the SQL and data to MySQL completely separately.
- oursql allows text or binary data to be streamed into the database and streamed out of the database, instead of requiring everything to be buffered in the client.
- oursql can both insert rows lazily and fetch rows lazily.
- oursql has unicode support on by default.
- oursql supports python 2.4 through 2.7 without any deprecation warnings on 2.6+ (see PEP 218) and without completely failing on 2.7 (see PEP 328).
- oursql runs natively on python 3.x.
So how to connect to mysql with oursql?
Very similar to mysqldb:
import oursql db_connection = oursql.connect(host='127.0.0.1',user='foo',passwd='foobar',db='db_name') cur=db_connection.cursor() cur.execute("SELECT * FROM `tbl_name`") for row in cur.fetchall(): print row
The tutorial in the documentation is pretty decent.
And of course for ORM SQLAlchemy is a good choice, as already mentioned in the other answers.
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".
For python 3.3
I have pip installed on my windows 7, just pip install cymysql
(you don't need cython) quick and painless
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()
Also take a look at Storm. It is a simple SQL mapping tool which allows you to easily edit and create SQL entries without writing the queries.
Here is a simple example:
from storm.locals import * # User will be the mapped object; you have to create the table before mapping it class User(object): __storm_table__ = "user" # table name ID = Int(primary=True) #field ID name= Unicode() # field name database = create_database("mysql://root:password@localhost:3306/databaseName") store = Store(database) user = User() user.name = u"Mark" print str(user.ID) # None store.add(user) store.flush() # ID is AUTO_INCREMENT print str(user.ID) # 1 (ID) store.commit() # commit all changes to the database
To find and object use:
michael = store.find(User, User.name == u"Michael").one() print str(user.ID) # 10
Find with primary key:
print store.get(User, 1).name #Mark
For further information see the tutorial.
Just a modification in above answer. Simply run this command to install mysql for python
sudo yum install MySQL-python sudo apt-get install MySQL-python
remember! It is case sensitive.
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.
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@example.com', '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, ('firstname.lastname@example.org',)) 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.
Despite all answers above, in case you do not want to connect to a specific database upfront, for example, if you want to create the database still (!), you can use
connection.select_db(database), as demonstrated in the following.
import pymysql.cursors connection = pymysql.connect(host='localhost', user='mahdi', password='mahdi', charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor) cursor = connection.cursor() cursor.execute("CREATE DATABASE IF NOT EXISTS "+database) connection.select_db(database) sql_create = "CREATE TABLE IF NOT EXISTS "+tablename+(timestamp DATETIME NOT NULL PRIMARY KEY)" cursor.execute(sql_create) connection.commit() cursor.close()
first install the driver
pip install MySQL-python
Then a basic code goes like this:
#!/usr/bin/python import MySQLdb try: db = MySQLdb.connect(host="localhost", # db server, can be a remote one db="mydb" # database user="mydb", # username passwd="mydb123", # password for this username ) # Create a Cursor object cur = db.cursor() # Create a query string. It can contain variables query_string = "SELECT * FROM MY_TABLE" # Execute the query cur.execute(query_string) # Get all the rows present the database for each_row in cur.fetchall(): print each_row # Close the connection db.close() except Exception, e: print 'Error ', e
mysqlclient is the best as others only provide support to specific versions of python
pip install mysqlclient
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())
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.
pip install sqlalchemy
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()
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()
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
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 cursor.close () conn.close ()
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
install connector using pip
pip install mysql-connector-python
or you can download the installer from https://dev.mysql.com/downloads/connector/python/
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.
cursorobject from connection object returned by
connect()method to execute SQL queries.
close the connection after your work completes.
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()
Important API of MySQL Connector Python
For DML operations - Use
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.fetchmany(SIZE)to fetch data
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：
select * from FOO;
immediatly execute in mysql terminal:
46410 rows in set (0.10 sec)
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()
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()
So, it seems that mysqlclient is much faster than pymysql
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()
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="yusername", 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 or update
For more commands: https://www.w3schools.com/python/python_mysql_getstarted.asp
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