Python Loop for mysql statement

I have a project that i need to compile numb开发者_JAVA技巧er 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] + ')'

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')

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.