Python循环mysql语句
我有一个项目,我需要编译每个州的城市数量,并为 mysql 数据库制作一条插入语句。 我认为最简单的方法是通过 python,但由于我是完全菜鸟,所以我想在这里联系所有 python 专家。 这是输入的样子。以下示例适用于佛罗里达州。
cities = ['Boca Raton', 'Boynton Beach', 'Bradenton', 'Cape Coral', 'Deltona']
这就是输出应该是什么。
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'),
如果您仔细查看“Florida”的“idLocationParent”值为“0”,这意味着它是顶级值。这将为 50 个州完成,以便能够 如果有简单的方法的话,将状态名称插入到 mysql 语句中将会锦上添花。还有 idLocation 的字母顺序和自动增量 会很棒的。
这是我试图实现串联的一个例子,这是我需要弄清楚的部分。
对于城市中的城市:打印 (1, '城市', 0, '城市'), 城市
这是我的解决方案
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] + ')'
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] + ')'
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
如果您希望 idLocation 自动递增,请将其作为表的主键。我还会研究外键,而不是将州和城市放入同一张表中。
这是一些代码,可以让您接近您所要求的:
结果:
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:
Result:
如果你想要你的解决方案:
更多一点Python风格,试试这个:
但无论如何,我会为你的数据库使用特定的DB-API。如果您想插入城市
O'Fallon, IL
或Coeur d'Alene, ID
会发生什么?它们包含撇号,这会破坏您的'%s'
格式逻辑并使您的INSERT
语句无效。If you want to have your solution:
a little bit more pythonic, try this:
But anyway, I would use a specific DB-API for your database. What happens, if you want to insert the city
O'Fallon, IL
orCoeur d'Alene, ID
? They contain an apostrophe, which will break your'%s'
formatting logic and invalidate yourINSERT
statement.