使用脚本语言动态数据库

2021-12-08 00:00:00 python sqlalchemy sql database sqlite

我有一组要处理的 .csv 文件.使用 SQL 查询处理它会容易得多.我想知道是否有某种方法可以加载 .csv 文件并使用 SQL 语言通过诸如 python 或 ruby​​ 之类的脚本语言来查看它.用类似于 ActiveRecord 的东西加载它会很棒.

I have a set of .csv files that I want to process. It would be far easier to process it with SQL queries. I wonder if there is some way to load a .csv file and use SQL language to look into it with a scripting language like python or ruby. Loading it with something similar to ActiveRecord would be awesome.

问题是我不想在运行我的脚本之前在某处运行数据库.除了脚本语言和一些模块之外，我不需要额外的安装.

The problem is that I don't want to have to run a database somewhere prior to running my script. I souldn't have additionnal installations needed outside of the scripting language and some modules.

我的问题是我应该使用哪种语言和哪些模块来完成这项任务.我环顾四周，找不到适合我需要的任何东西.甚至有可能吗?

My question is which language and what modules should I use for this task. I looked around and can't find anything that suits my need. Is it even possible?

推荐答案

sqlite3，包含在python中.使用它，您可以创建一个数据库(内存)并向其中添加行，并执行 SQL 查询.

There's sqlite3, included into python. With it you can create a database (on memory) and add rows to it, and perform SQL queries.

如果你想要简洁的类似 ActiveRecord 的功能，你应该添加一个外部 ORM，比如 sqlalchemy.不过这是一个单独的下载

If you want neat ActiveRecord-like functionality you should add an external ORM, like sqlalchemy. That's a separate download though

使用 sqlalchemy 的快速示例:

Quick example using sqlalchemy:

from sqlalchemy import create_engine, Column, String, Integer, MetaData, Table
from sqlalchemy.orm import mapper, create_session
import csv
CSV_FILE = 'foo.csv'
engine = create_engine('sqlite://') # memory-only database

table = None
metadata = MetaData(bind=engine)
with open(CSV_FILE) as f:
    # assume first line is header
    cf = csv.DictReader(f, delimiter=',')
    for row in cf:
        if table is None:
            # create the table
            table = Table('foo', metadata, 
                Column('id', Integer, primary_key=True),
                *(Column(rowname, String()) for rowname in row.keys()))
            table.create()
        # insert data into the table
        table.insert().values(**row).execute()

class CsvTable(object): pass
mapper(CsvTable, table)
session = create_session(bind=engine, autocommit=False, autoflush=True)

现在可以查询数据库，按任意字段过滤等

Now you can query the database, filtering by any field, etc.

假设你在这个 csv 上运行上面的代码:

Suppose you run the code above on this csv:

name,age,nickname
nosklo,32,nosklo
Afila Tun,32,afilatun
Foo Bar,33,baz

这将在内存中创建并填充一个包含字段name、age、nickname 的表.然后就可以查询表了:

That will create and populate a table in memory with fields name, age, nickname. You can then query the table:

for r in session.query(CsvTable).filter(CsvTable.age == '32'):
    print r.name, r.age, r.nickname

这将自动创建并运行 SELECT 查询并返回正确的行.

That will automatically create and run a SELECT query and return the correct rows.

使用 sqlalchemy 的另一个优点是，如果您决定将来使用另一个更强大的数据库，您可以在不更改代码的情况下实际使用.

Another advantage of using sqlalchemy is that, if you decide to use another, more powerful database in the future, you can do so pratically without changing the code.

相关文章