With the help of the IronPython generator in ApexSQL Generate, we can overcome pretty
complicated SQL data generation works.

SQL test data generation tasks can sometimes be more complex and difficult than we might expect. In some cases, we might require different test data patterns whereas predefined and conventional ones may not meet our needs. Under these circumstances, ApexSQL Generate offers various data generator solutions but in this article, we will learn the usage of the IronPython generator and reinforce it with examples.

Description

IronPython is an open-source implementation of Python for the .NET CLR and Mono hence it can solve various issues in many areas. IronPython generator allows us to execute the custom Python codes so that we can gain advanced SQL Server test data customization ability. We can use the resultset of these Python codes as test data in ApexSQL Generate. At the same time, we can combine fantastic features of the ApexSQL Generate (Loop, Shuffle, etc.) with Python resultsets during the SQL test data generation proceedings. So, we obtain a powerful SQL test data generation capabilities. At first, we will take a glance at the properties of the IronPython generator.

Custom Python code box– Allows editing the custom Python codes

Execute – Allows running the custom Python codes

Load – Imports the py files which are written in the Python

Modules – The Python modules can be described as code libraries which allow using particular classes and functions repeatedly. Modules option helps to indicate external modules path which is used in the custom Python code

In the following sections, we will complete two examples;

• Generating custom SQL test data from a txt file
• Generating custom SQL test data from a JSON file

Connecting to SQL Server with ApexSQL Generator

In the following examples, we use a test table, named customers. We can create this table through the following query:

IF EXISTS(SELECT *
         FROM sys.objects
         WHERE name = 'customers')
    DROP TABLE [dbo].[customers];
CREATE TABLE [customers]
([customer_id] [INT] IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED , 
 [first_name]  [VARCHAR](255) NOT NULL, 
 [last_name]   [VARCHAR](255) NOT NULL, 
 [phone]       [VARCHAR](25) NULL, 
 [email]       [VARCHAR](255) NOT NULL, 
 [street]      [VARCHAR](255) NULL, 
 [city]        [VARCHAR](50) NULL, 
 [state]       [VARCHAR](25) NULL, 
 [zip_code]    [VARCHAR](5) NULL
);
GO

We will launch the ApexSQL Generate and select the Live database in the Data Source tab. Fill the SQL Server connection credentials and select the related database which involves the customer’s table. Click the Load button.

The project window will appear and we will proceed to the next step.

Generating Custom SQL Test Data from Txt file with IronPython Generator

In this example, we will import test data from a custom txt file which is prepared by us to ApexSQL Generate. At first, we will prepare a custom txt file and add the random email addresses line by line. Save the txt file to any location.

We will click the email line in the main grid and change the Type property to IronPython in the Generator panel.

We will change the default Python code with the following and then click the Execute so that we can run the Python code.

def main():
    email_array = []
    with open('C:/samplemails.txt') as emaillist:
        for line in emaillist:
            email_array.append(line)
        return email_array

main()

As you can see in the above image, we have changed the predefined email addresses with the custom email addresses. IronPython generator provides the flexibility to use custom text files.

Generating Custom SQL Test Data from a JSON file with IronPython Generator

Now, we will go ahead in an advanced usage example of the IronPython generator. In the following, we will perform to get custom data from the JSON file. As we mentioned in the entrance, the Python programming language provides us to use different modules. In our example, we will use the JSON module of Python. At first, we will download and install the required modules from the IronPython project web site. The format of the JSON file will look like the below image:

In this step, we will change the IronPython code.

import json

def main():
    email_array = []
    with open('C:/samplemails.json') as json_file:
        data = json.load(json_file)
    for p in data['emaillist']:
        email_array.append(p['email'])
    return email_array
    
main()

The Python code includes the JSON module and for this reason, we should set the reference path of the JSON module. The default installation path of the IronPython project installation is “C:\Program Files\IronPython 2.7\Lib”, so we will add this path to Modules setting of the IronPython Generator.

We will click Execute in order to run the Python code and in the Preview panel we can see fetched email addresses are used to fill the selected column.

Conclusions

In this article, we learned usage details of the IronPython Generator in the ApexSQL Generate. IronPython Generator provides a wide range of possibilities to generate SQL Server test data. Additionally, we can use Python modules quite easily in ApexSQL Generate.