How to Connect a Database to Python

How to Connect a Database to Python

It is helpful to understand how to connect a database to Python scripts for serving dynamically generated web pages and collaborative reports. Python is almost always included in Linux distributions and used for multiple applications already. You don’t need PHP for this.

Below we’ll cover how to create a Python database connection (MySQL/MariaDB) in the Linux terminal.

How to Connect a Database to Python 2.7

  1. Log into SSH.
  2. From your website root directory, create a Python script file in the “cgi-bin” directory:
    touch cgi-bin/test-db.py
  3. Change the file’s permissions to 755:
    chmod 755 cgi-bin/test-db.py
  4. If you wish to execute Python scripts in web browsers, edit your Apache .htaccess file:
    nano .htaccess
  5. Add the following at the top of the file and save changes:
    AddHandler cgi-script .py
  6. To complete the Python database connection you’ll need to know the database host (“localhost” if on the same system), name, username, and user password.
  7. Run Python:
    python
  8. Ensure you have the MySQL Python module installed:
    import MySQLdb
    If you receive no notification, that means it is installed. You’ll need to install the module if you receive the error “ImportError: No module named mysqldb.”
  9. Exit Python:
    exit ()
  10. If you need to install it, we recommend using your OS repositories. You can also use PIP.
    Alma / Enterprise Linux:
    sudo yum install MySQL-python

    Ubuntu:
    sudo apt-get install python-pip python-dev libmysqlclient-dev

    PIP:
    pip install MySQL-python
  11. Edit your Python script:
    nano cgi-bin/test-db.py
  12. Insert the code below to connect to the database and run “SELECT VERSION(),” which shows our current version of MySQL. Replace the database user, password, and database.
    #!/usr/bin/env python
    import MySQLdb
    # connect to the database
    db = MySQLdb.connect("localhost","user","password","database" )
    # setup a cursor object using cursor() method
    cursor = db.cursor()
    # run an sql question
    cursor.execute("SELECT VERSION()")
    # grab one result
    data = cursor.fetchone()
    # begin printing data to the screen
    print "Content-Type: text/html"
    print
    print """
    <!DOCTYPE html>
    <html>
    <head>
    <title>Python - Hello World</title>
    </head>
    <body>
    """
    print "Database version : %s " % data
    print"""
    </body>
    </html>
    """
    # close the mysql database connection
    db.close()
  13. Save changes.
  14. Run the Python script:
    python test-db.py
    The results should show basic HTML markup and your current database version.

You can also visit the Python script URL in the web browser if you updated your web server configuration file. You’ll see the database version line.

Congrats on learning how to connect a database to Python 2.7+. Learn more about programming with Python.

If you don’t need cPanel, don't pay for it. Only pay for what you need with our Cloud VPS solutions.

check markCentOS, Debian, or Ubuntu check markNo cPanel Bloat check markSSH Key Management

Was this article helpful? Let us know!