Drupal – Import data into nodes and content types
Importing data into Drupal can be a bit complicated because of node revisions. The trick is that the data needs to be inserted into the node and the node revisions tables, and the two need to reference each other. This becomes even more complicated when using custom content types which also need to reference these nodes.
The function insert_node() takes the node title, body, content type (such as ‘page’ or ‘event’), and optionally the date (as a unix timestamp). It uses two global variables for the ids so that you can reference the ‘nid’ and ‘vid’ values for the inserted rows in other functions, as illustrated in the example insert_special_node().
function insert_node($title, $type, $body, $date = '')
{
global $insert_id, $insert_revision_id; //nid and vid
if ($date == '')
{
$sql = 'INSERT INTO node (title, type, uid, created, changed) VALUES (\'' . $title . '\', \'' . $type . '\', 1, UNIX_TIMESTAMP(), UNIX_TIMESTAMP())';
} else {
$sql = 'INSERT INTO node (title, type, uid, created, changed) VALUES (\'' . $title . '\', \'' . $type . '\', 1, ' . $date . ', ' . $date . ')';
}
$result = db_query($sql);
$insert_id = mysql_insert_id();
$sql = 'INSERT INTO node_revisions (nid, title, uid, body, teaser) VALUES (' . $insert_id . ', \'' . $title . '\', 1, \'' . $body . '\', \'' . $body . '\')';
$result = db_query($sql);
$insert_revision_id = mysql_insert_id();
if ($insert_id > 0)
{
$sql = 'UPDATE node SET vid = ' . $insert_revision_id . ' WHERE nid = ' . $insert_id;
$result = db_query($sql);
}
}
//example usage of insert_node with a sample content type called 'special'
function insert_special_node()
{
global $insert_id, $insert_revision_id; //nid and vid
$content_type = 'special';
//use mysql_real_escape_string() to clean user text for database insert
//this is mainly useful when inserting data read from another database query
$title = mysql_real_escape_string('title');
$body = mysql_real_escape_string('body text');
$special_text = mysql_real_escape_string('test');
insert_node($title, $content_type, $body);
$sql = 'INSERT INTO content_type_special (vid, nid, field_special_value) VALUES (' . $insert_revision_id . ', ' . $insert_id . ', \'' . $special_text . '\')';
$result = db_query($sql);
}
Subscribe to:
March 23rd, 2009 at 9:02 am
Hi there
This was really helpful as a starting point for my moving into drupal with my old php/mysql dynamic site.
I recon I need to retrieve the data from my old mysql, and match the with title, type, date and so on.
Then use your script to insert data into the drupal database.
I am quite rusty in this. Do you run these php snippets in php-files you create on your server? Or is there another way?
kind regards
Jens Poder
March 27th, 2009 at 4:36 pm
Yes you would basically write this in a php file that you would run on a server. I’m not sure your setup but the process would be something like this:
Create import script file (eg. import.php)
In that script create your connection to your existing database and pull the data
Loop over that data and insert it into the functions provided above
That’s about all I can tell you based on not knowing any of your setup information.
April 9th, 2010 at 5:43 pm
Here’s a Python implementation of that (will also work with any custom node type)
import MySQLdb
db = MySQLdb.connect(host=”, user=”, passwd=”, db=”)
cursor = db.cursor()
def insert_node(title, type,body, date = “”):
# Drupal required me to have a vid on insert.
cursor.execute(“select max(vid)+1 from node”);
vid = int(cursor.fetchone()[0])
# If no date value
if date == “”:
sql = “INSERT INTO node (title, type, uid, created, changed, vid, promote, comment) VALUES (‘”+title+”‘,’”+str(type)+”‘,1,UNIX_TIMESTAMP(), UNIX_TIMESTAMP(), “+str(vid)+”, 1, 2)”
else:
sql = “INSERT INTO node (title, type, uid, created, changed, vid, promote, comment) VALUES (‘”+title+”‘,’”+str(type)+”‘,1,”+date+”,”+date+”, “+str(vid)+”, 1, 2)”
# Insert the node
cursor.execute(sql)
insert_id = int(cursor.lastrowid)
db.commit()
# Insert revision node.
sql = “INSERT INTO node_revisions (nid, title, uid, body) VALUES (‘”+str(insert_id)+”‘, ‘”+title+”‘, 1, ‘”+body+”‘)”
cursor.execute(sql)
revision_insert_id = int(cursor.lastrowid)
db.commit()
# Not sure if this code has any meaning
if insert_id > 0:
sql = “UPDATE node SET vid = “+str(revision_insert_id)+” WHERE nid = “+str(insert_id)
cursor.execute(sql)
db.commit()
return {‘insert_id’:insert_id, ‘revision_id’:revision_insert_id}
def insert_special_node(content_type, data, title = “”, body = “”, date = “”):
# Insert node data
data_ids = insert_node(title,content_type, body ,date)
# Get any custom columns and values
cols = []
values = []
for c in data.keys():
cols.append(c)
v = “‘” + data[c].replace(“‘”, “\\’”) + “‘”
values.append(v)
# Create a string list out of them
col_str = “,”.join(cols)
val_str = “,”.join(values)
# Insert custom content data.
sql = “INSERT INTO content_type_”+content_type+” (vid, nid,”+col_str+”) VALUES(“+str(data_ids["revision_id"])+”,”+str(data_ids["insert_id"])+” ,”+val_str+”)”
cursor.execute(sql)
db.commit()
April 9th, 2010 at 6:00 pm
[...] into Drupal and it involve using custom CCK types. I found some great PHP code to do this here: http://www.rtraction.com/blog/devit/drupal-import-data-into-nodes-and-content-types.html but I wanted to do it using Python, so here is my translation of that [...]