- Python Basics
- Python Home
- Python History
- Python Applications
- Python Features
- Python Versions
- Python Environment Setup
- Python Basic Syntax
- Python end (end=)
- Python sep (sep=)
- Python Comments
- Python Identifiers
- Python Variables
- Python Operators
- Python Ternary Operator
- Python Operator Precedence
- Python Control & Decision
- Python Decision Making
- Python if elif else
- Python Loops
- Python for Loop
- Python while Loop
- Python break Statement
- Python continue Statement
- Python pass Statement
- Python break Vs continue
- Python pass Vs continue
- Python Built-in Types
- Python Data Types
- Python Lists
- Python Tuples
- Python Sets
- Python frozenset
- Python Dictionary
- List Vs Tuple Vs Dict Vs Set
- Python Numbers
- Python Strings
- Python bytes
- Python bytearray
- Python memoryview
- Python Misc Topics
- Python Functions
- Python Variable Scope
- Python Enumeration
- Python import Statement
- Python Modules
- Python operator Module
- Python os Module
- Python Date & Time
- Python Exception Handling
- Python File Handling
- Python Advanced
- Python Classes & Objects
- Python @classmethod Decorator
- Python @staticmethod Decorator
- Python Class Vs Static Method
- Python @property Decorator
- Python Regular Expressions
- Python CGI Programming
- Python Network Programming
- Python Send E-mail
- Python Multi-threading
- Python XML Processing
- Python MySQL Database
- Python GUI Programming
- Python Event Handling
- Python Keywords
- Python All Keywords
- Python and
- Python or
- Python not
- Python True
- Python False
- Python None
- Python in
- Python is
- Python as
- Python with
- Python yield
- Python return
- Python del
- Python from
- Python lambda
- Python assert
- Python Built-in Functions
- Python All Built-in Functions
- Python print() Function
- Python input() Function
- Python int() Function
- Python len() Function
- Python range() Function
- Python str() Function
- Python ord() Function
- Python chr() Function
- Python read()
- Python write()
- Python open()
- Python Examples
- Python Examples
- Python Test
- Python Online Test
- Give Online Test
- All Test List
Python MySQL Database Access
MySQLdb is simply an interface for connecting to the MySQL database server from python. To install MySQLdb module, download it. Here is the link to download, MySQLdb Download. Now proceed the following:
$ gunzip MySQL-python-1.2.2.tar.gz $ tar -xvf MySQL-python-1.2.2.tar $ cd MySQL-python-1.2.2 $ python setup.py build $ python setup.py install
Create Database Connection in Python
Make sure these things before connecting to the MySQL database:
- Make sure that you have created a database named MYDATABASE
- Make sure that you have created a table named STUDENT in MYDATABASE
- Make sure that this table has fields namely FIRST_NAME, LAST_NAME, AGE, SEX, and FEE
- Make sure that the user id is "checkuser" and password is "checkpass123" are set to access the database MYDATABASE
- Make sure that the python module MySQLdb is successfully and properly installed on your machine
Here is an example of connecting with MySQL database named MYDATABASE
# Python MySQL Database Access - Example Program import MySQLdb db=MySQLdb.connect("localhost","checkuser","checkpass123","MYDATABASE") #this opens database connection cursor = db.cursor() cursor.execute("SELECT VERSION()") data = cursor.fetchone() print("Database version: %s " % data) db.close() # this disconnect from the server
Here is the output produced by the above python script on Linux platform:
Database version : 5.0.45
Create Database Table in Python
Once a connection to the database is established, then we are able to create the tables or records into the database tables. Here is an example of creating a table:
# Python Database Access - Create a Database Table - Example Program import MySQLdb db = MySQLdb.connect("localhost","checkuser","checkpass123","MYDATABASE") cursor = db.cursor() cursor.execute("DROP TABLE IF EXISTS STUDENT") sql = """CREATE TABLE STUDENT( FIRST_NAME CHAR(20) NOT NULL, LAST_NAME CHAR(20), AGE INT, SEX CHAR(1), FEE FLOAT )""" cursor.execute(sql) db.close()
Insert Record in Database Table in Python
Insert statement is used to insert records into the table like this:
# Python MySQL Database Access - Example Program import MySQLdb db = MySQLdb.connect("localhost","checkuser","checkpass123","MYDATABASE") cursor = db.cursor() sql = """INSERT INTO STUDENT(FIRST_NAME, LAST_NAME, AGE, SEX, FEE) VALUES ('Richard', 'Jones', 20, 'M', 2000)""" try: # Execute the SQL command cursor.execute(sql) db.commit() except: db.rollback() db.close()
Read Record back from the Database Table in Python
Here is an example program, reads all the records from the table, named STUDENT having fee more than 1000:
# Python MySQL Database Access - Example Program import MySQLdb db = MySQLdb.connect("localhost","checkuser","checkpass123","MYDATABASE") cursor = db.cursor() sql = "SELECT * FROM STUDENT \ WHERE FEE > '%d'" % (1000) try: cursor.execute(sql) results = cursor.fetchall() for row in results: fname = row[0] lname = row[1] age = row[2] sex = row[3] fee = row[4] print("fname=%s,lname=%s,age=%d,sex=%s,fee=%d" % \ (fname, lname, age, sex, fee)) except: print "Error: unable to fecth data" db.close()
It will produce the following result:
fname=Richard, lname=Jones, age=20, sex=M, fee=2000
« Previous Tutorial Next Tutorial »