如何在Sqlite中向列中插入1000个随机int值行？

Question

我是python3的新手。我的作业是创建一个包含10个表的Sqlite数据库，每个表包含50列，每列包含1000行，数据是使用Python随机生成的。我差不多做完了

我的代码：

import sqlite3
conn = sqlite3.connect('testmydb.db')
cur = conn.cursor()


for table_number in range(1,11):
    cur.execute('''CREATE TABLE table''' + str(table_number) + '''(id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT)''')

    listOfColumns = ("column0",)
    for column_number in range(1,49):
        newColumn = ("column" + str(column_number),)
        listOfColumns = listOfColumns + newColumn

    for column_number in listOfColumns:
        cur.execute('''ALTER TABLE table''' + str(table_number) + ''' ADD COLUMN %s TEXT''' % column_number)

conn.commit()    
cur.close()
conn.close()

现在我想在1列中插入1000行，但是当我想创建更多的for循环时，我感到困惑。有人能推荐我吗

Answer 1

以下内容应满足您的要求（参见备注）

import sqlite3
import random
conn = sqlite3.connect('testmydb.db')
cur = conn.cursor()


#Only need to do this once
listOfColumns = ("column0",)
bindMarkers = ",?" #ADDED to allow values to be bound will be ?,?,?,?, ........ 49 ?
for column_number in range(1, 49):
    newColumn = ("column" + str(column_number),)
    listOfColumns = listOfColumns + newColumn
    bindMarkers = bindMarkers + ",?"

for table_number in range(1,11):
    cur.execute("DROP TABLE IF EXISTS table" + str(table_number)) #make it rerunnable
    cur.execute('''CREATE TABLE IF NOT EXISTS table''' + str(table_number) + '''(id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT)''')

    for column_number in listOfColumns:
        cur.execute('''ALTER TABLE table''' + str(table_number) + ''' ADD COLUMN %s TEXT''' % column_number)

    # The INSERT statement note null means that the id will be automatically generated
    insertsql = "INSERT INTO table" + str(table_number) + " VALUES(null" + bindMarkers + ")"
    #print the INSERT SQL (just the once)
    if table_number == 1:
        print(insertsql)

    for row_number in range(1,1001):
        # Generate a list of 49 random values
        listOfRandomValues =[random.randint(1, 999999999999) for i in range(49)]
        cur.execute(insertsql,listOfRandomValues) # insert the row

    # extract the first 5 rows an print each row
    cursor = cur.execute("SELECT * FROM table" + str(table_number) + " LIMIT 5")
    result = "row in table table" +  str(table_number) + " Data is "
    for row in cursor:
        print(row)

conn.commit()
cur.close()
conn.close()

这将产生如下输出（第一行是第一条INSERT语句/SQL）：-

INSERT INTO table1 VALUES(null,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)



