Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
MySqldb Connection in Python
Mysql is one of the most widely used open source Dbs. Python provides ways to connect to this DB and use the DB to store and retrive the data from it.
Install pymysql
Depending on the python environment you are using, pymysql package can be installed using one of the following methods.
# From python console pip install pymysql #Using Anaconda conda install -c anaconda pymysql # Add modules using any python IDE pymysql
Connecting to MySql
Now we can connect to the Mysql environment using the following code. After connecting we are finding out the version of the DB.
Example
import pymysql
# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )
# prepare a cursor object using cursor() method
cursor = db.cursor()
# execute SQL query using execute() method.
cursor.execute("SELECT VERSION()")
# Fetch a single row using fetchone() method.
data = cursor.fetchone()
print ("Database version : %s " % data)
# disconnect from server
db.close()Output
Running the above code gives us the following result −
Database version : 8.0.19
Executing DB commands
To execute the DB commands we cerate a db cursor and a Sql query to be passed onto that cursor. Then we use cursor.execute method to get the result out of the cursor execution.
Example
import pymysql
# Open database connection
db = pymysql.connect("localhost","username","paswd","DBname" )
# prepare a cursor object using cursor() method
cursor = db.cursor()
sql = "SELECT * FROM EMPLOYEE \
WHERE INCOME > '%d'" % (1000)
try:
# Execute the SQL command
cursor.execute(sql)
# Fetch all the rows in a list of lists.
results = cursor.fetchall()
for row in results:
fname = row[0]
lname = row[1]
age = row[2]
sex = row[3]
income = row[4]
# Now print fetched result
print "fname=%s,lname=%s,age=%d,sex=%s,income=%d" % \
(fname, lname, age, sex, income )
except:
print "Error: unable to fecth data"
# disconnect from server
db.close()Output
Running the above code gives us the following result −
fname = Jack, lname = Ma, age = 31, sex = M, income = 12000
Updated on: 04-Feb-2020
193 Views
Advertisements