SQL Database Connector

Python By Example

December 21, 2021 by cryan.com

It's really easy to query a database in Python. There's more than one way to do it, and there are plenty of third-party libraries that can offer better performance and cache capabilities.

Here's the basic to query a database:

hostname = 'localhost';
username = 'cryan';
password = 'Uniramous2';
database = 'webuser';
def doQuery( conn ) :
    cur = conn.cursor()
    content = ""
  
    cur.execute( "select date, title, comment from guestbook where topic = " + topic + "  order by title desc )
    records = cur.fetchall()
    for row in records:
        content = row[4]
    
    return content;    
myConnection = mysql.connector.connect( host=hostname, user=username, passwd=password, db=database )
content += doQuery( myConnection )
myConnection.close()
    

Technical Notes

You have to set the hostname, username, password, and database before calling the mysql.connector.connect function.

For security you should set up a READ-ONLY user for public database reads. This way if the code is ever displayed, someone can't just write a simple query to wipe out the database.

I use a Python function to handle the queries. Obviously, this isn't required, but it's a good habit to get into.

cur.execute handles all the database interactions.

Using cur.fetchall() returns an array of entries, you need to call the specific row entry, for example: row[3].