Help : Inserting records into MySQL workbench - Printable Version +- Python Forum (https://python-forum.io) +-- Forum: Python Coding (https://python-forum.io/forum-7.html) +--- Forum: Homework (https://python-forum.io/forum-9.html) +--- Thread: Help : Inserting records into MySQL workbench (/thread-15561.html) |
Help : Inserting records into MySQL workbench - Aresden - Jan-22-2019 Hi all, this is my first time and I am new here. With that being said, I have read the guidelines. First and foremost, this is a project homework related question. I have written a python program that accesses a .csv file (sFlow Data). Next, by using the pywhois package, it whois the ip address at the respective column(s). Finally, 4 things are printed out ; IP(Source), Registrar(Source), IP(Destination), Registrar(Destination). I have managed to print out all 4 of these informations. Detailed description of your problem, including steps to reproduce it I am using the PyMySQL package. I am able to get the connection to MySQL established. However, I am now faced with the problem of inserting those 4 things into MySQL Workbench 8.0. I have searched online(stackoverflow,youtube etc.) for solutions but to no avail. What steps have you tried? What was the outcome? Fundamentally, I am not great in python programming but I will try to explain what I have done. I tried the steps in https://www.youtube.com/watch?v=Dnm9gUtH-9M, but added/removed the '' because I am not inserting a static string, but a data i declared previously. I also read in overflow that parameters are placed in a tuple, so I also tried various ways, adding brackets () etc. Put any of your code in python tags, output in output tags, errors in error tags, etc. in BBCode Also make sure to paste as plain text or remove formatting within these tags Below is a portion of my code. connection = pymysql.connect (host='localhost', user='root', password='password', db='network_db',cursorclass=pymysql.cursors.DictCursor) print("mysql connection established!") try: with connection.cursor() as cursor: # Create a new record sql = "INSERT INTO `network_table` ('IP(Source)','Registrar(Source)','IP(Destination)','Registrar(Destination)') VALUES (%s, %s, %s, %s)" cursor.execute(sql, (src_IP,src_domain,dst_IP,dst_domain)) # connection is not autocommit by default. So you must commit to save # your changes. connection.commit() finally: connection.close() Below is the error message i get. System info, python version, operating system, etc.System/OS :Linux Ubuntu 18.04.1 LTS IDE: PyCharm 2018.3.3 (Community Edition) I appreciate the help. RE: Help : Inserting records into MySQL workbench - woooee - Jan-22-2019 try sql = "INSERT INTO network_table VALUES (?, ?, ?, ?)" ## () makes insert data a tuple cursor.execute(sql, (src_IP,src_domain,dst_IP,dst_domain)) <-- this is a tupleLinks to SQL tutorials http://zetcode.com/db/sqlitepythontutorial/ http://www.devshed.com/c/a/Python/Using-SQLite-in-Python/ http://www.tutorialspoint.com/sqlite/sqlite_python.htm |