Does CREATE TABLE IF NOT EXISTS work in MySQLdb? Syntax?

Question

I've created a table "table2" and get warnings (table already exists) when I run my code. I want to create the table only if it doesn't exist. Some research MySQL syntax websiteturns up the following in MySQL : CREATE TABLE IF NOT EXISTS

My code:

cursor.execute('CREATE TABLE IF NOT EXISTS (2 INT)`table2`')

provides this warning:

_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 '(2 INT)`table2`' at line 1")

I have database version Database version : 5.1.54-1ubuntu4 Thanks-Tom

Answer 1

mysql syntax is

CREATE TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

using the following...

cursor.execute('CREATE TABLE IF NOT EXISTS `table2` (`something` int(2))')

result:

__main__:1: Warning: Table 'table2' already exists

Answer 2

There are a couple of problems with the syntax of your CREATE TABLE command.

1. You have the table name after the column definitions. It should be placed before them, like this:

   CREATE TABLE IF NOT EXISTS table2 (<column definitions>);

2. Secondly, you are using 2 as a column name, but I'm not sure that 2 is even a valid column name. If it is, it should be quoted to distinguish it from an plain integer.

You can read more about the CREATE TABLE syntax at the MySQL documentation.