Python mysql параметры в запросе

How to Execute a SQL Query In Python With Variables

We touched on using parameters in recent post so I wanted to expand on that and show an example for including variables in the SQL statements. If your not familiar with setting up a SQL connection in python, see our post called How to Connect SQL Server in Python.

In many cases you will need to query the database to create a List, Tuple or Dictionary and use those results to execute another statement or loop through values.

Python Variables

Here is a quick summary of variable types and assignment. You will notice that the variable names are all lowercase and the boolean values are case sensitive too. You cannot use all caps like TRUE or FALSE for the boolean values. This is a simple assignment and the syntax is similar to most languages.

## String: app_name = "Some value for string" app_name = 'Same using single quotes' ## Number: num_tests = 214 ## Decimal: decimal_here = 23.18 ## Boolean (1 or 0): is_enabled = True is_enabled = False

Python SQL Server Connection

For this example we will use the previous connection code and get the parameters from variables. This makes the connection settings dynamic and the code reusable even in the same application. Create a new connection by simply changing the variables.

This is our starting code where the parameters are already assigned to the connection:

import pyodbc conn_str = ("Driver=SQL Server>;" "Server=mysqlserver\sqlinst1;" "Database=DBName;" "UID=MyUser;" "PWD=Pass123;") conn = pyodbc.connect(conn_str) cursor = conn.cursor() cursor.execute("SELECT TOP(1000) * FROM sometable") for row in cursor: print(row)

In this example we are showing the same connection with the parameters placed in variables instead. We will leave the Driver value for SQL Server in the “conn_str” syntax since it is unlikely this will be changed often.

We now assign the variables and add them to our “conn” connection object as parameters to the connection.

import pyodbc server= 'mysqlserver\sqlinst1' db= 'DBName' user= 'MyUser' password= 'Pass123' conn = pyodbc.connect("driver=SQL Server;server=%s;database=%s;uid=%s;pwd=%s" % ( server, db, user, password ) ) cursor = conn.cursor() cursor.execute("SELECT * FROM SomeTable") for row in cursor: print(row)

Notice the percent “%” sign syntax and that the code is using a Tuple so the parameters need to be in the correct order.

• %s – generic string or object
• %d – decimal integer
• %f – float
• %g – generic number

This was pretty straight forward example, what about a larger environment where we have Active Directory service accounts?

Windows Authentication

If your working in a domain and you want to use the logged in users domain credentials you can set the “Trusted Connection” parameter. This is what the code would look like:

import pyodbc server= 'mysqlserver\sqlinst1' db= 'DBName' conn = pyodbc.connect("driver=SQL Server;Trusted_Connection=Yes;server=%s;database=%s" % ( server, db) ) cursor = conn.cursor() cursor.execute("SELECT * FROM SomeTable") for row in cursor: print(row)

The user that is executing the Python code will be used automatically to authenticate to the database.

A more dynamic solution here is to assign the “The Trusted Connection” to a variable (db tales com) so it can be an option from within the application. You can switch from the logged on user to a defined user and password through a settings page.

Python Environment Variables

Often the environment variables are used instead of a static assignment. These are variables that exist at the OS level in the current environment you are running. To see these Variables you can run the following:

import os for i, j in os.environ.items(): print(i, j)

I have used many of these in Administrative type applications as they needed the machine name or “COMPUTERNAME”, or the “HOMEPATH”, “JAVA_HOME”, “PYTHONPATH”, “USERDOMAIN” or “USERNAME”. The os module makes this available so I don’t need to access the WMI through another method.

import os user = os.environ['USERNAME'] computer = os.environ['COMPUTERNAME'] domain = os.environ['USERDOMAIN'] print(domain,user,computer)

This can be useful for setting defaults in your SQL database application such as the Domain and Login information for this post purposes.

Источник

Python MySQL Execute Parameterized Query using Prepared Statement

This article demonstrates how to use a Python Parameterized query or Prepared Statement to perform MySQL database operations.

We use Parameterized query to use Python variable in SQL query. For example: –

• We often need to pass variables to SQL select query in where clause to check some conditions.
• In the user signup form user enter his/her details. You can take those values in Python variables and insert them into a table.

Further Reading:

For this article, we are going to use the Employee table present in my MySQL server. See its column details.

Table of contents

What is the Parameterized Query

A parameterized query is a query in which placeholders ( %s ) are used for parameters (column values) and the parameter values supplied at execution time.

Let’s see the example of a parameterized query:

sql_parameterized_query = """Update employee set Salary = %s where >As you can see, we are using a placeholder ( %s ) for the salary and id column. 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.

• SQL query
• A tuple of parameter values. In our case, we need to pass two Python variables, one for salary and one for id.

query = """Update employee set Salary = %s where = (8000, 5) cursor.execute(query, tuple1)

Use of Parameterized Query and Prepared Statement

There are the main 4 reasons to use. There are main four reasons to use.

There are four main reasons to use.

• Compile Once: Parameterized query compiled only once. When you use parameterized query, it gets precompiled and stored in a PreparedStatement object. Now, use this object to execute the same statement multiple times efficiently. Note: For a standard query, MySQL compiles the query each time before running it.
• Improves Speed: If you execute SQL statements repeatedly with a precompiled query, it reduces the execution time.
• Same Operation with Different Data: You can use it to execute the same query multiple times with different data. For example, you want to insert 200 rows in a table. In such cases, use parameterized query to repeatedly execute the same operation with a different set of values.
• It prevents SQL injection attacks.

