6
\$\begingroup\$

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)
\$\endgroup\$
  • 2
    \$\begingroup\$ What are name ssn address dob phone and where are they defined? \$\endgroup\$ – Linny Aug 30 '19 at 0:25
  • 8
    \$\begingroup\$ What's the purpose of this exercise? Is it homework? Surely creating 10,000 SQL tables isn't something we'd want to do in real life. What are your concerns - speed? Python style? SQL style? \$\endgroup\$ – Reinderien Aug 30 '19 at 0:47
  • 2
    \$\begingroup\$ Please define "extremely slow", since that is subjective. It's probably very fast compared to some measures. A rough estimate of the system used and the current execution time would be good. \$\endgroup\$ – 0 0 Aug 30 '19 at 15:08
  • 1
    \$\begingroup\$ It might be faster to create a list, NumPy array or Pandas DataFrame with all the random entries, and only then loop over the contents, so you don't have to call the random functions inside each loop (it'll depend a bit on the memory available). \$\endgroup\$ – 0 0 Aug 30 '19 at 15:13
  • 2
    \$\begingroup\$ Just to add to @Taemyr's comment: the very fastest SSDs today have write speeds of ~3.4GB/s. You would need a parallel RAID-0 of 12 of the fastest SSDs of the planet, just bring the pure time for even writing the output file to 30 minutes. This is assuming that no other I/O takes place during that time, that the 75 TB file largely fits in cache and that the writes are fully sequential and evenly distributed across the drives. \$\endgroup\$ – Jörg W Mittag Aug 30 '19 at 16:38
11
\$\begingroup\$

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
| improve this answer | |
\$\endgroup\$
  • \$\begingroup\$ Thanks for the advice! I am trying to generate create and insert into sql statements (for mysql db) through python. It seems Python does not support prepared statements. Any library suggestions for this? \$\endgroup\$ – Subhadeep Majumder Aug 30 '19 at 7:12
  • 3
    \$\begingroup\$ Python definitely does support prepared statements, but you have to use a library - maybe dev.mysql.com/doc/connector-python/en \$\endgroup\$ – Reinderien Aug 30 '19 at 13:53
6
\$\begingroup\$

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)
| improve this answer | |
\$\endgroup\$
  • \$\begingroup\$ Thanks for the suggestions. I have attached the full code in case you wanted to take a look into it. \$\endgroup\$ – Subhadeep Majumder Aug 30 '19 at 7:22
  • \$\begingroup\$ you can split the string along mutliple lines (string literal concatenation) to reduce the line length and make the long strings better readable \$\endgroup\$ – Maarten Fabré Aug 30 '19 at 7:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.