Python Data Access Fundamentals - Article on SQLNetHub

Python Data Access Fundamentals

by

Hi friends, in an older article, we have talked about how you can connect to SQL Server databases from a Python program. In this article, we will be discussing about the Python Data Access fundamentals, that is how you can connect to different databases from within your Python code.

In general, Python provides the capability to connecting to multiple database types. Therefore, via your Python code, you are able to connect to both SQL and No-SQL databases.

Great! Let’s discuss about some of the major databases you can connect to via a Python program.

 

High-Level Process for Connecting to Databases Using Python Libraries

The high-level process for connecting to SQL Server from Python using the different libraries (i.e. pyodbc, cx_Oracle, psycopg2) is the following:

  1. Download and install the library for your platform
  2. Import the library in your code using the “import” command
  3. Open a connection to the database
  4. Perform the SQL query using a cursor
  5. Get the results
  6. Close the cursor and connection

As a best practice, you should always wrap your data access code, within try…except…finally code blocks, in order to apply exception handling.

 

Connecting to SQL Server Databases from Python

As we have already discussed in an older article, you can connect to SQL Server using the pyodbc module. The pyodbc module, is an open-source Python module that provides access to ODBC databases. That means that along with pyodbc, you will also need to use the respective SQL Server odbc driver. Using pyodbc, you can use it to connect to SQL Server, execute SQL queries, and retrieve the results.

Here’s a basic code example on how you can connect to SQL Server via Python:

# Import the pyodbc library
import pyodbc

try:

   # Connect to the database - Trusted connection to Named Instance
   conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=Instance_Name_Goes_Here;DATABASE=Database_Name_Goes_Here;Trusted_Connection=yes;')

   # Create cursor
   cursor=conn.cursor()

   # Execute SQL query
   cursor.execute("SELECT id, code, descr FROM table_name_goes_here")
  
   # Iterate over the retrieved record set and print each row 
   while 1:
       row = cursor.fetchone()
       if not row:
           break
       print(row.version)

# Print exception error (if applicable)
except (Exception, pyodbc.Error) as error:
   print("Error occurred: ", error)

finally:
   if conn:
      # Close cursor
      cursor.close()

      # Close connection
      conn.close()

 

Connecting to Oracle Databases from Python

If you want to connect to Oracle databases and run queries via your Python program, you can use the cx_Oracle library. This data access library, provides a Python interface to the Oracle Call Interface (OCI), and allows you among other, to execute SQL statements and retrieve data from Oracle databases.

After downloading and installing the library for your platform/environment, see below a basic code example on how you can connect to Oracle via Python:

# Import the cx_Oracle library
import cx_Oracle

try:

   # Connect to the database
   conn = cx_Oracle.connect("user", "password", "host:port/service_name")

   # Create cursor
   cursor = conn.cursor()

   # Execute SQL query
   cursor.execute("SELECT id, code, descr FROM table_name_goes_here")

   # Retrieve the results
   results = cursor.fetchall()

   # Iterate over the retrieved record set and print each row  
   for row in results:
       print("id: ", row[0]) 
       print("code: ", row[1]) 
       print("descr: ", row[2])

# Print exception error (if applicable)
except (Exception, cx_Oracle.Error) as error: 
   print("Error occurred: ", error)

finally: 
   if conn:
      # Close cursor
      cursor.close()

      # Close connection
      conn.close()

 

Connecting to PostgreSQL Databases from Python

In order to connect to PostgreSQL databases from Python, you can use the psycopg2 library.

After downloading and installing the library for your platform/environment, see below a basic code example on how you can connect to PostgreSQL:

# Import the psycopg2 library
import psycopg2

try:

   # Connect to the database
   conn = psycopg2.connect(
       host="hostname",
       database="database",
       user="user",
       password="password"
   )

   # Create cursor
   cursor = conn.cursor()

   # Execute SQL query
   cursor.execute("SELECT id, code, descr FROM table_name_goes_here")

   # Retrieve the results
   allRecords = cursor.fetchall()

   # Iterate over the retrieved record set and print each row  
   for row in allRecords:
       print("id: ", row[0], )
       print("code: ", row[1])
       print("descr: ", row[2])

# Print exception error (if applicable)
except (Exception, psycopg2.Error) as error:
   print("Error occurred: ", error)

finally:
   if conn:
       # Close cursor     
       cursor.close()

       # Close connection
       conn.close()

 

Connecting to MySQL Databases from Python

In order to connect to MySQL databases from Python, you can use the PyMySQL library.

After downloading and installing the library for your platform/environment, see below a basic code example on how you can use it to connect to MySQL:

# Import the pymysql library
import pymysql

try:

   # Connect to the database
   conn = pymysql.connect(
    host='hostname',
    user='username',
    password='password',
    db='database_name',
    charset='utf8'
   )

   # Create cursor
   cursor = conn.cursor()

   # Execute SQL query
   cursor.execute("SELECT id, code, descr FROM table_name_goes_here")

   # Retrieve the results
   allRecords = cursor.fetchall()

   # Iterate over the retrieved record set and print each row  
   for row in allRecords:
       print("id: ", row[0], )
       print("code: ", row[1])
       print("descr: ", row[2])

# Print exception error (if applicable)
except (Exception, pymysql.Error) as error:
   print("Error occurred: ", error)

finally:
   if conn:
       # Close cursor     
       cursor.close()

       # Close connection
       conn.close()

 

Connecting to SQLite Databases from Python

In the above examples, we have seen how we can connect to different relational database management systems (RDBMs).

In this section of the article, we’ll see how we can connect to SQLite from Python.

SQLite is a lightweight file-based database engine, that is commonly used in small and medium-scale applications.

In order to connect to SQLite databases from Python, you can use the “sqlite3” module which is built-in to Python. Therefore, no need to download anything.

Below a basic code example on how you can connect to an SQLite database:

# Import the sqlite3 library
import sqlite3


try:

   # Connect to the database file
   conn = sqlite3.connect('database_file.db')

   # Create cursor
   cursor = conn.cursor()

   # Execute SQL query
   cursor.execute("SELECT id, code, descr FROM table_name_goes_here")

   # Retrieve the results
   allRecords = cursor.fetchall()

   # Iterate over the retrieved record set and print each row  
   for row in allRecords:
       print("id: ", row[0], )
       print("code: ", row[1])
       print("descr: ", row[2])

# Print exception error (if applicable)
except:
   print("An error occurred.")

finally:
   if conn:
       # Close cursor     
       cursor.close()

       # Close connection
       conn.close()

 

Connecting to NoSQL Databases from Python

In addition to all the above database types, from within Python, you can also connect to different NoSQL databases.

We have discussed about NoSQL databases in an older article. As mentioned in that article, Non-Relational Databases which are also known as “NoSQL databases”, are database systems that do not use the tabular schema of rows and columns which is usually used in Relational Database Management Systems (RDBMSs).

Below, you can find some examples of NoSQL databases and information about the libraries that can be used for connecting to these databases:

 

 

Learn more about SQL Server Data Access from Python – Enroll to our Course!

Enroll to our online course  “Working with Python on Windows and SQL Server Databases” with an exclusive discount, and get started with Python data access programming for SQL Server databases, fast and easy!

Working with Python on Windows and SQL Server Databases - Online Course
(Lifetime Access, Q&A, Certificate of Completion, downloadable resources and more!)

Enroll from $12.99

 

 

Featured Online Courses:

 

Read Also:

 

Check our online courses!

Check our eBooks!

Subscribe to our YouTube channel!

Subscribe to our newsletter and stay up to date!

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)

Loading...

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub

Views: 570