If the price of the drones comes down further, or fireworks become more regulated, then maybe.
Would they, or would cities find new occasions to use them because they are already bought and paid for? Maybe Memorial and Labor day drone shows, or "Saturday In The Park" drone shows, or Christmas and New Years drone shows. If they already have them in house, why not use them more? I know I would love that.
Honestly I think the biggest threat is also fireworks NIMBYism, people don't like the loud sounds disturbing their lives, their pets, etc combined with the general pressure of bigger and better exacerbating those problems.
The last few years a handful of gender reveal parties started fires, too.
I kinda forsee drone shows getting so extravagant that they make fireworks shows seem quaint: "why don't the fireworks make any pictures?"
Then the drone shows get oversaturated because they can easily be put on day after day without expending any munitions. Then they start to be seen as a nuisance and get regulated.
Fireworks are about as American as you get.