python mysql connector - what is the right way for multiple queries

  • Last Update :
  • Techknowledgy :
1._
import mysql.connector
cnx = mysql.connector.connect(user, password, host, database)
cursor = (cnx.cursor)

and, i am trying to query the database twice with different queries as below

query1result = []
query2result = []

cursor.execute(query1)
for each in cursor:
   query1result.append[each]
cursor.execute(query1)
for each in cursor:
   query1result.append[each]

Suggestion : 2

The execute() method accepts an optional keyword argument named multi. By default, it is set to False. If set to True, allows execute() to execute multiple queries separated by semicolons. When called with multi=True, the execute() method returns an iterator which can be used to access the result set produced by the queries.,The executemany() method is another way to execute a query with different sets of parameters. It internally calls the execute() method. Its Syntax is as follows:,On occasions, you might want to execute the same query with a different set of parameters. One way to achieve this is to call execute() method in a loop, like this:,The executemany() method discards the result set returned by the queries, so don't use it to execute the SELECT statement.

 1
 2
 3
 4
 5
 6
 7
 8
 9
 10
 11
 12
 13
 14
 15
 16
 17
import mysql.connector
from mysql.connector.cursor
import MySQLCursor

db = mysql.connector.connect(option_files = 'my.conf', use_pure = True)

cursor1 = db.cursor() # 1 st method
cursor2 = MySQLCursor(db) # 2n d method

print(db)

print("cursor1: ", cursor1)
print("cursor2: ", cursor2)

cursor1.close()
cursor2.close()

db.close()
1
2
3
<mysql.connector.connection.MySQLConnection object at 0x7fcf99799da0>
   cursor1: MySQLCursor: (Nothing executed yet)
   cursor2: MySQLCursor: (Nothing executed yet)
`execute(query, params=None)`
 1
 2
 3
 4
 5
 6
 7
 8
 9
 10
 11
 12
 13
 14
 15

Suggestion : 3

Use Python's MySQL connector to execute complex multi-query .sql files from within python, including setting user and system variables for the current session.,Without any modification to our code we can also make use of user and system set variables as well as updating them within your session, allowing for more complexity within your .sql files,In this tutorial we will be using the official python MySQL connector package to make our connections and execute our queries:,This way of executing .sql files can be extremely powerful and allows you to move complex SQL queries out of your python code and into independent files, allowing for better organization for large projects with many complex database operations.

In this tutorial we will be using the official python MySQL connector package to make our connections and execute our queries:

pip install mysql - connector - python

The you can change the credentials in the below example can be changed to your correct credentials, we'll be using a dictionary cursor for our executions just to make the selection results easier to read.

import mysql.connector

host_args = {
   "host": "localhost",
   "user": "testuser",
   "password": "testpass"
}

con = mysql.connector.connect( ** host_args)

cur = con.cursor(dictionary = True)

test1.sql

DROP DATABASE IF EXISTS sql_test;

CREATE DATABASE sql_test;

USE sql_test;

CREATE TABLE test_table(
   id INT PRIMARY KEY AUTO_INCREMENT,
   test_field VARCHAR(250) NOT NULL DEFAULT ''
);

INSERT INTO test_table(test_field) VALUES('test1');
INSERT INTO test_table(test_field) VALUES('test2');
INSERT INTO test_table(test_field) VALUES('test3');

When the multi argument is provided, the execute statement will return an iterator that when looped over will return the cursor object at the time of each statements execution, each statement within the file will be executed sequentially. For each iteration we can access the cursor object and print information about the query it is currently executing.

# Your connection code here...

   with open('test1.sql', 'r') as sql_file:
   result_iterator = cur.execute(sql_file.read(), multi = True)
for res in result_iterator:
   print("Running query: ", res) # Will print out a short representation of the query
print(f "Affected {res.rowcount} rows")

con.commit() # Remember to commit all your changes!

Running the above example code will output the following:

Running query: CMySQLCursorDict: DROP DATABASE IF EXISTS sql_test
Affected 1 rows
Running query: CMySQLCursorDict: CREATE DATABASE sql_test
Affected 1 rows
Running query: CMySQLCursorDict: USE sql_test
Affected 0 rows
Running query: CMySQLCursorDict: CREATE TABLE test_table(
      id INT..Affected 0 rows Running query: CMySQLCursorDict: INSERT INTO test_table(test_field) VALUE..Affected 1 rows Running query: CMySQLCursorDict: INSERT INTO test_table(test_field) VALUE..Affected 1 rows Running query: CMySQLCursorDict: INSERT INTO test_table(test_field) VALUE..Affected 1 rows

Suggestion : 4

This method executes the given database operation (query or command). The parameters found in the tuple or dictionary params are bound to the variables in the operation. Specify variables using %s or %(name)s parameter style (that is, using format or pyformat style). execute() returns an iterator if multi is True. , This example inserts information about a new employee, then selects the data for that person. The statements are executed as separate execute() operations: , The following example selects and inserts data in a single execute() operation and displays the result of each statement: , The data values are converted as necessary from Python objects to something MySQL understands. In the preceding example, the datetime.date() instance is converted to '2012-03-23'.

Syntax:

cursor.execute(operation, params = None, multi = False)
iterator = cursor.execute(operation, params = None, multi = True)

This example inserts information about a new employee, then selects the data for that person. The statements are executed as separate execute() operations:

insert_stmt = (
   "INSERT INTO employees (emp_no, first_name, last_name, hire_date) "
   "VALUES (%s, %s, %s, %s)"
)
data = (2, 'Jane', 'Doe', datetime.date(2012, 3, 23))
cursor.execute(insert_stmt, data)

select_stmt = "SELECT * FROM employees WHERE emp_no = %(emp_no)s"
cursor.execute(select_stmt, {
   'emp_no': 2
})

The following example selects and inserts data in a single execute() operation and displays the result of each statement:

operation = 'SELECT 1; INSERT INTO t1 VALUES (); SELECT 2'
for result in cursor.execute(operation, multi = True):
   if result.with_rows:
   print("Rows produced by statement '{}':".format(
      result.statement))
print(result.fetchall())
else:
   print("Number of rows affected by statement '{}': {}".format(
      result.statement, result.rowcount))

Suggestion : 5

Last Updated : 13 Jun, 2022

  • INNER JOIN gives the records that are produced by matching columns. JOIN and INNER JOIN both work the same. Syntax:
SELECT column1, column2...
   FROM tablename
JOIN tablename ON condition;
SELECT column1, column2...
FROM tablename
JOIN tablename ON condition;
SELECT column1, column2...
   FROM tablename
INNER JOIN tablename ON condition;
  • LEFT JOIN gives those records from table 1 removing exclusive contents of 2 Syntax:
SELECT column1, column2...
   FROM tablename
LEFT JOIN tablename ON condition;