I'm trying to copy some tables from MySQL to PostgreSQL, and it looked
like Table.tometadata() was exactly what I needed.
However, it's not working.
I keep getting a SQL syntax error when trying to create the new table.
--------------
sqlalchemy.exc.ProgrammingError: (ProgrammingError) syntax error at or near "ON"
LINE 5: ...STAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP ON UPDATE ...
^
"\nCREATE TABLE legacy_user (\n\tusr_id SERIAL NOT NULL, \n\tusr_name
VARCHAR(64) DEFAULT '' NOT NULL, \n\tusr_modification_time TIMESTAMP
WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP NOT NULL, \n\tusr_creation_time TIMESTAMP WITHOUT
TIME ZONE DEFAULT '0000-00-00 00:00:00' NOT NULL, \n\tPRIMARY KEY
(usr_id)\n)\n\n" {}
--------------
Here's the possible causes that I can think of:
1) tometadata isn't compiling the SQL according to the new metadata,
but rather the old one
2) there's a bug in the PostgreSQL compiler that emits bad syntax
3) I'm totally misunderstanding what is supposed to happen here
(perhaps the most likely)
so my question is:
Is this possible? Advisable?
It looks like there might be a way to hook into the DDL creation
process, though I don't quite understand how...maybe that's a way
forward?
Otherwise I may have to just dump the MySQL tables, munge them with a
script, and load them into pgsql, which I'd rather not do, if
possible.
sample code below
##############
import sys
from collections import namedtuple
from sqlalchemy import create_engine, MetaData, Table
from sqlalchemy.orm import sessionmaker
from sqlalchemy.schema import CreateTable
from sqlalchemy.sql import select
DBInfo = namedtuple('DBInfo', ['meta', 'engine', 'conn'])
def makeDBInfo(dbUri, echo=False, reflect=False):
"""
"""
engine = create_engine(dbUri, echo=echo)
meta = MetaData(engine)
if reflect:
meta.reflect(engine)
connection = engine.connect()
return DBInfo(meta=meta, engine=engine, conn=connection)
if __name__ == "__main__":
from floworkdb import model
import transaction
sourceDBUri = 'mysql://olduser:oldpasswd@localhost/olddb'
source = makeDBInfo(sourceDBUri, reflect=True)
print 'source:'
print source.meta
print source.engine
print source.conn
print
destDBUri = 'postgres://newuser:newpasswd6@localhost/newdb'
dest = makeDBInfo(destDBUri, echo=True)
print 'dest:'
print dest.meta
print dest.engine
print dest.conn
print
print "Creating 'user' table in dest DB"
sourceTable = source.meta.tables['user']
destTable = sourceTable.tometadata(dest.meta)
destTable.create() # <-- this fails!
# destTable.create(dest.engine) # <-- this fails too!
# yes we're really connected to the new metadata
print destTable.metadata
# the same sql gets generated for both MySQL and Postgres!
print CreateTable(destTable)
print CreateTable(sourceTable)
# try to copy the table columns manually?
# same error...
#~ destTable = Table('legacy_user', dest.meta)
#~ print destTable.c
#~ for c in sourceTable.c:
#~ print c
#~ destTable.append_column(c.copy())
#~ print
#~ print destTable.c
#~ print destTable.metadata
#~ sourceTable.metadata.create_all(dest.engine)
sys.exit()
sys.exit('everything below here is ignored')
"""
here's the SQL it tries to send to Postgres:
CREATE TABLE "user" (
usr_id INTEGER NOT NULL,
usr_name VARCHAR(64) DEFAULT '' NOT NULL,
usr_modification_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP NOT NULL,
usr_creation_time TIMESTAMP DEFAULT '0000-00-00 00:00:00' NOT NULL,
PRIMARY KEY (usr_id)
)
"""
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
