0

So I have been tasked with migrating content into Drupal 8. I wrote python to create SQL statements to insert nodes into the the Drupal database. I had to figure out how Drupal stores content for each content type and then write out the SQL.

Here's what I've done for a News Item content type. What would you do differently to insert content? What tools would you use to avoid having to manually extract how Content Types are stored in the database and then write lengthy SQL manually like I did?

import mysql.connector
import csv

class News_Item:
    # node and node_rev use nid vid. Other tables use entity_id and revision_id
    def node(self, type):
        file.write("INSERT INTO csr.node \n")
        file.write("SELECT `nid` + 5000, `vid` + 5000 , `type`, `uuid`, `langcode`  \n")
        file.write("FROM 8csr.node WHERE 8csr.node.type LIKE '" + type +"'")
        file.write("AND 8csr.node.nid < 5000;\n\n")

    def node_rev(self, type):
        file.write("INSERT INTO csr.node_revision \n")
        file.write("SELECT `8csr`.`node`.`nid` + 5000, `8csr`.`node`.`vid` + 5000 , `8csr`.`node`.`langcode`, `revision_uid`, `revision_timestamp`, `revision_log`  \n")
        file.write("FROM 8csr.node_revision \n")
        file.write("INNER JOIN `8csr`.node ")
        file.write("ON `8csr`.node_revision.vid = `8csr`.node.vid AND `8csr`.node.type LIKE '" + type + "'\n")
        file.write("AND 8csr.node.nid < 5000;\n\n")

    # Field data and field revision differ only in that one of them has a type field
    def field_data(self, type):
        file.write("INSERT INTO csr.")
        file.write("`node_field_data`")
        file.write("\n SELECT `node_field_data`.`nid` + 5000, `node_field_data`.`vid` + 5000 , `node_field_data`.`type`, `node_field_data`.`langcode`, `status`, `title`, `node_field_data`.`uid`, `created`, `changed`, `promote`, `sticky`, `revision_translation_affected`, `default_langcode` \n")
        file.write("FROM `8csr`.`"+ "node_field_data" +"` \n")
        file.write("INNER JOIN `8csr`.node \n")
        file.write("ON `8csr`.`"+ "node_field_data" +"`.vid = `8csr`.node.vid\n")
        file.write("AND `8csr`.node.type LIKE '" + type +"' \n")
        file.write("AND 8csr.node.nid < 5000;\n\n")

        file.write("UPDATE csr.node_field_data SET uid = 1; \n")

        file.write("INSERT INTO csr.")
        file.write("`node_field_revision`")
        file.write("\n SELECT `node_field_revision`.`nid` + 5000, `node_field_revision`.`vid` + 5000 , `node_field_revision`.`langcode`, `status`, `title`, `node_field_revision`.`uid`, `created`, `changed`, `promote`, `sticky`, `revision_translation_affected`, `default_langcode` \n")
        file.write("FROM `8csr`.`node_field_revision` \n")
        file.write("INNER JOIN `8csr`.node \n")
        file.write("ON `8csr`.`node_field_revision`.vid = `8csr`.node.vid\n")
        file.write("AND `8csr`.node.type LIKE '" + type +"' \n")
        file.write("AND 8csr.node.nid < 5000;\n\n")

        file.write("UPDATE csr.node_field_revision SET uid = 1; \n")

    def all_fields(self, type, fields, custom_data):
        for x in range(0, len(fields)):
            table = "node__" + fields[x]
            file.write("INSERT INTO csr.node__" + fields[x] +"\n")
            file.write(" (`bundle`, `deleted`, `entity_id`, `revision_id`, `langcode`, `delta`, " + custom_data[x] +")\n")
            file.write("SELECT `bundle`, `deleted`, `entity_id` + 5000, `revision_id` + 5000, `node`.`langcode`, `delta`, " + custom_data[x] +"\n")
            file.write("FROM `8csr`.`"+ table +"` \n")
            file.write("INNER JOIN `8csr`.node \n")
            file.write("ON `8csr`.`"+ table +"`.revision_id = `8csr`.node.vid\n")
            file.write("AND `8csr`.node.type LIKE '" + type +"' \n")
            file.write("AND 8csr.node.nid < 5000;\n\n")

    def all_fields_rev(self, type, fields, custom_data):
        for x in range(0, len(fields)):
            table = "node_revision__" + fields[x]
            file.write("INSERT INTO csr." + table +"\n")
            file.write(" (`bundle`, `deleted`, `entity_id`, `revision_id`, `langcode`, `delta`, " + custom_data[x] +")\n")
            file.write("SELECT `bundle`, `deleted`, `entity_id` + 5000, `revision_id` + 5000, `node`.`langcode`, `delta`, " + custom_data[x] +"\n")
            file.write("FROM `8csr`.`"+ table +"` \n")
            file.write("INNER JOIN `8csr`.node \n")
            file.write("ON `8csr`.`"+ table +"`.revision_id = `8csr`.node.vid\n")
            file.write("AND `8csr`.node.type LIKE '" + type +"' \n")
            file.write("AND 8csr.node.nid < 5000;\n\n")

    def files_added(self, type):
        file.write("INSERT INTO `csr`.`file_usage` \n")
        file.write("SELECT `fid` + 5000, `module`, `8csr`.`file_usage`.`type`, `id` + 5000, `count` \n")
        file.write("FROM `8csr`.`file_usage` \n")
        file.write("INNER JOIN `8csr`.node \n")
        file.write("ON `8csr`.`file_usage`.id = `8csr`.node.nid\n")
        file.write("AND `8csr`.node.type LIKE '" + type +"' \n")
        file.write("AND 8csr.node.nid < 5000;\n\n")

        file.write("INSERT INTO `csr`.`file_managed` \n")
        file.write("SELECT `fid` + 5000, `8csr`.`file_managed`.`uuid`, `8csr`.`file_managed`.`langcode`, `uid`, `filename`, `uri`, `filemime`, `filesize`, `status`, `created`, `changed`")
        file.write("FROM `8csr`.`file_managed` ;\n\n")
        file.write("UPDATE csr.file_managed SET uid = 1 ; \n ")
        file.write("UPDATE csr.node__field_publication SET field_publication_target_id = field_publication_target_id + 5000 WHERE `bundle` LIKE 'news_item'; \n")
        file.write("UPDATE csr.node__field_photo SET field_photo_target_id = field_photo_target_id + 5000 WHERE `bundle` LIKE 'news_item'; \n")

