Author: ymartel Date: 2012-05-24 17:26:37 +0200 (Thu, 24 May 2012) New Revision: 543 Url: http://nuiton.org/repositories/revision/sandbox/543 Log: add a generator for table example SQL instructions Added: scripts/dbTableGenerator.py Added: scripts/dbTableGenerator.py =================================================================== --- scripts/dbTableGenerator.py (rev 0) +++ scripts/dbTableGenerator.py 2012-05-24 15:26:37 UTC (rev 543) @@ -0,0 +1,103 @@ +# Generate a SQL file with 1.000.000 entries for a (not yet) customized table +# This program should ask user to have number of String/Test Column, Date Column +# or Numeric column +#!/usr/bin/python +# -*- coding: utf-8 -*- +from datetime import date, datetime +import random, string, sys, time + +#maybe manage String as a table of max lenght ? stringColumns = [50, 10, 23] ? +stringColumns = 13 +textColumns = 0 +#think about bigint ? +integerColumns = 5 +floatColumns = 1 +dateColumns = 2 +#have you seen blob ? + +#prepare the SQL file +filename = "perfotest1M.sql" +file = open(filename, "w") + +#Create the table +database_name = "PerfoTest" +createStatement = "create table " + database_name + " (\n" +createStatement += "\tid\t\tvarchar(255)" +create_index = "\nCREATE INDEX id_idx ON " + database_name + " (id);" + +for n in range (stringColumns) : + column_name = "stringcol" + str(n + 1) + createStatement += ",\n\t" + column_name + "\t\tvarchar(255)" + create_index += "\nCREATE INDEX " + column_name + "_idx ON " + database_name \ + + " (" + column_name + ");" + +for n in range (textColumns) : + column_name = "textcol" + str(n + 1) + createStatement += ",\n\t" + column_name + "\t\ttext" + create_index += "\nCREATE INDEX " + column_name + "_idx ON " + database_name \ + + " (" + column_name + ");" + +for n in range (integerColumns) : + column_name = "integercol" + str(n + 1) + createStatement += ",\n\t" + column_name + "\t\tinteger" + create_index += "\nCREATE INDEX " + column_name + "_idx ON " + database_name \ + + " (" + column_name + ");" + +for n in range (floatColumns) : + column_name = "numericcol" + str(n + 1) + createStatement += ",\n\t" + column_name + "\t\tnumeric" + create_index += "\nCREATE INDEX " + column_name + "_idx ON " + database_name \ + + " (" + column_name + ");" + +for n in range (dateColumns) : + column_name = "datecol" + str(n + 1) + createStatement += ",\n\t" + column_name + "\t\ttimestamp" + create_index += "\nCREATE INDEX " + column_name + "_idx ON " + database_name \ + + " (" + column_name + ");" + +createStatement += "\n);" + +#print createStatement +file.writelines("START TRANSACTION;\n") +file.writelines(createStatement) +file.writelines(create_index) + +#Add entries now! Groovy baby +print "c'est parti mon kiki! " + str(time.strftime("%H:%M:%S")) +query = "\n\n" +for row in range(1000000): + #ready ? + query_row = "INSERT INTO PerfoTest VALUES (" + "'monsuperIdOfTheDead" + str(row) +"'" + + for n in range (stringColumns) : + query_row += ", '" + ''.join(random.choice(string.digits + \ + string.letters) for i in xrange(random.randint(1, 255))) + "'" + + for n in range (textColumns) : + query_row += ", '" + ''.join(random.choice(string.letters) for i in \ + xrange(random.randint(1, 700))) + "'" + + for n in range (integerColumns) : + query_row += ", " + str(random.randint(1, 2147483647)) + + for n in range (floatColumns) : + query_row += ", " + str(random.uniform(1, 10) * random.randint(1, 10000)) + + for n in range (dateColumns) : + start_date = date.today().replace(day=1, month=1).toordinal() + end_date = date.today().toordinal() + random_day = date.fromordinal(random.randint(start_date, end_date)) + query_row += ", '" + str(datetime(*(random_day.timetuple())[:6])) + "'" + + query += query_row + ");\n" + + #A little commit! + if (row % 100000 == 0) : + query += "COMMIT;\nSTART TRANSACTION;\n" + +#print query +file.writelines(query) +file.writelines("COMMIT;\n") + +file.close() +print "Generation effectuee, finie a " + str(time.strftime("%H:%M:%S"))