Menu
I would like to have a connection between my python code and a SQL database. I have read several ways to do it , but I am failing to get the results.
conn = pymysql.connect(user=X,passwd=X,host=X,port=X,database=X, charset='utf8', autocommit=True)
curs = conn.cursor()
try:
curs.execute('SELECT id,sing_name,bir_yr FROM singers_list WHERE bir_yr = ? ',year)
data = curs.fetchall()
for i in data:
yield " Data: " + str(i) + "<br/>"
except:
yield " Failed to get data from base<br/>"
Where year is an int python variable. I am getting the proper results with:
curs.execute('SELECT id,sing_name,bir_yr FROM singers_list)
Which means I am connecting successfully to the database . How can I include variables in queries ? (not only integers , but strings too or any type)
377
The syntax for assigning a value to a SQL variable within a SELECT query is @ var_name := value , where var_name is the variable name and value is a value that you're retrieving. The variable may be used in subsequent queries wherever an expression is allowed, such as in a WHERE clause or in an INSERT statement.
We need to supply values in placeholders ( %s ) before executing a query. Pass Python variables at the placeholder's position when we execute a query. We need to pass the following two arguments to a cursor. execute() function to run a parameterized query.
To assign a value to a variable, use the SET statement. This is the preferred method of assigning a value to a variable. A variable can also have a value assigned by being referenced in the select list of a SELECT statement.
You have to pass the parameters inside an iterable - commonly a tuple:
query = 'SELECT id,sing_name,bir_yr FROM singers_list WHERE bir_yr = %s'
curs.execute(query, (year, ))
Note that I've also replaced the ? placeholder with %s.
Also note that the MySQL driver would automatically handle the type conversion between Python and MySQL, would put quotes if necessary and escape the parameters to keep you safe from SQL injection attacks.
58
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