fields = ["body", "field_publication", "field_photo"]
custom_data = [
"`body_value`, `body_summary`, `body_format` ",
"`field_publication_target_id`,  `field_publication_display`, `field_publication_description` ",
"`field_photo_target_id`, `field_photo_alt`, `field_photo_title`, `field_photo_width`, `field_photo_height` "]

type = "news_item"

file = open('news_items.sql', 'w+')
file.write("begin;\n")

e = News_Item()
e.node(type)
e.node_rev(type)
e.field_data(type)
e.all_fields(type, fields, custom_data)
e.all_fields_rev(type, fields, custom_data)
e.files_added(type)
file.write("commit;\n")

What is the drupal way to import content from another source of raw data like an external database or CSV file? Is there a ORM tool like Hibernate that can be used for drupal development?

  • 1
    Ditch all of this, you should use the Migrate module in core to construct migration scripts. – Kevin Dec 8 '17 at 19:24
2

There are couple of things you can do in this scenario.

  1. Create a custom module and use the core migrate framework to perform your migration. You may also require the migrate_plus module for this. This project provides extensions to core migration framework functionality.

  2. If you are just migrating CSV data then you can use feeds or Migrate Source CSV module as long as you have the content types properly configured in your Drupal instance.

It is recommended to use tools provided by and created for Drupal for tasks like this.

2

As others have said use the Drupal 8 Migration API to perform this task.

A good overview of the API and its usage and examples can be found at: https://www.drupal.org/docs/8/api/migrate-api/migrate-api-overview

-2

For straight forward content type mapping from a source file I have been relying on Feeds. https://www.drupal.org/project/feeds

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.