Note: We are using MySQL Connector Python to execute a Parameterized query.

How to use Parameterized Query in Python

Create a Prepared statement object using a connection.cursor(prepared=True) .

It creates a specific cursor on which statements are prepared and return a MySQLCursorPrepared class instance.

import mysql.connector connection = mysql.connector.connect(host='localhost', database='python_db', user='pynative', password='pynative@#29') # this will retun MySQLCursorPrepared object cursor = connection.cursor(prepared=True)

Example to insert data into MySQL table using Parameterized Query

Sometimes you need to insert a Python variable as a column value in the insert query. For example, a user has filled an online form and clicked on submit. So you need to insert those values into a MySQL table.

First, you need to take user input into a variable and pass that variable to the INSERT query as a placeholder ( %s ). All values are dynamic, i.e., depending on user input.

Let’s see how to use the parameterized query to insert data into the MySQL database using Python.

import mysql.connector try: connection = mysql.connector.connect(host='localhost', database='python_db', user='root') cursor = connection.cursor(prepared=True) # Parameterized query sql_insert_query = """ INSERT INTO Employee (id, Name, Joining_date, salary) VALUES (%s,%s,%s,%s)""" # tuple to insert at placeholder tuple1 = (1, "Json", "2019-03-23", 9000) tuple2 = (2, "Emma", "2019-05-19", 9500) cursor.execute(sql_insert_query, tuple1) cursor.execute(sql_insert_query, tuple2) connection.commit() print("Data inserted successfully into employee table using the prepared statement") except mysql.connector.Error as error: print("parameterized query failed <>".format(error)) finally: if connection.is_connected(): cursor.close() connection.close() print("MySQL connection is closed") 

Data inserted successfully into employee table using the prepared statement MySQL connection is closed

Refer to fetch rows from MySQL table in Python using parameterized Query.

Understand Python MySQL parameterized Query program

• First, we established the connection with MySQL from Python.
• Next, we created a prepared statement object.
• Next, we created the parameterized SQL query. In this query, we are using four placeholders for four columns.
• Next, we added the value of four columns in the tuple in sequential order.
• Next, we passed SQL insert query and tuple to a cursor.execute() method, remember tuple contains user data in the sequential order of placeholders.
• n the end, we are committing our changes to the database using the connection.commit() .
• We placed our all code in the try-except block to catch exceptions if any.

Note: You can also create a prepared statement by explicitly passing the MySQLCursorPrepared class as an argument while creating a cursor.

connection.cursor(cursor_class=MySQLCursorPrepared)

Use Parameterized Query Update data of MySQL table

Let’s see how to update the MySQL table using Python. In this example, we are updating the salary of an employee using a parameterized query.

import mysql.connector try: connection = mysql.connector.connect(host='localhost', database='python_db', user='pynative', password='pynative@#29') cursor = connection.cursor(prepared=True) sql_update_query = """UPDATE Employee set Salary = %s where data_tuple = (12000, 1) cursor.execute(sql_update_query, data_tuple) connection.commit() print("Employee table updated using the prepared statement") except mysql.connector.Error as error: print("parameterized query failed <>".format(error)) finally: if connection.is_connected(): cursor.close() connection.close() print("MySQL connection is closed") 

Employee table updated using the prepared statement MySQL connection is closed

Use Parameterized query and Prepared Statement to Delete data from MySQL table

Now, let’s see how to use the prepared statement and the parameterized query to delete the MySQL table’s data from Python.

For example, when user deleting their data from the web portal. In such a scenario, we need to use those variables inside a parameterized query using a placeholder ( %s ).

import mysql.connector try: connection = mysql.connector.connect(host='localhost', database='python_db', user='pynative', password='pynative@#29') cursor = connection.cursor(prepared=True) sql_Delete_query = """Delete from employee where empId = 2 cursor.execute(sql_Delete_query, (empId,)) connection.commit() print("Record Deleted successfully using Parameterized query") except mysql.connector.Error as error: print("parameterized query failed <>".format(error)) finally: if connection.is_connected(): cursor.close() connection.close() print("MySQL connection is closed") 

Record Deleted successfully using Parameterized query MySQL connection is closed

Working of a Parameterized Query

The first time you pass a SQL query statement to the cursor’s execute() method, it creates the prepared statement.

For subsequent invocations of executing, the preparation phase is skipped if the SQL statement is the same, i.e., the query is not recompiled.

• In the first cursor.execute(query, tuple) Python prepares statement i.e. Query gets compiled.
• For subsequent execution calls of cursor.execute(query, tuple) , The query gets executed directly with passed parameter values.

Next Steps

To practice what you learned in this article, Please solve a Python Database Exercise project to Practice and master the Python Database operations.

Did you find this page helpful? Let others know about it. Sharing helps me continue to create free Python resources.

About Vishal

I’m Vishal Hule, Founder of PYnative.com. I am a Python developer, and I love to write articles to help students, developers, and learners. Follow me on Twitter

Related Tutorial Topics:

Python Exercises and Quizzes

Free coding exercises and quizzes cover Python basics, data structure, data analytics, and more.

• 15+ Topic-specific Exercises and Quizzes
• Each Exercise contains 10 questions
• Each Quiz contains 12-15 MCQ

Источник