How do I connect to a MySQL Database in Python?

0 votes
How do I connect to a MySQL database using a python program?
Sep 18, 2018 in Python by bug_seeker
• 14,980 points
74 views

2 answers to this question.

Your answer

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.
0 votes

Connecting to MYSQL with Python in 3 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.

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.

answered Sep 18, 2018 by Priyaj
• 56,140 points
0 votes

connect mysql database with python

import MySQLdb
db = MySQLdb.connect(host="localhost", 
                     user="rajesh",         # your username
                     passwd="kumar",         # your password
                     db="emp")           # name of the data base
create a Cursor object. you execute all the queries you need cursor
cur = db.cursor()
cur.execute("SELECT * FROM YOUR_TABLE_NAME")# execute query
# print all the first cell of all the rows
for row in cur.fetchall():
    print row[0]
db.close()

this is the way to connect mysql database with python if you know more about python

,mysql and cursor related topic than you can join best industrial training in python.

answered Mar 28 by rajesh
• 1,210 points

Related Questions In Python

0 votes
1 answer

How do I use urllib to see if a website is 404 or 200 in Python?

For Python 3, try doing this: import urllib.request, ...READ MORE

answered Nov 29, 2018 in Python by Nymeria
• 3,500 points

edited Dec 11, 2018 by Nymeria 180 views
0 votes
2 answers

How do I copy a file in python?

copy a file in python  from shutil ...READ MORE

answered Mar 27 in Python by rajesh
• 1,210 points
23 views
0 votes
1 answer

Connect to a MySQL DB in python?

db = MySQLdb.connect(host="localhost", # ...READ MORE

answered Jul 20, 2018 in Python by Nietzsche's daemon
• 4,260 points
40 views
0 votes
1 answer

In Python, how do I read a file line-by-line into a list?

with open(fname) as f:     content = f.readlines() # you ...READ MORE

answered Oct 9, 2018 in Python by SDeb
• 10,700 points
75 views
0 votes
1 answer

ProgrammingError: not all arguments converted during string formatting

Sorted!!!! just found the solution, 1 - apparently ...READ MORE

answered Sep 10, 2018 in Python by Priyaj
• 56,140 points
1,826 views
0 votes
1 answer

ProgrammingError: not all arguments converted during string formatting

Sorted!!!! just found the solution, 1 - apparently ...READ MORE

answered Sep 20, 2018 in Python by Priyaj
• 56,140 points
340 views
0 votes
1 answer

Connect to a MySQL using Python?

Very similar to mysqldb but better than ...READ MORE

answered Nov 16, 2018 in Python by Jino
• 5,520 points
29 views
0 votes
1 answer

ssh first with mysqldb in python

Setup an ssh tunnel before you use ...READ MORE

answered May 23 in Python by ana1504.k
• 6,630 points
13 views
0 votes
1 answer

How do I copy a file in python?

Use the shutil module. copyfile(src, dst) Copy the contents ...READ MORE

answered Jul 31, 2018 in Python by Priyaj
• 56,140 points
25 views
–1 vote
2 answers

How to find the size of a string in Python?

following way to find length of string  x ...READ MORE

answered Mar 29 in Python by rajesh
• 1,210 points
50 views

© 2018 Brain4ce Education Solutions Pvt. Ltd. All rights Reserved.
"PMP®","PMI®", "PMI-ACP®" and "PMBOK®" are registered marks of the Project Management Institute, Inc. MongoDB®, Mongo and the leaf logo are the registered trademarks of MongoDB, Inc.