Writing 1.5 million rows in a file 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)
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