CODEWITHSUNDEEP
pythonlistcsv
Mike
Mike

Reputation: 4405

Change Series of Numbers to List, python

I have been given a series of numbers I would like to take and put into a python list or tuple as comma separated numbers. I've been digging around on stacked, but maybe I can't find the correct way to phrase my question because I keep finding ways to conver tuples to lists and vice versa.

This is what I have. I received an email with a list series of numbers like so (but several hundred more):

417985
417986
417987
417988
417989
417990

I will use the numbers to query from a database, so:

NUMBER in (417985,417986,417987,417988,417989,417990)

Is there any way in python to convert that series of numbers so that I canget the comma separated values in the query? FYI, I do a lot of python, so that's where I am comforable, however any other method I would be interested to hear.

Upvotes: 1

Views: 741

Answers (2)

Martijn Pieters
Martijn Pieters

Reputation: 1121486

If you are going to use these numbers in a SQL query, you need to generate SQL parameters rather than build a query string.

You'd read your lines and turn this into a sequence (a list will do) of integers:

numbers = [int(line) for line in email_lines]

You then generate a query with enough placeholders for each of those values:

statement = "SELECT * FROM table WHERE NUMBER IN ({0})".format(
    ', '.join(['?' * len(numbers)]))

where I assume that the database connector you are using uses ? as the SQL parameter placeholder; it could also be %s.

Now you can ask the database for matching rows:

cursor.execute(statement, numbers)

By using SQL parameters you get a few things for free:

  • Proper quoting and escaping of the values. Less important here because you are already converting everything to integers, but it is a good practice to not escape and quote values yourself; you will almost certainly miss something.
  • Reuse of cached query plans; the database only has to parse the statement query once and can reuse the result for subsequent queries.

If you wanted to copy and paste those numbers, type this:

email_lines = """\
<paste your lines with numbers>
""".splitlines()

then apply the first line from my answer to the result.

Upvotes: 2

user2489252
user2489252

Reputation:

Is this maybe what you want?

raw = """417985
417986
417987
417988
417989
417990"""

numbers = [int(n) for n in raw.split()]

>> numbers_int
[417985, 417986, 417987, 417988, 417989, 417990]

Or if you want to construct a string

numbers_str = [n for n in raw.split()]
>> ",".join(numbers_str)
'417985,417986,417987,417988,417989,417990'

Upvotes: 1

Related Questions