Connect to a Database Using a Python Script

Scenario

Dataphin SQL supports only specific database data source types. For a list of supported data sources, see Data sources supported by Dataphin, specifically the Offline development – Database SQL column. What can you do to interact with an unsupported database?

Solution and features

You can use Python database connector libraries—such as pymssql, pyodbc, or mysql-connector-python—to write a Python script that connects to and operates on those databases. The following example shows how to connect to a SQL Server database and create a table using pymssql.

1. Go to Management Hub > Third-party library management > Python packages and install the required Python packages either online or by uploading them.

2. Go to Development > Data Studio > Compute nodes and create a Python script to connect to the database and create a table. Use the following script.

   import pymssql
   
   # Connect to SQL Server
   connection = pymssql.connect(server='xxx', user='xxx', password=${dp_glb_sql_server_pwd}, database='xxx')
   
   cursor = connection.cursor()
   
   # Create a table
   cursor.execute('''
       CREATE TABLE customers (
       id INT PRIMARY KEY IDENTITY(1,1),  -- Customer ID, auto-incrementing primary key
       first_name NVARCHAR(50) NOT NULL,   -- First name, cannot be null
       last_name NVARCHAR(50) NOT NULL,    -- Last name, cannot be null
       email NVARCHAR(100) NOT NULL UNIQUE,-- Email, cannot be null and must be unique
       phone NVARCHAR(15),                  -- Phone number, can be null
       created_at DATETIME DEFAULT GETDATE() -- Registration timestamp, defaults to current time
   )
   ''')
   
   # Submit changes and close the connection
   connection.commit()
   cursor.close()
   connection.close()

3. Open the Properties panel for the Python node and select the required Python packages.