[Tutor] Creating MySQL table

[Bernard Lebel]

See [Bernard]

On 7/18/05, Don Parris <gnumathetes at gmail.com> wrote:
> On 7/18/05, Bernard Lebel <3dbernard at gmail.com> wrote:
> > Hello,
> >
> > How do I create a MySQL table in Python?
> >
> > Here is what I'm trying:
> >
> >
> > import MySQLdb as sql
> >
> > def connect2db():
> >       return sql.connect( blah blah blah )
> >
> >
> > oConnection = connect2db()
> > oCursor = oConnection.cursor()
> >
> >
> > sQuery = "CREATE TABLE '3DPipeline'.'TB_MT_NAME' (;\
> >   'ID' INTEGER UNSIGNED CHARACTER SET latin1 COLLATE latin1_swedish_ci
> > NOT NULL AUTO_INCREMENT,
> >   'text' TINYTEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
> >   PRIMARY KEY('ID')
> > )
> > ENGINE = InnoDB"
> >
> >
> Looking above, I'm not sure why there's a "." after 3DPipeline.  Also,
> the ";" should be placed after the last command before the closing
> parenthesis, not at the beginning.  I have no idea about your use of
> the backslash.

[Bernard] The backslash thing is a trick that you can use to lure
Python, it acts like a line break in your code, and allows you to
bypass the indentation.

The dot is to separate the database from the table. Before the dot is
the database.

See later for the semi-colon.

> 
> 
> > When I run that, I get this:
> >
> > Traceback (most recent call last):
> >   File "<stdin>", line 1, in ?
> >   File "D:\Python24\Lib\site-packages\MySQLdb\cursors.py", line 137, in execute
> >     self.errorhandler(self, exc, value)
> >   File "D:\Python24\Lib\site-packages\MySQLdb\connections.py", line
> > 33, in defaulterrorhandler
> >     raise errorclass, errorvalue
> > _mysql_exceptions.ProgrammingError: (1064, "You have an error in your
> > SQL syntax; check the manual that corresponds to your MySQL server
> > version for the right syntax to use near ''3DPipline'.'TB_MT_NAME' (
> > 'ID' INTERGER UNSIGNED CHARACTER SET latin1 COLLATE l' at line 1")
> >
> >
> This error message points to your SQL syntax.  Notice that it wants
> you to check "your MySQL server version for the right syntax to use".
> That should alert you to the fact that your SQL syntax is incorrect.
> 
> HTH,
> Don

[Bernard] Well I kind of figured it was telling it's a syntax error. ;-)

After trial and error for an hour or two, I managed to get it sorted.

First, I noticed that in Python there is no need for a semi-colon
terminator (even for SQL commands).
Also, removing all quotes except the opening and closing one made the code work.

The SQL book I am using uses DOS command line syntax, not Python, so I
had to translate the examples into Python syntax.



Cheers
Bernard