python将excel自适应导入数据库

程序员霖霖 2021-02-22 12:03:23
Python SQLite xlrd


python 将excel导入数据库

功能如下:

  1. 将excel中每个sheet自动导入数据库,以sheet名称为表名在数据库中创建表;
  2. 将每个sheet中的数据添加至数据库对应表中;默认以第一行为该表的列表名,其它行作为数据;
  3. 查找数据中存在的表文件名;
  4. 查找数据库中某表的说有数据;
  5. 向数据库某表中添加数据;
  6. 自适应创建表;

小结:

使用python sqlite创建表,查看表数量及名称,查看表的列表名称,查看表的内容,表数据插入;

代码如下:

# This is a sample Python script.
# Press Shift+F10 to execute it or replace it with your code.
# Press Double Shift to search everywhere for classes, files, tool windows, actions, and settings.
import xlrd
import sqlite3
import pprint
# 连接数据库
def connect_db(file_path):
conn = sqlite3.connect(file_path)
return conn
# 获取数据库中所有表的名字
def get_tables(conn):
sql = "SELECT * FROM sys.Tables"
cursor = conn.cursor()
# 获取表名
cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
tables = [tuple[0] for tuple in cursor.fetchall()]
print(tables)
return tables
# 获取数据库中,表table_name 的表头信息,列名称
def get_desc(conn, table_name):
cursor = conn.cursor()
sql1 = "select * from {}".format(table_name)
cursor.execute(sql1)
col_name_list = [tuple[0] for tuple in cursor.description]
sql = "("
for index in col_name_list:
sql += index + ","
ret = sql[:-1] + ")"
return ret
# 显示数据库中表table_name 的所有元素
def show_table(conn, table_name):
cursor = conn.cursor()
sql = "select * from {}".format(table_name)
cursor.execute(sql)
pprint.pprint(cursor.fetchall())
# 创建数据库,table_items 为 table_name 中列名称,即表头信息
def create_table(conn, table_name, table_items):
sqlline = "create table {} (".format(table_name)
for i in table_items:
sqlline += i + " text,"
sql_line = sqlline[:-1] + ")"
cursor = conn.cursor()
cursor.execute(sql_line)
conn.commit()
# 数据库文件插入,content_items 为需要插入表 table_name 的数据信息
def insert_data(conn, table_name, content_items):
sql = ''' insert into {}
{}
values ('''.format(table_name, get_desc(conn, table_name))
for index in content_items:
sql += str(index) + ","
ret = sql[:-1] + ")"
cursor = conn.cursor()
cursor.execute(ret)
conn.commit()
#数据库中table_name表中查找 table_head = table_content 的项
def find_data(conn, table_name, table_head, table_content):
sql = "select {table_head} from {table_name} where {table_head} = {table_content}".format(table_head=table_head,
table_name=table_name,
table_content=table_content)
cursor = conn.cursor()
cursor.execute(sql)
pprint.pprint(cursor.fetchone())
# 读取exel表格,并在数据库中创建该表
def read_exel(file_path, conn):
if not file_path.endswith("xlsx"):
print("path_wrong")
# 获取一个Book对象
book = xlrd.open_workbook(file_path)
# 获取一个sheet对象的列表
sheets = book.sheets()
for sheet in sheets:
sheet_name = sheet.name
# 获取表行数
rows = sheet.get_rows()
for index, row in enumerate(rows):
table_items = [tuple.value for tuple in row]
print(table_items)
if index == 0:
# 默认第一行为表头信息,在数据库中创建该表
create_table(conn, sheet_name, list(table_items))
else:
# 将次sheet中的每一行都插入数据库中
insert_data(conn, sheet_name, table_items)
show_table(conn, sheet_name)
def main():
# Use a breakpoint in the code line below to debug your script.
conn = connect_db("test.db")
table = get_tables(conn)
find_data(conn,table[0],"测试",2.0)
show_table(conn,table[0])
# file_path = "test.xlsx"
# read_exel(file_path, conn[0])
conn.close()
# Press the green button in the gutter to run the script.
if __name__ == '__main__':
main()

