Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

I have a project that i need to compile number of cities in each state and make an insert statement for mysql database. I think the easiest way to do it is via python but since i m a complete noob i would like to reach out all the python gurus here. Here is what the input looks like. Example below is for Florida.

cities = ['Boca Raton', 'Boynton Beach', 'Bradenton', 'Cape Coral', 'Deltona']

and this what the output should be.

INSERT INTO `oc_locations` (`idLocation`, `name`, `idLocationParent`, `friendlyName`) VALUES
(1, 'Florida', 0, 'Florida'),
(2, 'Boca Raton', 1, 'Boca Raton'),
(3, 'Boynton Beach', 1, 'Boynton Beach'),
(4, 'Bradenton', 1, 'Bradenton'),
(5, 'Cape Coral', 1, 'Cape Coral'),
(6, 'Deltona', 1, 'Deltona'),

If you look at carefully the "idLocationParent" for "Florida" value is "0" so which means it is a top level value. This will be done for 50 states so ability to plug the state name into the mysql statement would be icing on the cake if there is a easy way to do it. Also alphabetical order and auto increment for the idLocation would be great.

Here is an example of what i m trying to achieve concatenation is the part i need to figure out.

for city in cities: print (1, 'city', 0, 'city'), city

Here is my solution

      cities = ['Naples', 'Ocala', 'Odesa', 'Oldsmar', 'Orlando', 'Pembroke Pines', 'Pompano beach', 'Port St lucie',
'Sarasota', 'St. Petersburg', 'Tallahasee', 'Tampa', 'Venice']

cities.sort() #For alphebetical sorting

for i in range(len(cities)):
    print '(' + str(i) +', ' + cities[i] + ', 1, ' + cities[i] + ')'
share|improve this question
I don't understand your output. How does it reflect the input? Edit: never mind; the input changed. – Steve Tjoa Dec 24 '10 at 4:03
What do you have so far, and how doesn't it work? – Ignacio Vazquez-Abrams Dec 24 '10 at 4:03

2 Answers

up vote 1 down vote accepted

If you want idLocation to auto increment make it a primary key of the table. I would also look into foreign keys instead of putting the states and cities into the same table.

Here is some code that will get you close to what you're asking for:

cities.sort()
for i in range(len(cities)):
    print "(%d, '%s', 0, '%s')" % (i+1, cities[i], cities[i])

Result:

(1, 'Boca Raton', 0, 'Boca Raton')
(2, 'Boynton Beach', 0, 'Boynton Beach')
(3, 'Bradenton', 0, 'Bradenton')
(4, 'Cape Coral', 0, 'Cape Coral')
(5, 'Deltona', 0, 'Deltona')
share|improve this answer
Thanks alot yours looks much more elegant. – user552974 Dec 24 '10 at 5:21
+1 for mentioning primary keys, foreign key constraints, and a generally non-evil data data model… – Donal Fellows Dec 24 '10 at 6:26
up vote 1 down vote

If you want to have your solution:

cities.sort()
for i in range(len(cities)):
    print "(%d, '%s', 0, '%s')" % (i+1, cities[i], cities[i])

a little bit more pythonic, try this:

for i, city in enumerate(sorted(cities)):
    print "(%d, '%s', 0, '%s')" % (i+1, city, city)

But anyway, I would use a specific DB-API for your database. What happens, if you want to insert the city O'Fallon, IL or Coeur d'Alene, ID? They contain an apostrophe, which will break your '%s' formatting logic and invalidate your INSERT statement.

share|improve this answer
Cool I've never used enumerate! +1 thanks. – keegan3d Dec 25 '10 at 5:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.