def update_inv_quant():
    new_quant = int(input("Enter the updated quantity in stock: "))

Hello! I'm wondering how to insert a user variable into an sql statement so that a record is updated to said variable. Also, it'd be really helpful if you could also help me figure out how to print records of the database into the actual python console. Thank you!

I tried doing soemthing like ("INSERT INTO Inv(ItemName) Value {user_iname)") but i'm not surprised it didnt work

CodePudding user response：

It would have been more helpful if you specified an actual database.

First method (Bad)

The usual way (which is highly discouraged as Graybeard said in the comments) is using python's f-string. You can google what it is and how to use it more in-depth.

but basically, say you have two variables user_id = 1 and user_name = 'fish', f-string turns something like f"INSERT INTO mytable(id, name) values({user_id},'{user_name}')" into the string INSERT INTO mytable(id,name) values(1,'fish').

As we mentioned before, this causes something called SQL injection. There are many good youtube videos that demonstrate what that is and why it's dangerous.

Second method

The second method is dependent on what database you are using. For example, in Psycopg2 (Driver for PostgreSQL database), the cursor.execute method uses the following syntax to pass variables cur.execute('SELECT id FROM users WHERE cookie_id = %s',(cookieid,)), notice that the variables are passed in a tuple as a second argument.

All databases use similar methods, with minor differences. For example, I believe SQLite3 uses ? instead of psycopg2's %s. That's why I said that specifying the actual database would have been more helpful.

Fetching records

I am most familiar with PostgreSQL and psycopg2, so you will have to read the docs of your database of choice.

To fetch records, you send the query with cursor.execute() like we said before, and then call cursor.fetchone() which returns a single row, or cursor.fetchall() which returns all rows in an iterable that you can directly print.

Execute didn't update the database?

Statements executing from drivers are transactional, which is a whole topic by itself that I am sure will find people on the internet who can explain it better than I can. To keep things short, for the statement to physically change the database, you call connection.commit() after cursor.execute()

So finally to answer both of your questions, read the documentation of the database's driver and look for the execute method.

CodePudding user response：

This is what I do (which is for sqlite3 and would be similar for other SQL type databases):

Assuming that you have connected to the database and the table exists (otherwise you need to create the table). For the purpose of the example, i have used a table called trades.

new_quant = 1000

# insert one record (row)
command = f"""INSERT INTO trades VALUES (
    'some_ticker', {new_quant}, other_values, ...
    ) """

cur.execute(command)
con.commit()
print('trade inserted !!')

You can then wrap the above into your function accordingly.