mysql - Ruby 和 MySQL : How to handle missing elements while parsing XML file

Question

目前我正在尝试解析大型 xml 文件，这是我的 xml 文件的样子:

<post>
  <row Id="22" PostTypeId="2" ParentId="9" CreationDate="2008-08-01T12:07:19.500" Score="7" Body="&lt;p&gt;The best way that I know of because of leap years and everything is:&lt;/p&gt;&#xD;&#xA;&#xD;&#xA;&lt;pre&gt;&lt;code&gt;DateTime birthDate = new DateTime(2000,3,1);&lt;br&gt;int age = (int)Math.Floor((DateTime.Now - birthDate).TotalDays / 365.25D);&lt;br&gt;&lt;/code&gt;&lt;/pre&gt;&#xD;&#xA;&#xD;&#xA;&lt;p&gt;Hope this helps.&lt;/p&gt;" OwnerUserId="17" LastEditorUserId="17" LastEditorDisplayName="Nick" LastEditDate="2008-08-01T15:26:37.087" LastActivityDate="2008-08-01T15:26:37.087" CommentCount="1" CommunityOwnedDate="2011-08-16T19:40:43.080" />

  <row Id="29" PostTypeId="2" ParentId="13" CreationDate="2008-08-01T12:19:17.417" Score="18" Body="&lt;p&gt;There are no HTTP headers that will report the clients timezone so far although it has been suggested to include it in the HTTP specification.&lt;/p&gt;&#xD;&#xA;&#xD;&#xA;&lt;p&gt;If it was me, I would probably try to fetch the timezone using clientside JavaScript and then submit it to the server using Ajax or something.&lt;/p&gt;" OwnerUserId="19" LastActivityDate="2008-08-01T12:19:17.417" CommentCount="0" />

</post>

此 XML 文件中这两条记录的不同之处在于没有 LastEditDate 元素。我相信因此我收到以下错误:

