Store unknown text file data into MySQL using Python

Question

To go more in depth into a question like this:

How to store text file into MySQL database using python

I have data that I am uncertain what it will be until my Python script reads the file. So the error I am getting is:

MySQLdb._exceptions.ProgrammingError: not enough arguments for format string

Here's my code:

file = open('./ftp_files/ReadMe.txt', 'r')
file_content = file.read()
for line in file_content:
fields = line.split('|')
file.close()

query = "INSERT INTO ICLN_LOADER_JOB_LOG (JOB_ID, LOADER_HOST_NAME, MYSQL_HOST_NAME) VALUES ('%s', '%s', '%s')"

cursor.execute(query, (file_content,))

I understand that normally you would have the data values after %s... but what if I don't know what those values are, how can I set it up so that Python reads what the file content is and fills in the gaps?

Malcador · Accepted Answer · 2020-02-03 22:25:40Z

0

You can insert any values into a string using .format() method. But your problem should be that you are closing your file before you use file_content.

answered Feb 3, 2020 at 22:25

Malcador

739 bronze badges

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

Comments

nbk · Accepted Answer · 2020-02-03 22:30:20Z

0

That depends how you defined the table ICLN_LOADER_JOB_LOG

IF LOADER_HOST_NAME and MYSQL_HOST_NAME alloow NULL as Value. you can do this

query = """INSERT INTO ICLN_LOADER_JOB_LOG (JOB_ID, LOADER_HOST_NAME, MYSQL_HOST_NAME) 
         VALUES ('%s', NULL, NULL)"""

cursor.execute(query, (file_content,))

Also you could use instead of NULL other values that mark , the columns had no value

Like

query = """INSERT INTO ICLN_LOADER_JOB_LOG (JOB_ID, LOADER_HOST_NAME, MYSQL_HOST_NAME) 
         VALUES ('%s', '%s', '%s')"""
lhn = '';
myhn = '';
cursor.execute(query, (file_content,lhn,mysn))

edited Feb 3, 2020 at 22:30

answered Feb 3, 2020 at 22:24

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Collectives™ on Stack Overflow

Store unknown text file data into MySQL using Python

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related