How to create sample data in SQL Server on Ubuntu using Python is shown in this blog.
Contents
▼1. Creating sample data using Python
In the past, how to create sample data using the insert query that is created by python code was provided below in this case, this insert query was executed using sqlcmd utility. but this blog all of steps is executed by python without sqlcmd.
Python – Creating sample data in SQL Server on Ubuntu No.62
▼2. Prerequisites
How to build an environment using Python is shown below.
Python – How to connect to SQL Server on Ubuntu No.92
▼3. Bulk create sample data in SQL Sever
3-1. Creating Python code
“sa” user is used to log in SQL Server. the password of “sa” user is set in “password” parameter. this code create and execute the insert query to generate sample data and then retrieve this data using select statement.
import pyodbc
import random
import time, datetime, string
server = 'localhost'
database = 'testdb'
username = 'sa'
password = 'password'
driver= '{ODBC Driver 17 for SQL Server}'
success = False
retryc = 0
max_retries = 3
kazu = 10
def randomword(length):
letters = string.ascii_lowercase
return ''.join(random.choice(letters) for i in range(length))
while not success and retryc < max_retries:
try:
cnxn = pyodbc.connect('DRIVER='+driver+';SERVER='+server+',1433;DATABASE='+database+';UID='+username+';PWD='+ password+';',autocommit=True,Timeout=1,Trusted_Connection='no',Encrypt='no')
cnxn.setencoding('utf-8')
cursor1 = cnxn.cursor()
cursor1.execute("IF DB_ID(N'sampledb1') is null create database sampledb1")
cursor2 = cnxn.cursor()
cursor2.execute("IF NOT EXISTS (select * from sampledb1.dbo.sysobjects where name like 'sampletb') create table sampledb1.dbo.sampletb (id int, num int, cdatetime datetime, note varchar(50))")
num = random.randint(100, 10000000)
dtnow = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
insertquery1 = "insert into sampledb1.dbo.sampletb (id, num,cdatetime,note) values ('"+ str(num) +"','"+ str(num) +"','"+str(dtnow)+"','int')"
for i in range(1,kazu):
insertquery1 = insertquery1 + ",(" + str(i*num) + "," + str(i) + ",'"+ str(dtnow) +"','" + str(randomword(10)) + "')"
print(insertquery1)
cursor3 = cnxn.cursor()
cursor3.execute(insertquery1)
cursor4 = cnxn.cursor()
cursor4.execute("SELECT * FROM sampledb1.dbo.sampletb")
row = cursor4.fetchone()
print("==========================")
print("Results of a select query")
print("==========================")
while row:
print(str(row[0]) + " " + str(row[1]) + " " + str(row[2]) + " " + str(row[3]))
row = cursor4.fetchone()
success = True
except pyodbc.Error as ex:
retryc += 1
print("Current Timestamps:" + str(datetime.datetime.now()) + "retry count: " + str(retryc))
print("==========================")
print("Error: " + str(ex))
print("==========================")
time.sleep(3)
finally:
cursor1.close()
cursor2.close()
cursor3.close()
cursor4.close()
cnxn.close()3-2. Results of this python code
insert into sampledb1.dbo.sampletb (id, num,cdatetime,note) values ('3366109','3366109','2022-12-09 13:59:12','int'),(3366109,1,'2022-12-09 13:59:12','mcranentiv'),(6732218,2,'2022-12-09 13:59:12','orlsojlvuu'),(10098327,3,'2022-12-09 13:59:12','uaclnlkehv'),(13464436,4,'2022-12-09 13:59:12','xngcehsmtc'),(16830545,5,'2022-12-09 13:59:12','llvzgooqaa'),(20196654,6,'2022-12-09 13:59:12','atmordnpfz'),(23562763,7,'2022-12-09 13:59:12','qpzlferdra'),(26928872,8,'2022-12-09 13:59:12','lnbjlmclxx'),(30294981,9,'2022-12-09 13:59:12','xfspqvetbf')
==========================
Results of a select query
==========================
3366109 3366109 2022-12-09 13:59:12 int
3366109 1 2022-12-09 13:59:12 mcranentiv
6732218 2 2022-12-09 13:59:12 orlsojlvuu
10098327 3 2022-12-09 13:59:12 uaclnlkehv
13464436 4 2022-12-09 13:59:12 xngcehsmtc
16830545 5 2022-12-09 13:59:12 llvzgooqaa
20196654 6 2022-12-09 13:59:12 atmordnpfz
23562763 7 2022-12-09 13:59:12 qpzlferdra
26928872 8 2022-12-09 13:59:12 lnbjlmclxx
30294981 9 2022-12-09 13:59:12 xfspqvetbf