(1, '208968800970', '673486951978', '416011320117', '257739455602', '161014001387', '66915092142', '192394825558', '894946418178', '147479449787', '429768915009', '343072031065', '312483697033', '38240897968', '179592184222', '517690986147', '401721693004', '760956848808', '787028914225', '658523299261', '923606731801', '740090529164', '169600507787', '441903806645', '82302358448', '250921627878', '542116452618', '998918595471', '775548995005', '733089506549', '957054106540', '449321507524', '798501631292', '409382414444', '945602662286', '706232454927', '930118739979', '691405693853', '201175361297', '513975533346', '16690109599', '592944414377', '948709328664', '490207084748', '406188522423', '799744354342', '474761616653', '314527920015', '94102072722', '912028741567')
(2, '172875509043', '126844020427', '423436418690', '973535472434', '171412421537', '693479106176', '909004577995', '920911700813', '605955273811', '325652512054', '94057263900', '45907520985', '64928934172', '301130729226', '103229253943', '114469347031', '551553752113', '626314462779', '22617947251', '997836163264', '585793592332', '620096766798', '565760327235', '348031514661', '871589505728', '58320228377', '179652288652', '977988196994', '742110712624', '201181530463', '816248034687', '22951611374', '723154858722', '289036915539', '997272483698', '61348539011', '977373908399', '668284539899', '55348735729', '263726052214', '662603583920', '790720286573', '487793507420', '883073500835', '519633722649', '383008255347', '30563959610', '617324332661', '89956476106')
(3, '253567041183', '70027774987', '535230659770', '191267720449', '791090949115', '399626615217', '649492276413', '594283985270', '983353743022', '713002984294', '982490173135', '109850128623', '571489216078', '900560015434', '729185220526', '362712800267', '619582132251', '990925729743', '144006421433', '790742578660', '64886161120', '266462916556', '89211644675', '941650491818', '878437527129', '827767387129', '899754797443', '280555144440', '623469334050', '882001652568', '395198811620', '393149546360', '509545198950', '534252806675', '582802496697', '674715538387', '748829323303', '296068248515', '573789396002', '84015250035', '963083904856', '677426863455', '173505995385', '569976297792', '643158854425', '273191627696', '676364784545', '536715691007', '678846958313')
(4, '575055534615', '179094408882', '418242646417', '258767847915', '533305509121', '800410396430', '416643709991', '453093467839', '352906227023', '711478657972', '542560050616', '477511637703', '464619274323', '438591712313', '293891594997', '638717557413', '796607432824', '845617819673', '682479247215', '687662681530', '682910774205', '547150987433', '645097550529', '781225444825', '498491871793', '280308928866', '386747319120', '175187502068', '554032903538', '906897892968', '847200546291', '724824936579', '257524554306', '341479642174', '628478037881', '41911000836', '487139622046', '698641404274', '300203051807', '321147725978', '201308004931', '324554566932', '54668008952', '799888599714', '544776279131', '851164639529', '1118079080', '993554994315', '97774308420')
(5, '263377483252', '535276579958', '434436394255', '235123585872', '886866465625', '83437890933', '546739192349', '832929945092', '889303183895', '517501283515', '386452334064', '437005515113', '567305852696', '254940127493', '158473804439', '714105412308', '887616841407', '873758857265', '59024734698', '495085412255', '757296111012', '438130715784', '661863799528', '370244296694', '559859930401', '409259131854', '72716791778', '900054227569', '897455645761', '254989679831', '46456169823', '597888422562', '581408791663', '191438417130', '468539979785', '998729241595', '596707251066', '731997835957', '432001941801', '351970232680', '602771773558', '793033654396', '205236245465', '547142878108', '973842386021', '742055066627', '455501634405', '130419180039', '870186517783')
(1, '472841964440', '177094420514', '859773622393', '943573354468', '909606787130', '278659426379', '129796913302', '67857238168', '104155180296', '581639712382', '451184580063', '917433785632', '226959780068', '190462507493', '256274613979', '919674630928', '976702823134', '121337013780', '254022515917', '293782992065', '903483153770', '147697931939', '279062893088', '553519369139', '962433270653', '640822114280', '816716757345', '999707836592', '697963179054', '104305203866', '735705858863', '617083342099', '262076004375', '797912340506', '205887749382', '576489282235', '705096989440', '670969562520', '649164826831', '311493582872', '760367591190', '749686855909', '819181100789', '466265188300', '304292298579', '420782152623', '854335337149', '916391611738', '964274785687')
(2, '621325506597', '776006955683', '137683264810', '351906945610', '682429690372', '965366508605', '666337420753', '453325880143', '70778770818', '103682937480', '868216544504', '229703959756', '41004116292', '507097353534', '871910281669', '251530835311', '836500603189', '601460038094', '897559700303', '681312522817', '161143454247', '553960203443', '777460295192', '458302954528', '977754347041', '892360041754', '681995024692', '248485864749', '348381577064', '450879805019', '650777503736', '353872867221', '97506344721', '747237255889', '455629065944', '861413783175', '214743871915', '77511793017', '621196858622', '825422146350', '489409477723', '908004452720', '238639741015', '426722798842', '980323652543', '561628376666', '838205614824', '784039262073', '949055065484')
(3, '736008123891', '923934389646', '546159245294', '429258073881', '583372466354', '50804206500', '273716995212', '733988654121', '788160350686', '749598895287', '551751993459', '916986772574', '622366294456', '687624270621', '185660393899', '329963428664', '928661078668', '875765821125', '754653923243', '151547845857', '248763933358', '636547599095', '87140063802', '267688269107', '224477253917', '641792646340', '59046381016', '103443043545', '485267444040', '387215340714', '268223896307', '480068950182', '225811319773', '492031230630', '502916805016', '514567127425', '178032451267', '750288734257', '825600642728', '641081438590', '207022050440', '902457228778', '115373751089', '348372424350', '768147081429', '715162751738', '210598155420', '196905259558', '873091126544')
(4, '560125266801', '378302831641', '471084702841', '679900688640', '201624340251', '909766550240', '687623074376', '116508086811', '217573740193', '378086229046', '466649195230', '932285473013', '648745964471', '968517127245', '748917121449', '224930472692', '698734544540', '793428186573', '153336974374', '24843476682', '42926459163', '503345524005', '116363947828', '524399560588', '238188045685', '3353134402', '97245283198', '780904780984', '768226492682', '337351478339', '761762114083', '4108216481', '715457129140', '718946387960', '808632491477', '283509135313', '750631442686', '302040053814', '354520401885', '30869550070', '831081853310', '317334330124', '175699898404', '316762996417', '144843539429', '647890863625', '500905345131', '686585819856', '439083530058')
(5, '786320993918', '418227705376', '222672045565', '50994821164', '445050766070', '655740733971', '144925180595', '178456995314', '968483620704', '217344736719', '659133382247', '699130444999', '645737723689', '211418136852', '977174813693', '404005933734', '416012774264', '498694089898', '286235598876', '105048705716', '745323502156', '22320974963', '287621972357', '484051431377', '677832782489', '175141638805', '652237666867', '633826915005', '826792363302', '181964153730', '549735148579', '820006084751', '622355043852', '615716362152', '337022948655', '280970738440', '264064973515', '550249406679', '912858473551', '542805313957', '43397863679', '257720759974', '189160263335', '265086252271', '692156831796', '860245023055', '769544988002', '856033591981', '865669688852')
(1, '29773154022', '105812125224', '923886735040', '494040618517', '406872772654', '964605045362', '483548207268', '222657267987', '728533595865', '427758006630', '250839721516', '246117222632', '625392752778', '372756660516', '276521371279', '677307428516', '434498176501', '757867858941', '568841625163', '315224423736', '939706907834', '567757610656', '977473375050', '476473505693', '921117900131', '344700573908', '350627473109', '569315794206', '780528101292', '957322180230', '952406583209', '435610932961', '463449885730', '174468401098', '916963726643', '193968348451', '297427605119', '481930164885', '685603984144', '543719297225', '612929787721', '475021539217', '176642603133', '74400339089', '95276914071', '808000358479', '79312180687', '502877681225', '659274942719')
..........

