Thought I would try my hand at some SQL programming with Python, I was stuck using a Windows machine(BLAH) I wanted to setup a MySQL database and 1 table for testing. I am on a Windows machine so I installed WAMP which is a Windows Apache Mysql Php server. You can get the installer for Windows here:
http://www.wampserver.com/en/
You can get the python library / module here:
Mysql Python
Using PhpMyAdmin I setup a database and added the table.
As you can see I created a function that added data into my new database. I pass it 4 parameters, id-name-dept-salary, when you execute this script the database gets populated with the correct data. Pretty cool!!
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
#!/usr/bin/python from mysql import connector import optparse parser = optparse.OptionParser() parser.add_option('-i', action="store", help="Enter Job ID#", type="int") parser.add_option('-n', action="store", help="Enter Employee Name", type="string") parser.add_option('-d', action="store", help="Enter Department", type="string") parser.add_option('-s', action="store", help="Enter Salary", type="int") options, args = parser.parse_args() def databaseAdd(id,name,dept,salary): con = connector.Connect(user='jason', password='password', database='jason', host='localhost') c = con.cursor() table_name = "employee" insert = "INSERT INTO " + table_name + " (id,name,dept,salary) \ VALUES (%s, %s, %s, %s)" data_value = (id,name,dept,salary) c.execute(insert, data_value) con.commit() c.close() con.close() databaseAdd(options.i ,options.n,options.d,options.s) |
Here is how you can see the help and call the command properly:
Here is what you can see when performing a select * from the table.
Here is the awesome IDE pyCharm and the results from running the code.
Here is an example of how to query that database from the table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
from mysql import connector import sys import optparse parser = optparse.OptionParser() parser.add_option('--id', action="store", help="Enter ID number", type="int") options, args = parser.parse_args() try: cnx = connector.Connect(user='jason', password='password', database='jason', host='localhost') except connector.Error as err: print "Connection to Database Failed" print err sys.exit(1) cursor = cnx.cursor() query = "SELECT * from employee WHERE id='%s'" % options.id cursor.execute(query) for row in cursor: print row |
Here is the output of the fetch command using ID as a parameter:
Took me a while to figure out the syntax, so hopefully this helps someone.
Jason
I don’t see where you are sanitizing any of the arguments before using them in building the SQL statements. Being that it isn’t a Web application this is probably OK. If you try to do this in a Web application then you would need to apply some type of sanitation to the user’s form input before using it.