Writing 1.5 million rows in a file

Question

I am generating SQL statements (of MySQL format) and writing into a file to create 10,000 tables. Each table contains 5 columns with 1.5 million rows of data in each column. I generated these data by using regex and faker module. So, I have a loop of 10,000 (one for each create table statement) and within this loop, I have another loop of 1.5 million (to insert rows of data into each table created).

The code is running properly but is extremely slow. Is there any way to make this run considerably faster?

import rstr
import random
from faker import Faker
import time

fake = Faker()

name = ['Name', 'Person_Name', 'Customer_Name', 'Employee_Name', 'Contact_Name', 'CustName', 'EmpName']
ssn = ['SSN', 'Social_Security_Number', 'National_Identification_Number', 'NID', 'US_SSN', 'Social_Number', 'Social_Security_No', 'Social_Security_Num', 'Customer_Social_Security Number', 'Employee_Social_Security_Number']
address = ['Address', 'Customer_Location_Details', 'Employee_Address_Details', 'Address_Det.', 'Add_Line_1', 'Add_Line_2', 'Address_Line_1', 'Address_Line_2']
dob = ['DOB', 'Date_Of_Birth', 'Birth_Date', 'V_VLD_BRTH_DT', 'DAY_OF_BRTH', 'DT_OF_BIRTH']
phone = ['Phone_Number', 'Contact_Number', 'Office_Phone', 'Residential_phone', 'Contact_Phone']

ssn1='(?!000|.+0{4})(?:\d{9})'
ssn2='(?!000|.+0{4})(\d{3}-\d{2}-\d{4})'
sList=[ssn1,ssn2]

db1='^(0[1-9]|[12][0-9]|3[01])[/](0[1-9]|1[012])[/](19)\d\d$'
db2='^(19)\d\d$[.](0[1-9]|1[012])[.](0[1-9]|[12][0-9]|3[01])'
db3='^(19)\d\d$[-](0[1-9]|1[012])[-](0[1-9]|[12][0-9]|3[01])'            
db4='^(0[1-9]|[12][0-9]|3[01])[-](0[1-9]|1[012])[-](19)\d\d$'
db5='^(0[1-9]|[12][0-9]|3[01])[.](0[1-9]|1[012])[.]\d\d$'
dbList=[db1,db2, db3, db4, db5]

p1='1[2-9][0-8][0-9][2-9][0-9]{2}[0-9]{4}'
p2='[2-9][0-8][0-9][2-9][0-9]{2}[0-9]{4}'
p3='001[2-9][0-8][0-9][2-9][0-9]{2}[0-9]{4}'
p4='[(][\d]{3}[)][ ]?[\d]{3}-[\d]{4}'
p5='\+1[2-9][0-8][0-9][2-9][0-9]{2}[0-9]{4}'
p6='(\d{3}\d{3}\d{4})'
p7='[2-9][0-8][0-9][-][2-9][0-9]{2}[-][0-9]{4}'
pList=[p1, p2, p3, p4, p5, p6, p7]

tables=10001
rows=1500001

with open("D:\Output.sql", "a") as text_file:
    count = 0

    for i in range(1,tables):
        a=[]
        b=[]
        j=str(i)
        x=f'Tab_{i}'

        #a.append(random.choice(name)+'_'+j)
        #a.append(random.choice(ssn)+'_'+j)
        #a.append(random.choice(address)+'_'+j)
        #a.append(random.choice(dob)+'_'+j)
        #a.append(random.choice(phone)+'_'+j)
        a.extend(f'{random.choice(n)}_{j}' for n in (name, ssn, address, dob, phone))

        text_file.write(f"create table Python_test.{x}({a[0]} VARCHAR(255),{a[1]} VARCHAR(255),{a[2]} VARCHAR(255),{a[3]} VARCHAR(255),{a[4]} VARCHAR(255));\n")

        start = time.time()
        for j in range(1,rows):
            text_file.write(f"insert into {x}({a[0]},{a[1]},{a[2]},{a[3]},{a[4]}) values ('{fake.name()}','{rstr.xeger(random.choice(sList))}','{fake.address()}','{rstr.xeger(random.choice(dbList))}','{rstr.xeger(random.choice(pList))}');\n")

        print(time.time() - start) 
        text_file.write('\n')
        count+=1
        print(count)

Answer 1

Use open in a with

...so that you can be guaranteed of file closure regardless of any potential exceptions.

Use f-strings

So that something like this:

x='Tab_'+str(i)

turns into

x = f'Tab_{i}'

Computers are good at repetition

