python 将excel导入数据库
 
功能如下：
 
将excel中每个sheet自动导入数据库，以sheet名称为表名在数据库中创建表;
将每个sheet中的数据添加至数据库对应表中;默认以第一行为该表的列表名，其它行作为数据;
查找数据中存在的表文件名;
查找数据库中某表的说有数据;
向数据库某表中添加数据;
自适应创建表;
 
小结：
 
使用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

核心提示：日常工作中，我们常常需要建立一些有规律的数据库。例如我为了管理全乡的农业税，需建立一数据库，该数据库第一个字段名为村名，第二个字段名为组别。
 
 
 
　　日常工作中，我们常常需要建立一些有规律的数据库。例如我为了管理全乡的农业税，需建立一数据库，该数据库第一个字段名为村名，第二个字段名为组别。我乡共19个村，每个村7～17个组不等，共计258个组。这个数据库用数据库软件（哪怕是Visual FoxPro 6.0或是Access97等高档次的）很不好建立——逐个儿输入吗，只有傻瓜才有这种想法。用Access宏或FoxPro编程来输入吧，这些数据似乎还嫌不够规则（每个村对应的组数不一定相同），这个程序编写可就不那么简单了，除非你是编程高手兼编程迷，否则可有小题大作之嫌了。
 
 
　　其实Excel提供了一些很有用的功能，可让我们任何一个人都可轻松搞定这些数据库： 
 
 
　　第一步：打开Excel97（Excel2000当然也行），在A列单元格第1行填上“村名”，第2行填上“东山村”，第19行填上“年背岭村”（注：东山17个组，2＋17=19据此推算），第28行填上“横坡村”（算法同前，牛背岭村9个组：19＋9=28），如此类推把19个村名填好。
 
 
　　第二步：在第B列第1行填上“组别”，第2行填上“第1组”并在此按鼠标右键选择“复制”把这三个字复制剪贴板，然后在每一个填有村名的那一行的B列点一下鼠标右键选择“粘贴”在那里填上一个“第1组”。
 
 
　　第三步；用鼠标点击选中A2“东山村”单元格，然后把鼠标单元格右下角（此时鼠标变为单“十”字形），按住鼠标往下拖动，拖过的地方会被自动填上“东山村”字样。用同样的方法可以把其它村名和组别用鼠标“一拖了之”。填组别时你别担心Excel会把组别全部填为“第1组”，只要你别把“第1组”写成“第一组”，Excel会自动把它识别为序列进行处理。所以拖动“第1组”时，填写的结果为“第2组”“第3组”……填完这两个字段后，其它的数据可以继续在Excel中填写，也可等以后在数据库软件中填写，反正劳动强度差不多。
 
 
　　第四步：保存文件。如果你需要建立的是Access数据库，那么别管它，就用Excel默认的“.xls”格式保存下来。如果你需要建立的是FoxPro数据库，那么请以Dbase 4 （.dbf）格式保存文件。
 
 
　　第五步：如果需要的是Access数据库，那么你还必需新建一个Access数据库，在“新建表”的对话框里，你选择“导入表”然后在导入对话框中选择你刚刚存盘的“.xls”文件。（什么？你找不到？！这个对话框默认的文件类型是Microsoft Access，只要你改为Microsoft Excel 就能找到了），选择好导入文件后，你只要注意把一个“第一行包含列标题”的复选框 芯托辛耍ㄈ绻 你不需要ID字段，你可以在Access向你推荐主关键字时拒绝——选择“不要主关键字”），其余的你都可视而不见，只管按“下一步”直至完成。导入完成后你可以打数据库进行使用或修改。如果你需要的是FoxPro数据库，那么更简单，可以直接用FoxPro打开上一步你存盘的“.dbf”文件，根据需要进行一些诸如字段宽度、字段数据类型设置就可以使用了。
 
 
　　说到这里，你可能会说“呀，太简单了，我也会”，那么好了，我写这篇文章的目的就是要大家尽可能不再做那些很麻烦的事，“该偷懒时就偷懒”嘛。
 

 #region 连接并查询Excel2003数据库
    /// <summary>
    /// 连接并查询Excel2003数据库
    /// </summary>
    protected void btnExcel2003_Click(object sender, EventArgs e)
    {
        string connStr = "provider=Microsoft.Jet.Oledb.4.0;Data Source=|DataDirectory|//Excel2003.xls;extended properties=excel 8.0";
        OleDbConnection conn = new OleDbConnection(connStr);
        try
        {
            conn.Open();
            lblExcel2003.Text = "成功打开Excel2003数据库！";
            /*
             在代码中引用工作表时，应将表名表示为“[工作表名$]”，遇到字段为数据库保留关键字时，给该字段名加上[]以示区别，
             如定义select 语句时：string connStr=”select * from [login$] where username=’abc’ and [password]=’abc123’ ”；
             如果在数据表中用数字作为文本类型数据时，则应在数字前加单引号将默认的数值强行设定为文本类型。
            */
            string sql = "select * from [UserInfo$] where UserName='abc'and [password]=123";
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader dr = cmd.ExecuteReader();
            if (dr.HasRows)
            {
                dr.Read();
                lblExcel2003.Text += "--并且成功读取第1条记录：UserNaem=" + dr["UserName"] + " Pwd=" + dr["password"].ToString();
            }
            else
            {
                lblExcel2003.Text += "--但没有读取到任何数据！";
            }
        }
        catch (OleDbException ex)
        {
            lblExcel2003.Text = "打开Excel2003数据库文件失败！";
            Response.Write("-----------------------------------------------------------------------------------------<br/>"
                            + ex.StackTrace
                            + "<br/>----------------------------------------------------------------------------------------------<br/>");
        }
        finally
        {
            if (conn.State == ConnectionState.Open)
                conn.Close();
        }
    }
    #endregion
    #region 连接并查询Excel2007数据库
    /// <summary>
    /// 连接并查询Excel2007数据库
    /// </summary>
    protected void btnExcel2007_Click(object sender, EventArgs e)
    {
        string connStr = "provider=Microsoft.Ace.Oledb.12.0;Data Source=|DataDirectory|//Excel2007.xlsx;extended properties=excel 12.0";
        OleDbConnection conn = new OleDbConnection(connStr);
        try
        {
            conn.Open();
            lblExcel2007.Text = "成功打开Excel2007数据库！";
            /*
             在代码中引用工作表时，应将表名表示为“[工作表名$]”，遇到字段为数据库保留关键字时，给该字段名加上[]以示区别，
             如定义select 语句时：string connStr=”select * from [login$] where username=’abc’ and [password]=’abc123’ ”；
             如果在数据表中用数字作为文本类型数据时，则应在数字前加单引号将默认的数值强行设定为文本类型。
            */
            string sql = "select * from [UserInfo$] where UserName='abc'and pwd='abc123'";
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader dr = cmd.ExecuteReader();
            if (dr.HasRows)
            {
                dr.Read();
                lblExcel2007.Text += "--并且成功读取第1条记录：UserNaem=" + dr["UserName"] + " Pwd=" + dr["Pwd"].ToString();
            }
            else
            {
                lblExcel2007.Text += "--但没有读取到任何数据！";
            }
        }
        catch (OleDbException ex)
        {
            lblExcel2007.Text = "打开Excel2007数据库文件失败！";
            Response.Write("-----------------------------------------------------------------------------------------<br/>"
                            + ex.StackTrace
                            + "<br/>----------------------------------------------------------------------------------------------<br/>");
        }
        finally
        {
            if (conn.State == ConnectionState.Open)
                conn.Close();
        }
    }
    #endregion