使用工具:

xlrd
python 安装 xlrd 注意事项,如果直接使用:

pip install xlrd
  •  

可能会遇到,打开excel表格失败

最简单避免使用xlrd错误的安装方式为:

pip install pip install xlrd==1.2.0

好了,今天的分享就到这,如果你对Python感兴趣,欢迎加入我们【python学习交流裙】,免费领取学习资料和源码。  

版权声明
本文为[程序员霖霖]所创,转载请带上原文链接,感谢
https://my.oschina.net/u/4636319/blog/4958780

  1. Python 3 entry, see this is enough
  2. 华为大佬打造的400集Python视频学起来,学完万物皆可爬
  3. 400 episodes of Python video created by Huawei boss
  4. django之csrf_exempt解决跨域请求的问题
  5. CSRF of Django_ Exempt solves the problem of cross domain requests
  6. 1.7 万 Star!一个简单实用的 Python 进度条库
  7. 17000 stars! A simple and practical Python progress bar library
  8. Python爬虫:设置Cookie解决网站拦截并爬取蚂蚁短租
  9. Python crawler: setting cookie to solve website interception and crawling ant short rent
  10. Python-Net编程
  11. Python net programming
  12. 学习Python数学英语基础重要吗?Python教程!
  13. Is it important to learn the basics of math and English in Python!
  14. Python数据分析常用库有哪些?Python学习!
  15. What are the common libraries for Python data analysis? Learn Python!
  16. win 创建python虚拟环境
  17. Creating Python virtual environment with win
  18. In order to automatically collect B station barrage, I developed a tool in Python
  19. 用Python编程语言来实现阿姆斯特朗数的检查
  20. Using python programming language to check Armstrong number
  21. Python中的解决中文字符编码的问题
  22. Solving the problem of Chinese character coding in Python
  23. Translation: practical Python Programming 02_ 01_ Datatypes
  24. Installation and use of Python and tensorflow in win10 environment (Python version 3.6, tensorflow version 1.6)
  25. Python series 46
  26. Linux安装Python3
  27. 【python接口自动化】- 正则用例参数化
  28. Python RestFul Api 设计
  29. filecmp --- 文件及目录的比较│Python标准库
  30. Installing python3 on Linux
  31. [Python] Matplotlib 圖表的繪製和美化技巧
  32. (資料科學學習手札108)Python+Dash快速web應用開發——靜態部件篇(上)
  33. 翻譯:《實用的Python程式設計》02_01_Datatypes
  34. 【python接口自动化】- 正则用例参数化
  35. 翻译:《实用的Python编程》02_02_Containers
  36. 两年Java,去字节跳动写Python和Go
  37. [Python interface automation] - regular use case parameterization
  38. Python restful API design
  39. 翻译:《实用的Python编程》02_02_Containers
  40. 两年Java,去字节跳动写Python和Go
  41. 翻译:《实用的Python编程》02_02_Containers
  42. Python基于粒子群优化的投资组合优化研究
  43. ubuntu部署django项目
  44. 兩年Java,去位元組跳動寫Python和Go
  45. 翻譯:《實用的Python程式設計》02_02_Containers
  46. 这样学习Python,爷爷都学会了!超简单Python入门
  47. [Python] 基于 jieba 的中文分词总结
  48. 【python】递归听了N次也没印象,读完这篇你就懂了
  49. [Python] 基于 jieba 的中文分词总结
  50. 人理解迭代,神则体会递归,从电影艺术到Python代码实现神的逆向思维模式
  51. [Python] 基於 jieba 的中文分詞總結
  52. Python属于后端开发还是前端开发?Python入门!
  53. 【python】递归听了N次也没印象,读完这篇你就懂了
  54. 一天快速入门python
  55. 学习Python对年龄有没有要求?30岁可以吗?
  56. 清华教授!12小时整理的最全Python教程(文末无偿分享)
  57. Filecmp -- comparison of files and directories
  58. Drawing and beautifying skills of [Python] Matplotlib chart
  59. Python + dash rapid web application development static components
  60. Translation: practical Python Programming 02_ 01_ Datatypes