CODEWITHSUNDEEP
pythonsqlmysql-workbenchmysql-python
frazman
frazman

Reputation: 33223

sql dump file.. load to mysql

Ok first, please assume that this is the first time I will be writing a single alphabet of code in SQL.. so please please please... it would be very kind if you can explain me each step. :) I am using mac for this development. So in the desktop, I have a huge (1.5GB) dumpfile of a database. I have mysqlworkbench (yes I have never worked in that either :( ) And I have installed MySQLdb. So, basically what I want to do is use the data in the database from python. So I have python.. mysqldb to connect to db.. huge db and mysqlworkbench.. Now I assume, that this dumpfile is useless as its not in form of database.. so how do I "mount" this dump to a database..(mysqlworkbench) After that how do I query from python.. (like the port number.. username , password!! ? ) It would be of huge help if anyone can help me.. I have been stuck in this since morning.. :(

Upvotes: 4

Views: 3219

Answers (1)

Portablejim
Portablejim

Reputation: 844

In order to use the data in the file, it needs to be imported into the database. (Think of the file as a recipe for MySQL to make a database. Now you need to tell MySQL to make the database from the recipe).

You can import the database using command line, a python script or even MySQL workbench. However, due to the size of the file, using MySQL workbench could be a problem (Note: I am not familiar with MySQL workbench).

To import the database with the command line use the command:

mysql -u <username> -p -h localhost king_tracking < <filename>

To actually use the database with python, there are several step by step tutorials available, easily accessible from a google search. A basic script is (from here)

import MySQLdb

conn = MySQLdb.connect (host = "localhost",
                       user = "testuser",
                       passwd = "testpass",
                       db = "test")
cursor = conn.cursor ()
cursor.execute ("SELECT VERSION()")
row = cursor.fetchone ()
print "server version:", row[0]
cursor.close ()
conn.close ()

This just shows the version of MySQL, however if

"SELECT VERSION()"

is replaced by your own query, like

"SELECT * FROM <tablename> LIMIT 0,30"

you can execute your own queries.

In order to understand the tutorials on interfacing python and MySQL, you should be familiar with both separately. A good SQL tutorial is the one at W3schools.

The problem with creating a table is separate from the original question, but may need to be addressed before trying to import the file into the database.

Upvotes: 4

Related Questions