How can I create and drop the database using Python code?

Question

I'm very new to Python. How can we drop and create the database in Python?

I'm using MySql and Windows server 2003.

Note: I have all the queries, Drop, create and insert records everything in a input.sql

So I want to import input.sql.

Can anyone help me?

Burhan Khalid · Accepted Answer · 2012-08-26 11:00:23Z

2

If you have access to the mysql client, then this is trivial:

mysql -u username -p database < input.sql

This will prompt you for a password; if you want to supply the password inline:

mysql -u username -ppassword database < input.sql

Run any of the above from Python using the subprocess module.

The not-so-easy way would be to read each line of the file - do some logic to grab entire SQL statements - and then execute it against the database "manually", but this would just replicate what the mysql command is doing:

import MySQLdb
db = MySQLdb.connect(username="foo",passwd="secret",db="mydb")
c = db.cursor()

with open('input.sql') as f:
   for line in f:
      proper_sql = some_magic_function(line)
      c.execute(proper_sql)
      db.commit()

edited Aug 26, 2012 at 11:00

answered Aug 26, 2012 at 10:19

Burhan Khalid

175k20 gold badges254 silver badges291 bronze badges

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

1 Comment

Jonas Schäfer Over a year ago

Plus, the not-so-easy way would not work the same, as the default mysql client will detect open brackets etc. and thus allow statements spanning over multiple lines.

Collectives™ on Stack Overflow

How can I create and drop the database using Python code?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related