Importing excel into database adaptively by Python

Programmer Lin Lin 2021-02-22 21:26:53
importing excel database adaptively python


python take excel Import database

Function as follows :

  1. take excel Each of them sheet Automatically import the database , With sheet Name is the name of the table. Create a table in the database ;
  2. Each one sheet The data in the database is added to the corresponding table in the database ; The first line is the list name of the table by default , Other rows as data ;
  3. Find the table file name that exists in the data ;
  4. Look up a table in the database and say it has data ;
  5. Add data to a table in the database ;
  6. Adaptive table creation ;

Summary :

Use python sqlite Create table , Look at the number and name of the tables , Look at the list name of the table , Check the contents of the table , Table data insertion ;

The code is as follows :

# 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
# Connect to database 
def connect_db(file_path):
conn = sqlite3.connect(file_path)
return conn
# Get the names of all the tables in the database 
def get_tables(conn):
sql = "SELECT * FROM sys.Tables"
cursor = conn.cursor()
# Get table name 
cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
tables = [tuple[0] for tuple in cursor.fetchall()]
print(tables)
return tables
# Get... In the database , surface table_name The header information of , Column 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
# Show the tables in the database table_name All elements of 
def show_table(conn, table_name):
cursor = conn.cursor()
sql = "select * from {}".format(table_name)
cursor.execute(sql)
pprint.pprint(cursor.fetchall())
# Create database ,table_items by table_name Column name in , That is, the header information 
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()
# Database file insert ,content_items Insert table as needed table_name Data information 
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()
# In the database table_name Find in table table_head = table_content The item 
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())
# Read exel form , And create the table in the database 
def read_exel(file_path, conn):
if not file_path.endswith("xlsx"):
print("path_wrong")
# Get one Book object 
book = xlrd.open_workbook(file_path)
# Get one sheet List of objects 
sheets = book.sheets()
for sheet in sheets:
sheet_name = sheet.name
# Get the number of table rows 
rows = sheet.get_rows()
for index, row in enumerate(rows):
table_items = [tuple.value for tuple in row]
print(table_items)
if index == 0:
# The first line is header information by default , Create the table in the database 
create_table(conn, sheet_name, list(table_items))
else:
# General sheet Every row in is inserted into the database 
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]," test ",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()

Using tools :

xlrd
python install xlrd matters needing attention , If used directly :

pip install xlrd
  •  

May come across , open excel Form failed

The simplest way to avoid using xlrd The wrong way to install is :

pip install pip install xlrd==1.2.0

Okay , Today's sharing is here , If you are right about Python Interested in , Welcome to join us 【python Exchange of learning skirt 】, Free access to learning materials and source code .  

版权声明
本文为[Programmer Lin Lin]所创,转载请带上原文链接,感谢
https://pythonmana.com/2021/02/20210222120251484P.html

  1. 使用Python开发DeFi项目
  2. python 函数详解
  3. Python工程师是做什么的?前景如何?
  4. Python - zip() 函数
  5. 30 周年生日,Python 先驱是怎么评价这门语言的?
  6. python将excel自适应导入数据库
  7. 从小白到大师,这里有一份Pandas入门指南
  8. [Python] 茎叶图和复合饼图的画法
  9. [Python interface automation] - regular use case parameterization
  10. Translation: practical Python Programming 02_ 02_ Containers
  11. Two years of Java, to write Python and go
  12. Translation: practical Python Programming 02_ 02_ Containers
  13. Two years of Java, to write Python and go
  14. Python-geoplot 空间核密度估计图绘制
  15. Python-seaborn 经济学人经典图表仿制
  16. python空间绘图- regionmask掩膜操作示例
  17. Python 空间绘图 - Cartopy 经纬度添加
  18. Python-pykrige包-克里金(Kriging)插值计算及可视化绘制
  19. Python 批量重采样、掩膜、坡度提取
  20. python - 多种交通方式可达圈分析
  21. Python 空间绘图 - 房价气泡图绘制
  22. Translation: practical Python Programming 02_ 02_ Containers
  23. Research on Portfolio Optimization Based on particle swarm optimization
  24. Ubuntu deploying Django project
  25. Two years of Java, write Python and go without byte beating
  26. Translation: practical Python Programming 02_ 02_ Containers
  27. So learn python, grandfather learned! Introduction to super simple Python
  28. python3 多线程 与 mongo亿级消费日志数据 新鲜demo 【优化第一版】
  29. Summary of Chinese word segmentation based on Jieba
  30. I've heard it n times, but I'm not impressed. After reading this, you'll understand
  31. Summary of Chinese word segmentation based on Jieba
  32. From movie art to Python code to realize God's reverse thinking mode
  33. Summary of Chinese word segmentation based on Jieba
  34. ARIMA模型预测CO2浓度时间序列-python实现
  35. Python belongs to back-end development or front-end development? Introduction to Python!
  36. python isinstance()
  37. I've heard it n times, but I'm not impressed. After reading this, you'll understand
  38. This article will familiarize you with the transformation process of Python - & gt; cafe - & gt; om model
  39. 如何用Python一键修改上万个文件名
  40. One day quick start to Python
  41. Python 学习笔记: List
  42. 翻译:《实用的Python编程》02_03_Formatting
  43. Is there any age requirement for learning Python? Is 30 OK?
  44. Professor Tsinghua! The most complete Python tutorial in 12 hours (free sharing at the end of the article)
  45. Using Python to develop defi project
  46. Detailed explanation of Python function
  47. Python 可变类型作为函数默认参数时的副作用
  48. What do Python engineers do? What's their future?
  49. 这是我见过最好的Python教程:十分钟带你认识Python
  50. Python欢喜冤家:爬虫与反爬虫带着处理方案来给大家拜年了
  51. Python - zip() function
  52. 写Python会遇到如下的错误:ModuleNotFoundError: No module named 'email.mime'; 'email' is not a package
  53. Python类的调用以及私有和公有属性方法的调用
  54. Python类的专有方法
  55. Python基础之:数字字符串和列表
  56. How did Python pioneers evaluate this language on their 30th birthday?
  57. Python基础之:数字字符串和列表
  58. Python基础之:数字字符串和列表
  59. 窥探未来不是梦,python数据分析轻松实现
  60. This article will familiarize you with the transformation process of Python - & gt; cafe - & gt; om model