i still don't get what is variable bindMarkers and if table_number == 1: print(insertsql)

第一行第二行if table_number == 1: print(insertsql)

正在打印INSERT语句以显示其外观。它只是包含在其中，没有必要。但是，知道它是什么样子来解释这个问题是有用的？占位符和绑定值

所以INSERT语句是沿着

INSERT INTO tablex VALUES(null,?,? ....... (49 ?'s)

• 表x，其中x代表1-10

注释中的第一个null允许SQLite为id列生成唯一值

每个人？是占位符，将被绑定值替换。这种技术可以防止SQL注入

bindMarkers只是一个由1生成的字符串？因此它是一个49英寸的字符串（比键入VALUES(null,?,?,?,?,? .....)更容易，而且如果列号要更改，也更灵活/适应性更强）

您可以看到listOfRandomValues =[random.randint(1, 999999999999) for i in range(49)]行创建了一个包含49个随机值的列表，每个随机值将用于替换单个值？（第一个值替换第一个？，第二个值替换第二个？，依此类推）

这被认为是比按照

INSERT INTO tablex VALUES(null,'208968800970', '673486951978', '416011320117', '257739455602', '161014001387', '66915092142', '192394825558', '894946418178', '147479449787', '429768915009', '343072031065', '312483697033', '38240897968', '179592184222', '517690986147', '401721693004', '760956848808', '787028914225', '658523299261', '923606731801', '740090529164', '169600507787', '441903806645', '82302358448', '250921627878', '542116452618', '998918595471', '775548995005', '733089506549', '957054106540', '449321507524', '798501631292', '409382414444', '945602662286', '706232454927', '930118739979', '691405693853', '201175361297', '513975533346', '16690109599', '592944414377', '948709328664', '490207084748', '406188522423', '799744354342', '474761616653', '314527920015', '94102072722', '912028741567')

语句本身较短（即1？而不是12位数字），因此不太可能引起限制问题