Writing from a text file to SQL using Python

Question

Hey I was just wondering if anyone would be able to help with writing the contents of a series of text files (e.g. activities.txt, sports.txt) (each has has a number, a tab, and a value (e.g. 90 Badminton) in each row). I have already initialised the database connection and have the table in SQL made ready to go.

Any help would be greatly appreciated!

Thanks

Give examples of the input and output you want. Not just useful for us in helping, that's useful to you in designing a solution yourself. — Kenan Banks
– Kenan Banks, Commented May 27, 2011 at 0:59
I need to input the rows from the text files and output each piece of data to a specific column in the SQL table. For example, the table headers would be PersonId | Category | Type and the values would be 1 | Sports | Soccer. I'd get this data from the text files e.g. sports.txt "1 Soccer 2 Badminton 3 Football" etc — Dylan
– Dylan, Commented May 27, 2011 at 1:06

Bryan · Accepted Answer · 2011-05-27 01:44:15Z

1

If I understand this correctly then the following snippet should get you started. I'm going to assume you already have access to a cursor...

category = "Sports"
with open("sports.txt", "r") as sports:
    lines = sports.readlines()

for line in lines:
    # Split the line on whitespace
    data = line.split()
    number = data[0]
    value = data[1]

    # Put this through to SQL using an INSERT statement...
    cursor.execute("""INSERT INTO tablename (person_id, category, type)
                   VALUES(%s, %s, %s)""", (number, category, value))

edited May 27, 2011 at 1:44

answered May 27, 2011 at 1:30

Bryan

6,7192 gold badges32 silver badges16 bronze badges

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

3 Comments

geofftnz Over a year ago

You'll want to make sure you handle the case where number, category and value have single quotes in them.

Daniel Pryden Over a year ago

I was almost ready to upvote until I saw the unescaped strings going into the SQL. If one of the categories is "Bobby Tables' favorite strings" then you're going to have a problem...

Bryan Over a year ago

The others are correct. I meant to make use of DB-API's parameter substitution. My post now reflects that...

Collectives™ on Stack Overflow

Writing from a text file to SQL using Python

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related