MySqldb Connection in Python

PythonServer Side ProgrammingProgrammingMySQLDatabase

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
raja
Published on 04-Feb-2020 11:13:44
Advertisements