Python generate sql insert query
I know this is an old question, but I've often wanted what it seems the OP wants: A VERY simple library for generating basic SQL. Show The below functions do just that. You give them a table name and a dictionary containing the data you want to use and they return the SQL query for the operation you need. The key/value pairs represent field names and values in the database rows.
You use it like so. Just give it a table name and a dictionary (or use the **kwargs feature of python):
But BEWARE OF SQL INJECTION ATTACKS Look what happens when a malicious user of your code does this:
It's easy to make your own makeshift ORM but you only get what you see -- you have to escape the input yourself :) EDIT: I'm still using my old library. I've updated it a bit lately: https://github.com/kokke/nano-ORM-py There is a neater, more Pythonic way of doing this using the Currently, the way you are building your query, you are vulnerable to SQL injection. This is the point @Mat's Mug was getting at in his comment. What if a malicious user decides to give the following as a user ID:
If this happens, a disaster beyond your imagination will occur*! To prevent this, the
If you want to look at another database solution for Python, take a look at SQLAlchemy. Its a pretty powerful framework that makes working with databases much simpler. *=Sorry for the Phantom of the Opera quote. I'm feeling oddly dramatic right now. UpdateTo answer your question about removing the for-loop, the best you can hope for is to use a list comprehension. In many situations, list comprehensions are typically the Pythonic way of iterating over a collection. However, in your situation, this probably is not the case. The comprehension for your current code would look like this:
This is really only effective creating the end to your query. Unfortunately, in addition to being hard to read, it is still vulnerable to SQL injection. In your case, I think a for-loop would be the cleanest way to implement what you want. How write SQL insert query in Python?Inserting data in MySQL table using python. import mysql. connector package.. Create a connection object using the mysql. connector. ... . Create a cursor object by invoking the cursor() method on the connection object created above.. Then, execute the INSERT statement by passing it as a parameter to the execute() method.. How do you insert multiple rows in SQL using Python?What if you want to insert multiple rows into a table in a single insert query from the Python application. Use the cursor's executemany() function to insert multiple records into a table. Syntax of the executemany() method.
How do I create a .SQL file in Python?How to create .. f= open("result. sql","w+") to create result . sql file.. f. write for each lines of statements for creating db and tables.. INSERT the data from each column of df to each column of db tables.. How do you add user input to a database in Python?Python Code :
cursor () #create the salesman table cursor. execute("CREATE TABLE salesman(salesman_id n(5), name char(30), city char(35), commission decimal(7,2));") s_id = input('Salesman ID:') s_name = input('Name:') s_city = input('City:') s_commision = input('Commission:') cursor.
|