Create MySQLdb database using Python script

Question

I'm having troubles with creating a database and tables. The database needs to be created within a Python script.

#connect method has 4 parameters:
#localhost (where mysql db is located), 
#database user name, 
#account password, 
#database name    
db1 = MS.connect(host="localhost",user="root",passwd="****",db="test")

returns

_mysql_exceptions.OperationalError: (1049, "Unknown database 'test'")

So clearly, the db1 needs to be created first, but how? I've tried CREATE before the connect() statement but get errors.

Once the database is created, how do I create tables? Thanks, Tom

Here is the syntax, this works, at least the first time around. The second time naturally returns that the db already exists. Now to figure out how to use the drop command properly.

   db = MS.connect(host="localhost",user="root",passwd="****")
   db1 = db.cursor()
   db1.execute('CREATE DATABASE test1')

So this works great the first time through. The second time through provides a warning "db already exists". How to deal with this? The following is how I think it should work, but doesn't. OR should it be an if statement, looking for if it already exists, do not populate?

import warnings
warnings.filterwarnings("ignore", "test1")

Answer 1

Use CREATE DATABASE to create the database:

db1 = MS.connect(host="localhost",user="root",passwd="****")
cursor = db1.cursor()
sql = 'CREATE DATABASE mydata'
cursor.execute(sql)

Use CREATE TABLE to create the table:

sql = '''CREATE TABLE foo (
       bar VARCHAR(50) DEFAULT NULL
       ) ENGINE=MyISAM DEFAULT CHARSET=latin1
       '''
cursor.execute(sql)

There are a lot of options when creating a table. If you are not sure what the right SQL should be, it may help to use a graphical tool like phpmyadmin to create a table, and then use SHOW CREATE TABLE to discover what SQL is needed to create it:

mysql> show create table foo \G
*************************** 1. row ***************************
       Table: foo
Create Table: CREATE TABLE `foo` (
  `bar` varchar(50) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

phpmyadmin can also show you what SQL it used to perform all sorts of operations. This can be a convenient way to learn some basic SQL.

Once you've experimented with this, then you can write the SQL in Python.