/ruby/1.9.2/ubuntuamd1/lib/ruby/1.9.1/date/format.rb:1031:in `dup': can't dup NilClass (TypeError)
    from /soft/ruby/1.9.2/ubuntuamd1/lib/ruby/1.9.1/date/format.rb:1031:in `_parse'
    from /soft/ruby/1.9.2/ubuntuamd1/lib/ruby/1.9.1/date.rb:1732:in `parse'
    from load.rb:105:in `on_start_element'
    from load.rb:165:in `parse'

这是引用的代码段:

if element == 'row'
  @post_st.execute(attributes['Id'], attributes['PostTypeId'], attributes['AcceptedAnswerId'], attributes['ParentId'], attributes['Score'], attributes['ViewCount'], 
    attributes['Body'], attributes['OwnerUserId'] == nil ? -1 : attributes['OwnerUserId'], attributes['LastEditorUserId'], attributes['LastEditorDisplayName'], 
    DateTime.parse(attributes['LastEditDate']).to_time.strftime("%F %T"), DateTime.parse(attributes['LastActivityDate']).to_time.strftime("%F %T"), attributes['Title'] == nil ? '' : attributes['Title'], 
    attributes['AnswerCount'] == nil ? 0 : attributes['AnswerCount'], attributes['CommentCount'] == nil ? 0 : attributes['CommentCount'], 
    attributes['FavoriteCount'] == nil ? 0 : attributes['FavoriteCount'], DateTime.parse(attributes['CreationDate']).to_time.strftime("%F %T"))
  post_id = attributes['Id']

此外，我认为这是我寻找 LastEditDate

的行

 DateTime.parse(attributes['LastEditDate']).to_time.strftime("%F %T"), DateTime.parse(attributes['LastActivityDate']).to_time.strftime("%F %T"), attributes['Title'] == nil ? '' : attributes['Title']

我猜由于该元素不存在，我收到了上述错误。我想知道如何处理这种情况，如果元素不存在，则将其设置为默认值。因为当我解析这些记录时，我将它们插入 MySQL 数据库。其表结构如下:

+--------------------------+--------------+------+-----+---------------------+-----------------------------+
| Field                    | Type         | Null | Key | Default             | Extra                       |
+--------------------------+--------------+------+-----+---------------------+-----------------------------+
| id                       | int(11)      | NO   | PRI | NULL                |                             |
| post_type_id             | int(11)      | NO   |     | NULL                |                             |
| accepted_answer_id       | int(11)      | YES  |     | NULL                |                             |
| parent_id                | int(11)      | YES  | MUL | NULL                |                             |
| score                    | int(11)      | YES  |     | NULL                |                             |
| view_count               | int(11)      | YES  |     | NULL                |                             |
| body_text                | text         | YES  |     | NULL                |                             |
| owner_id                 | int(11)      | NO   |     | NULL                |                             |
| last_editor_user_id      | int(11)      | YES  |     | NULL                |                             |
| last_editor_display_name | varchar(40)  | YES  |     | NULL                |                             |
| last_edit_date           | timestamp    | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| last_activity_date       | timestamp    | NO   |     | 0000-00-00 00:00:00 |                             |
| title                    | varchar(256) | NO   |     | NULL                |                             |
| answer_count             | int(11)      | NO   |     | NULL                |                             |
| comment_count            | int(11)      | NO   |     | NULL                |                             |
| favorite_count           | int(11)      | NO   |     | NULL                |                             |
| created                  | timestamp    | NO   |     | 0000-00-00 00:00:00 |                             |
+--------------------------+--------------+------+-----+---------------------+-----------------------------+

我已将 last_edit_date 设置为非空列。

根据提供的答案，我进行了更改，但错误仍然相同:

  def convert_to_mysql_time(date='1973-01-01T01:01:01.000')
    DateTime.parse(date).to_time.strftime("%F %T")
  end

  def on_start_element(element, attributes)
    if element == 'row'
      @post_st.execute(attributes['Id'], attributes['PostTypeId'], attributes['AcceptedAnswerId'], attributes['ParentId'], attributes['Score'], attributes['ViewCount'],
        attributes['Body'], attributes['OwnerUserId'] == nil ? -1 : attributes['OwnerUserId'], attributes['LastEditorUserId'], attributes['LastEditorDisplayName'],
        convert_to_mysql_time(attributes['LastEditDate']), DateTime.parse(attributes['LastActivityDate']).to_time.strftime("%F %T"), attributes['Title'] == nil ? '' : attributes['Title'],
        attributes['AnswerCount'] == nil ? 0 : attributes['AnswerCount'], attributes['CommentCount'] == nil ? 0 : attributes['CommentCount'],
        attributes['FavoriteCount'] == nil ? 0 : attributes['FavoriteCount'], DateTime.parse(attributes['CreationDate']).to_time.strftime("%F %T"))
      post_id = attributes['Id']

错误如下:

/ruby/1.9.2/ubuntuamd1/lib/ruby/1.9.1/date/format.rb:1031:in `dup': can't dup NilClass (TypeError)
    from /soft/ruby/1.9.2/ubuntuamd1/lib/ruby/1.9.1/date/format.rb:1031:in `_parse'
    from /soft/ruby/1.9.2/ubuntuamd1/lib/ruby/1.9.1/date.rb:1732:in `parse'
    from load.rb:102:in `convert_to_mysql_time'
    from load.rb:109:in `on_start_element'
    from load.rb:169:in `parse'
    from load.rb:169:in `<main>'

Answer 1

我会编写一个将字符串日期转换为 MySQL 日期的方法，并在向该方法提供 nil 时为其提供默认值，例如:

def convert_to_my_sql_date(date)
    date = '1973-01-01T01:01:01.000' if (date.empty? rescue true) #was added since empty string gets supplied as an argument, and the rescue to make arguments that do not respond to empty? take a default date
    DateTime.parse(date).to_time.strftime("%F %T")
end

因此，当日期为零时，它使用默认值，那么您现在可以在方法中使用如下内容:

convert_to_my_sql_date(attributes['LastEditDate'])