Cannot properly execute a sql script with sqlite3 Python module

Question

I'm trying to execute SQL scripts contained in a .sql file with the sqlite3 Python module, in order to migrate the database from a Drupal site to a Wordpress one.

So I open and read the .sql thing with this little script:

def executeSQLScriptsFromFile(filename):
    fd = open(filename, 'r')
    sqlFile = fd.read()
    fd.close()

    sqlCommands = sqlFile.split(';')

    for command in sqlCommands:
        print " === BEGIN \n" + command + "\nEND === \n"
        try:
            c.execute(command)
        except OperationalError, msg:
            print "Command skipped: ", msg

And when I execute this code with SQL commands I write, everything works fine: CREATE TABLE, SELECT, DELETE...

But when I try with the SQL script that I downloaded from a website database I work on:

CREATE TABLE IF NOT EXISTS `node` (
  `nid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `vid` int(10) unsigned NOT NULL DEFAULT '0',
  `type` varchar(32) NOT NULL DEFAULT '', 
  `language` varchar(12) NOT NULL DEFAULT '', 
  `title` varchar(255) NOT NULL DEFAULT '', 
  `uid` int(11) NOT NULL DEFAULT '0',
  `status` int(11) NOT NULL DEFAULT '1',
  `created` int(11) NOT NULL DEFAULT '0',
  `changed` int(11) NOT NULL DEFAULT '0',
  `comment` int(11) NOT NULL DEFAULT '0',
  `promote` int(11) NOT NULL DEFAULT '0',
  `moderate` int(11) NOT NULL DEFAULT '0',
  `sticky` int(11) NOT NULL DEFAULT '0',
  `tnid` int(10) unsigned NOT NULL DEFAULT '0',
  `translate` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`nid`),
  UNIQUE KEY `vid` (`vid`),
  KEY `node_changed` (`changed`),
  KEY `node_created` (`created`),
  KEY `node_moderate` (`moderate`),
  KEY `node_promote_status` (`promote`,`status`),
  KEY `node_status_type` (`status`,`type`,`nid`),
  KEY `node_title_type` (`title`,`type`(4)),
  KEY `node_type` (`type`(4)),
  KEY `uid` (`uid`),
  KEY `tnid` (`tnid`),
  KEY `translate` (`translate`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=663 ;

I got this exception:

Command skipped:  near "unsigned": syntax error

As you can see, I am not fluent in SQL :p

Does someone know what may be the problem ?

are you using sqlite3 or mysql? why is this tagged with both? basically that sql is likely targeting a different sql engine than the one you are using ... — Joran Beasley
– Joran Beasley, Commented Jul 17, 2015 at 15:43

Joran Beasley · Accepted Answer · 2015-07-17 16:30:25Z

1

sqlite3 does not support "unsigned" and it does not support AUTO_INCREMENT. there are probably some other directive that sqlite3 does not support ... I think that you got that file from someone who was targeting postgres or mysql... to fix it remove all directives that are not supported by sqlite3, or use the same database engine that file targets

as an aside you can do

 c.executescript(open("my_sqcript.sql").read())

here is the schema that will work for sqlite3

CREATE TABLE IF NOT EXISTS `node` (
  `nid` int(10) NOT NULL,
  `vid` int(10) NOT NULL DEFAULT '0',
  `type` varchar(32) NOT NULL DEFAULT '', 
  `language` varchar(12) NOT NULL DEFAULT '', 
  `title` varchar(255) NOT NULL DEFAULT '', 
  `uid` int(11) NOT NULL DEFAULT '0',
  `status` int(11) NOT NULL DEFAULT '1',
  `created` int(11) NOT NULL DEFAULT '0',
  `changed` int(11) NOT NULL DEFAULT '0',
  `comment` int(11) NOT NULL DEFAULT '0',
  `promote` int(11) NOT NULL DEFAULT '0',
  `moderate` int(11) NOT NULL DEFAULT '0',
  `sticky` int(11) NOT NULL DEFAULT '0',
  `tnid` int(10)  NOT NULL DEFAULT '0',
  `translate` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`nid`),
  UNIQUE(`vid`)

)   ;

here is the table as defined by sqlalchemy

class Node(Base):
   __tablename__ = 'node'
   nid     = Column(Integer,primary_key=True)
   vid     = Column(Integer,default=0)
   type    = Column(String,default="")
   language= Column(String,default="")
   title   = Column(String,default="")
   uid     = Column(Integer,default=0)
   status  = Column(Integer,default=1)
   created = Column(Integer,default=0)
   changed = Column(Integer,default=0)
   comment = Column(Integer,default=0)
   promote = Column(Integer,default=0)
   moderate= Column(Integer,default=0) 
   sticky  = Column(Integer,default=0)
   tnid    = Column(Integer,default=0)
   translate= Column(Integer,default=0)

edited Jul 17, 2015 at 16:30

answered Jul 17, 2015 at 15:47

Joran Beasley

114k13 gold badges167 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kh4L Over a year ago

So, what would be the best way and the simplest way to work with MySQL scripts in Python ?

Joran Beasley Over a year ago

convert it to an ORM like sqlalchemy, then it doesn't care about the backend engine, and in theory you can change it easily. mysqldb i think is what every one uses to talk to mysql databases ... if you want to take raw sql written for mysql lots of it will probably break if you are just trying to use it with sqlite3

Collectives™ on Stack Overflow

Cannot properly execute a sql script with sqlite3 Python module

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related