MySqldb connection in Python

Mysql is one of the most widely used open source databases. Python provides methods to connect to this database and use it to store and retrieve data.

Installing pymysql

Depending on the python environment you are using, the 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

Connect MySql

Now we can use the following code to connect to the Mysql environment. After connecting we are looking for the version of the database.

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 results-

Database version : 8.0.19

Executing database commands

In order to execute database commands , we create a database cursor and an Sql query to be passed to the cursor. Then we use the cursor.execute method to get the results 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 results -

fname = Jack, lname = Ma, age = 31, sex = M, income = 12000

The above is the detailed content of MySqldb connection in Python. For more information, please follow other related articles on the PHP Chinese website!