使用Python、SQLAlchemy和Sqlite设置/插入多对多数据库

Question

我正在学习Python，作为我的第一个项目，我在处理Twitter的RSS源，解析数据，并把这些数据插入到一个sqlite数据库中。我已经成功地把每个RSS源的条目解析成一个内容变量（比如说，“你应该低买...”），一个网址变量（比如说，u'http://bit.ly/HbFwL），还有一个标签列表（比如说，#stocks', u'#stockmarket', u'#finance', u'#money', u'#mkt'）。我也成功地把这三部分信息插入到sqlite的“RSSEntries”表中，每一行代表一个不同的RSS条目或推文。

不过，我想建立一个数据库，让每个RSS源条目（也就是每条推文）和与之相关的标签之间有多对多的关系。所以，我用sqlalchemy设置了以下几个表（第一个表只包含我想下载和解析的Twitter用户的RSS源网址）：

RSSFeeds = schema.Table('feeds', metadata,
    schema.Column('id', types.Integer, 
        schema.Sequence('feeds_seq_id', optional=True), primary_key=True),
    schema.Column('url', types.VARCHAR(1000), default=u''),
)

RSSEntries = schema.Table('entries', metadata,
    schema.Column('id', types.Integer, 
        schema.Sequence('entries_seq_id', optional=True), primary_key=True),
    schema.Column('feed_id', types.Integer, schema.ForeignKey('feeds.id')),
    schema.Column('short_url', types.VARCHAR(1000), default=u''),
    schema.Column('content', types.Text(), nullable=False),
    schema.Column('hashtags', types.Unicode(255)),
)

tag_table = schema.Table('tag', metadata,
    schema.Column('id', types.Integer,
       schema.Sequence('tag_seq_id', optional=True), primary_key=True),
    schema.Column('tagname', types.Unicode(20), nullable=False, unique=True)
)

entrytag_table = schema.Table('entrytag', metadata,
    schema.Column('id', types.Integer,
        schema.Sequence('entrytag_seq_id', optional=True), primary_key=True),
    schema.Column('entryid', types.Integer, schema.ForeignKey('entries.id')),
    schema.Column('tagid', types.Integer, schema.ForeignKey('tag.id')),
)

到目前为止，我已经能够成功地把这三条主要信息插入到RSSEntries表中，使用的代码如下（简化了...）

engine = create_engine('sqlite:///test.sqlite', echo=True)
conn = engine.connect()
.........
conn.execute('INSERT INTO entries (feed_id, short_url, content, hashtags) VALUES 
    (?,?,?,?)', (id, tinyurl, content, hashtags))

现在，问题来了。我该如何把数据插入到feedtag和tagname表中？这对我来说是个大难题，因为目前标签变量是一个列表，而每个源条目可能包含0到6个标签。我知道如何把整个列表插入到一个列中，但不知道如何把列表中的每个元素插入到不同的列（或者在这个例子中，是不同的行）。更大的难点是，如何把单个标签插入到tagname表中，因为一个标签可能在多个不同的源条目中使用，然后如何让“关联”在feedtag表中正确显示。

简单来说，我知道所有表完成后应该是什么样子的，但我不知道该怎么写代码把数据放进tagname和feedtag表中。整个“多对多”的设置对我来说是新的。

我真的需要你的帮助。提前感谢任何建议。

-Greg

P.S. - 编辑 - 感谢Ants Aasma的优秀建议，我几乎已经让整个项目运作起来了。具体来说，第一和第二个建议的代码块现在运行得很好，但我在实现第三个代码块时遇到了问题。我得到了以下错误：

Traceback (most recent call last):
  File "RSS_sqlalchemy.py", line 242, in <module>
    store_feed_items(id, entries)
  File "RSS_sqlalchemy.py", line 196, in store_feed_items
    [{'feedid': entry_id, 'tagid': tag_ids[tag]} for tag in hashtags2])
NameError: global name 'entry_id' is not defined

然后，因为我不知道Ants Aasma的“entry_id”部分是从哪里来的，我尝试用“entries.id”替换它，想着这可能会插入“entries”表中的“id”。但是，这样我又得到了这个错误：

Traceback (most recent call last):
  File "RSS_sqlalchemy.py", line 242, in <module>
    store_feed_items(id, entries)
  File "RSS_sqlalchemy.py", line 196, in store_feed_items
    [{'feedid': entries.id, 'tagid': tag_ids[tag]} for tag in hashtags2])
AttributeError: 'list' object has no attribute 'id'

我不太确定问题出在哪里，也不太明白“entry_id”部分是怎么回事，所以我把我所有相关的“插入”代码贴在下面。有人能帮我看看哪里出错了吗？注意，我刚刚发现我错误地把最后一个表称为“feedtag_table”，而不是“entrytag_table”。这与我最初的目标不符，我是想把单个源条目与标签关联，而不是源与标签关联。我已经在上面的代码中纠正了这个错误。

feeds = conn.execute('SELECT id, url FROM feeds').fetchall()

def store_feed_items(id, items):
    """ Takes a feed_id and a list of items and stored them in the DB """
    for entry in items:
        conn.execute('SELECT id from entries WHERE short_url=?', (entry.link,))
        s = unicode(entry.summary) 
        test = s.split()
        tinyurl2 = [i for i in test if i.startswith('http://')]
        hashtags2 = [i for i in s.split() if i.startswith('#')]
        content2 = ' '.join(i for i in s.split() if i not in tinyurl2+hashtags2)
        content = unicode(content2)
        tinyurl = unicode(tinyurl2)
        hashtags = unicode (hashtags2)
        date = strftime("%Y-%m-%d %H:%M:%S",entry.updated_parsed)

        conn.execute(RSSEntries.insert(), {'feed_id': id, 'short_url': tinyurl,
            'content': content, 'hashtags': hashtags, 'date': date})    

        tags = tag_table
        tag_id_query = select([tags.c.tagname, tags.c.id], tags.c.tagname.in_(hashtags))
        tag_ids = dict(conn.execute(tag_id_query).fetchall())
        for tag in hashtags:
            if tag not in tag_ids:
                result = conn.execute(tags.insert(), {'tagname': tag})
                tag_ids[tag] = result.last_inserted_ids()[0]

        conn.execute(entrytag_table.insert(),
            [{'feedid': id, 'tagid': tag_ids[tag]} for tag in hashtags2])

sqlalchemy 数据插入 sqlite 数据库设计多对多关系标签管理 RSS解析数据库表结构

使用Python、SQLAlchemy和Sqlite设置/插入多对多数据库

1 个回答

撰写回答