Python Forum
Help : Inserting records into MySQL workbench
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Help : Inserting records into MySQL workbench
#1
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.
Error:
Traceback (most recent call last): File "/home/darren/Desktop/Downloads/FYPtesting/test.py", line 67, in <module> cursor.execute(sql, (src_IP,src_domain,dst_IP,dst_domain)) File "/home/darren/PycharmProjects/HelloWorld1/venv/lib/python3.6/site-packages/pymysql/cursors.py", line 170, in execute result = self._query(query) File "/home/darren/PycharmProjects/HelloWorld1/venv/lib/python3.6/site-packages/pymysql/cursors.py", line 328, in _query conn.query(q) File "/home/darren/PycharmProjects/HelloWorld1/venv/lib/python3.6/site-packages/pymysql/connections.py", line 517, in query self._affected_rows = self._read_query_result(unbuffered=unbuffered) File "/home/darren/PycharmProjects/HelloWorld1/venv/lib/python3.6/site-packages/pymysql/connections.py", line 732, in _read_query_result result.read() File "/home/darren/PycharmProjects/HelloWorld1/venv/lib/python3.6/site-packages/pymysql/connections.py", line 1075, in read first_packet = self.connection._read_packet() File "/home/darren/PycharmProjects/HelloWorld1/venv/lib/python3.6/site-packages/pymysql/connections.py", line 684, in _read_packet packet.check_error() File "/home/darren/PycharmProjects/HelloWorld1/venv/lib/python3.6/site-packages/pymysql/protocol.py", line 220, in check_error ============================================================ err.raise_mysql_exception(self._data) None File "/home/darren/PycharmProjects/HelloWorld1/venv/lib/python3.6/site-packages/pymysql/err.py", line 109, in raise_mysql_exception ============================================================ MarkMonitor, Inc. raise errorclass(errno, errval) ============================================================ Protocol type : TCP pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''IP(Source)','Registrar(Source)','IP(Destination)','Registrar(Destination)') VAL' at line 1") mysql connection established!
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.
Reply
#2
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 tuple
Links to SQL tutorials http://zetcode.com/db/sqlitepythontutorial/
http://www.devshed.com/c/a/Python/Using-...in-Python/
http://www.tutorialspoint.com/sqlite/sqlite_python.htm
Reply


Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020