使用python从多个csv文件构建sqlite数据库

2024-09-28 20:57:05 发布

您现在位置:Python中文网/ 问答频道 /正文

我正在尝试使用Python(enthoughtcanopy编辑器)从csv文件构建一个sqlite数据库。 我想将文件夹中包含的所有csv文件导入到单独的表中,表名是根据csv文件名指定的。 到目前为止,我已经成功地将在线找到的代码块复制并粘贴到以下位置:

import csv
import sqlite3
# Create the database
connection = sqlite3.connect('C:/ROAST/3_ANALYSIS/03_SQL-PY/primo.db')
cursor = connection.cursor()

# Create the table
cursor.execute('DROP TABLE IF EXISTS A08')
cursor.execute('CREATE TABLE  A08 (uno, due, tre) ')
connection.commit()

# Load the CSV file into CSV reader
csvfile = open('C:/ROAST/3_ANALYSIS/03_SQL-PY\A08_csv/A08_B1_T5.csv', 'rb')
creader = csv.reader(csvfile, delimiter=',', quotechar='|')

# Iterate through the CSV reader, inserting values into the database
for t in creader:
cursor.execute('INSERT INTO A08 VALUES (?,?,?)', t )

# Close the csv file, commit changes, and close the connection
csvfile.close()
connection.commit()
connection.close()'

Tags: 文件csvthecsvfileimportcloseexecutecreate
1条回答
网友
1楼 · 发布于 2024-09-28 20:57:05

下面是我如何解决你的问题。在

import csv
import sqlite3
import glob
import os

def do_directory(dirname, db):
    for filename in glob.glob(os.path.join(dirname, '*.csv')):
        do_file(filename, db)

def do_file(filename, db):
        with open(filename) as f:
            with db:
                data = csv.DictReader(f)
                cols = data.fieldnames
                table=os.path.splitext(os.path.basename(filename))[0]

                sql = 'drop table if exists "{}"'.format(table)
                db.execute(sql)

                sql = 'create table "{table}" ( {cols} )'.format(
                    table=table,
                    cols=','.join('"{}"'.format(col) for col in cols))
                db.execute(sql)

                sql = 'insert into "{table}" values ( {vals} )'.format(
                    table=table,
                    vals=','.join('?' for col in cols))
                db.executemany(sql, (list(map(row.get, cols)) for row in data))

if __name__ == '__main__':
    conn = sqlite3.connect('foo.db')
    do_directory('.', conn)

适当地替换最后的__main__部分。也许你会想要:

^{pr2}$

或者

connection = sqlite3.connect('C:/ROAST/3_ANALYSIS/03_SQL-PY/primo.db')
do_directory('C:/ROAST/3_ANALYSIS/03_SQL-PY\A08_csv',connection)

相关问题 更多 >