python写sql语句_python脚本随笔：实现生成插入表的sql语句

最近在做课程设计项目的时候需要用到分页，所以就想学习一下真分页。所以需要在数据库中要有多条记录，因此就顺手写下了个python脚本来生成多条insert语句。代码如下：

#!/usr/bin/env python3

import os,os.path

import string,base64

def main():

f=open("inserData.sql",'w')

i=1

while i<10:

strI=str(i)

sql="insert into t_admin values('"+strI+"',"+"'username"+strI+"',"+"'password"+strI+"',"+"'name"+strI+"','"+"1',"+"'introduce"+strI+"');"

f.write(sql +"\n")

print(sql)

i=i+1

f.close()

if __name__=='__main__':

main()

生成代码如下：

insert into t_admin values('1','username1','password1','name1','1','introduce1');

insert into t_admin values('2','username2','password2','name2','1','introduce2');

insert into t_admin values('3','username3','password3','name3','1','introduce3');

insert into t_admin values('4','username4','password4','name4','1','introduce4');

insert into t_admin values('5','username5','password5','name5','1','introduce5');

insert into t_admin values('6','username6','password6','name6','1','introduce6');

insert into t_admin values('7','username7','password7','name7','1','introduce7');

insert into t_admin values('8','username8','password8','name8','1','introduce8');

insert into t_admin values('9','username9','password9','name9','1','introduce9');

在这个脚本之上，本来希望做个能够动态生成sql代码的脚本，脚本代码如下：

#!/usr/bin/env python3

import os,os.path

import string,base64

import string

def main():

table=input("请输入数据表名:")

li=[]

li=check(li)

f=open("inserData.sql",'w')

i=1

num=input("将要生成的sql语句多少条？:")

sqlfirst=createsqlFirst(table,li)

print(">>>>>"+sqlfirst)

numStr=str(num)

while i<=int(num):

sql=createsql(sqlfirst,li,str(i))

i=i+1

f.write(sql+";"+"\n")

print(sql)

#sql="insert into values "+table+"("+li[0]+"','"

#print(sql)

f.close()

def inputColumn():

column=input("请输入列名:")

return column

def check(li):

flag=input("是否还有列要输入，是则输入'y'，没有则输入'n':")

if flag=="y":

li.append(inputColumn())

check(li)

if flag=="n":

return li

else:

return li

def createsqlFirst(table,li):

sql="insert into "+table+"("

for element in li:

sql=sql+element+","

sql=sql[:-1]

sql=sql+")"

return sql

def createsql(sql,li,numStr):

sql=sql+"values"+"("

for element in li:

sql=sql+"'"+element+numStr+"',"

sql=sql[:-1]

sql=sql+")"

return sql

if __name__=='__main__':

main()

输入的参数如下：

在inserData.sql文件里面输出的代码如下：

insert into admin(username,password,name,ontr)values('username1','password1','name1','ontr1');

insert into admin(username,password,name,ontr)values('username2','password2','name2','ontr2');

insert into admin(username,password,name,ontr)values('username3','password3','name3','ontr3');

insert into admin(username,password,name,ontr)values('username4','password4','name4','ontr4');

insert into admin(username,password,name,ontr)values('username5','password5','name5','ontr5');

insert into admin(username,password,name,ontr)values('username6','password6','name6','ontr6');

insert into admin(username,password,name,ontr)values('username7','password7','name7','ontr7');

insert into admin(username,password,name,ontr)values('username8','password8','name8','ontr8');

insert into admin(username,password,name,ontr)values('username9','password9','name9','ontr9');

insert into admin(username,password,name,ontr)values('username10','password10','name10','ontr10');

insert into admin(username,password,name,ontr)values('username11','password11','name11','ontr11');

insert into admin(username,password,name,ontr)values('username12','password12','name12','ontr12');

insert into admin(username,password,name,ontr)values('username13','password13','name13','ontr13');

insert into admin(username,password,name,ontr)values('username14','password14','name14','ontr14');

insert into admin(username,password,name,ontr)values('username15','password15','name15','ontr15');

insert into admin(username,password,name,ontr)values('username16','password16','name16','ontr16');

insert into admin(username,password,name,ontr)values('username17','password17','name17','ontr17');

insert into admin(username,password,name,ontr)values('username18','password18','name18','ontr18');

insert into admin(username,password,name,ontr)values('username19','password19','name19','ontr19');

insert into admin(username,password,name,ontr)values('username20','password20','name20','ontr20');

缺陷：这个脚本虽然可以动态生成插入的sql代码，但是无法生成除了char和varchar其他类型的sql代码。如果是复杂的sql代码，还是用自己手写脚本吧