a.append(random.choice(name)+'_'+str(i))
a.append(random.choice(ssn)+'_'+str(i))
a.append(random.choice(address)+'_'+str(i))
a.append(random.choice(dob)+'_'+str(i))
a.append(random.choice(phone)+'_'+str(i))

can be

a.extend(f'{random.choice(n)}_{i}' for n in (name, ssn, address, dob, phone))

Injection attacks

Unless you're really confident about the source, safety and validity of your data, constructing a string and sending it off as executable SQL is enemy number one of database security. This is what prepared statements are for. It's unclear what flavour of SQL you need, so it's unclear which library you'll need for this.

Combined assignment/increment

count=count+1

can be

count += 1

Answer 2

Docstrings

You should include a docstring at the beginning of every module, class, and function you write. This will allow documentation to identify what your code is supposed to do.

with open vs file = open

Currently, you open the file with text_file = open("D:\Output.sql", "a", encoding="utf-8"), and manually close it with text_file.close(). You can save yourself from having to do this with opening the file using with open(...) as ...:. This lets you work with the file, and once done and out of scope, can continue writing the program. "But wait. What about closing the file?". When you use with, once you are out of the scope of the with, it closes itself automatically.

Magic Numbers

for i in range(1,10001): ...
for j in range(1,1500001): ...

At first look, I had no idea what these numbers were supposed to represent. You should assign these numbers to variables, and use those variables instead. This makes the code cleaner and easier to maintain. What if you wanted to change the number of tables or number of people? You'd need to find every instance of that magic number (should you use it more than once) and change it. Having it assigned to a variables fixes this problem, as you can just change the value of the variable.

String Formatting f""

text_file.write('create table Python_test.'+x+'('+a[0]+' VARCHAR(255),'+a[1]+' VARCHAR(255),'+a[2]+' VARCHAR(255),'+a[3]+' VARCHAR(255),'+a[4]+' VARCHAR(255));\n')

This hurts to read. All the + split apart the string and makes it really hard to know what the whole string is. My solution is to use f"...". This allows you to directly incorporate variables into your strings, without having to +...+, and also avoiding (if present) +str(...)+ which is very ugly to me.

Objects

Having a redefined list with fixed positions of properties of people is messy. Instead, you can generate a list with instances of a Person, and you can access their properties when incorporating it in your SQL string.

_ for unused loop variables

When you don't use a variable in a loop, like:

for i in range(5):
    print("Hi!")

You should use a _. This makes it clear that the variable used for the loop is not needed, and should be ignored.

Helper Functions

I wrote a few helper functions for generating some random values in your code. Using helper functions in your code can really help you, as you don't have to cram everything into one function.

Exclaimer: I had no idea what you were trying to do when inserting data into your tables, so I left that blank. It also didn't help that fake and rstr weren't defined / weren't shown in your program.

Updated Code

"""
Module Docstring
A description of your program goes here
"""

import random

class Person():
    """
    Class to store properties of Person
    """

    def __init__(self, name, ssn, address, dob, phone):
        self.name = name
        self.ssn = ssn
        self.address = address
        self.dob = dob
        self.phone = phone

def generate_ssn():
    """
    Returns a randomly generated SSN
    """
    return f"{random.randint(100, 999)}-{random.randint(10, 99)}-{random.randint(1000, 9999)}"

def generate_dob():
    """
    Returns a randomly generated address in format MM/DD/YYYY
    """
    return f"{random.randint(1, 12)}/{random.randint(1, 31)}/{random.randint(1919, 2019)}"

def generate_phone():
    """
    Returns a randomly generated phone number
    """
    return f"{random.randint(100, 999)}-{random.randint(100, 999)}-{random.randint(1000, 9999)}"

def generate_sql_script(num_people, num_tables, output_file):
    """
    Generates an SQL script for inserting people into tables
    """
    with open(output_file, "a") as out_file:
        people = []
        count = 0
        for i in range(1, num_people):
            people.append(Person(f"John_{i}",
                                 generate_ssn(),
                                 f"123 Main St_{i}",
                                 generate_dob(),
                                 generate_phone())
                         )
            random_person = random.choice(people)
            table = f"Tab_{i}"
            out_file.write(f'CREATE TABLE Python_test.{table}({random_person.name} VARCHAR(255), {random_person.ssn} VARCHAR(255), {random_person.address} VARCHAR(255), {random_person.dob} VARCHAR(255), {random_person.phone} VARCHAR(255)')

            for _ in range(1, num_tables):
                #Couldn't understand what this is doing with `fake` and `rstr`
                pass

            out_file.write('\n')
            count += 1
            print(count)