怎么把EXCEL中的数据自动添加到SQL数据库
发布网友
发布时间：2022-04-27 04:07
共3个回答
懂视网
时间：2022-04-08 11:45
2.
import MySQLdb,xlrd,sys
db_config={‘user‘:‘root‘,‘passwd‘:‘passwd‘,‘host‘:‘localhost‘,‘db‘:‘test‘,‘port‘:3306}
def get_connction(db_config):
‘‘‘
返回数据库的链接,游标信息
‘‘‘
try:
conn=MySQLdb.connect(**db_config)
cur=conn.cursor()
except Exception as e:
print ‘Can‘t Connect the database: ‘,e
sys.exit(1)
return conn,cur
def main():
conn,cur=get_connction(db_config)
sql=‘‘‘create table if not exists basic_info
(id int primary key not null,
name char(20),
gender char(2)
)‘‘‘
cur.execute(sql)
excel=xlrd.open_workbook(r‘D:/data.xls‘)
sheet=excel.sheets()[0]
nrow=sheet.nrows
for row in range(1,nrow): #获取xls每行数据,insert入表，table中id是int型号,填充时也用%s
cur.execute(‘insert into basic_info values (%s,%s,%s)‘,tuple(sheet.row_values(row)))
conn.commit()
cur.close()
conn.commit()
if __name__==‘__main__‘:
main()
xls填充sql
标签：
热心网友
时间：2022-04-08 08:53
完整代码如下：
<%
on error resume next
导入excel电子表格数据到sql sever数据库 by dicky 2004-12-27 16:41:12
function open_conn(sqldatabasename,sqlpassword,sqlusername,sqllocalname,sqlconn)
创建数据库连接对象并打开数据库连接
dim connstr
sql server数据库连接参数：数据库名、用户密码、用户名、连接名（本地用local，外地用ip）
connstr = "provider=sqloledb; user id=" & sqlusername & "; password=" & sqlpassword & "; initial catalog = " & sqldatabasename & "; data source=" & sqllocalname & ";"
set sqlconn = server.createobject("adodb.connection")
sqlconn.open connstr
if err then
err.clear
set sqlconn = nothing
response.write "数据连接错误!"
response.end
end if
end function
function close_conn(sqlconn)
关闭数据库连接并清除数据库连接对象
if isobject(sqlconn) then
sqlconn.close
set sqlconn = nothing
end if
end function
call open_conn("shat_edg","","sa","(local)",sqlconn) 打开本地sql server数据库连接
call open_conn("shat_edg","","sa","ssh03",sqlconn1) 打开远程sql server数据库连接
function get_emp_cnname(ntaccnt)
根据用户nt帐号得到用户中文名
dim sql1,rs1
sql1 = "select emp_cname from rf_employee where emp_ntaccnt="&ntaccnt&""
set rs1 = server.createobject("adodb.recordset")
rs1.open sql1,sqlconn1,1,1
if rs1.eof then
get_emp_cnname = ""
else
get_emp_cnname = rs1("emp_cname")
end if
rs1.close
set rs1 = nothing
end function %>
<html>
<head>
<title>导入excel电子表格数据到sql sever数据库</title>
<body bgcolor="#acd9af">
<center><b>导入excel电子表格数据到sql sever数据库</b></center>
<form method="post" name="form1">
?????源（本地库）:
<select name="table" title="请选择需要导入数据的表">
<option></option>
<% dim rssqldatabasetable
set rssqldatabasetable = sqlconn.openschema(20)
do while not rssqldatabasetable.eof %>
<option<%if trim(request("table"))=rssqldatabasetable(2) then response.write " selected"%>><%=rssqldatabasetable(2)%></option>
<% rssqldatabasetable.movenext:loop
set rssqldatabasetable = nothing %>
</select><input type=submit name=submit value="?始?出">
<br>???目?表（远程库）:
<select name="table1" title="请选择需要导入数据的表">
<option></option>
<% dim rssqldatabasetable1
set rssqldatabasetable1 = sqlconn1.openschema(20)
do while not rssqldatabasetable1.eof %>
<option<%if trim(request("table1"))=rssqldatabasetable1(2) then response.write " selected"%>><%=rssqldatabasetable1(2)%></option>
<% rssqldatabasetable1.movenext:loop
set rssqldatabasetable1 = nothing %>
</select>
</form>
<font color=blue>?出?程中?不要刷新?面！</font><br>
<%
if trim(request("table1")) <> "" then
dim sql,rs
sql = "select * from sheet1$" sheet1$是我们实现用sql server自身数据转换功能得到的表名
set rs = sqlconn.execute(sql)
if rs.eof and rs.bof then
response.write "没有找到你需要的数据!!<br>"
else
do while not rs.eof
sqlinsert = "insert into "&trim(request("table1"))&" (edg_project_name,edg_project_no,edg_project_vm,edg_project_vm_cnname,edg_project_m,edg_project_m_cnname,edg_project_director,edg_project_director_cnname) values ("&trim(rs("工程名称"))&","&trim(rs("工程编号"))&","&trim(rs("项目副理"))&","&trim(rs("项目副理"))&"("&get_emp_cnname(trim(rs("项目副理")))&")"&","&trim(rs("项目经理"))&","&trim(rs("项目经理"))&"("&get_emp_cnname(trim(rs("项目经理")))&")"&","&trim(rs("项目总监"))&","&trim(rs("项目总监"))&"("&get_emp_cnname(trim(rs("项目总监")))&")"&")"
response.write sqlinsert
此处插入的值根据实际数据库结构进行调整
sqlconn1.execute(sqlinsert)插入excel表格?所有??
rs.movenext
loop
response.write "<font color=red>恭喜，成功?出数据！^_^</font><br>"
end if
rs.close
set rs = nothing
end if
call close_conn(sqlconn) 关闭excel数据库连接
call close_conn(sqlconn1) 关闭sql server数据库连接 %>
</body>
</html>
热心网友
时间：2022-04-08 10:11
