Python code for connecting to a database and querying data.

mysql database (mariadb)

Connecting to the database

First, you need to establish a connection to the MySQL database using the () function. You need to provide the address of the database server (host), the user name (user), the password (passwd), and the name of the database you want to operate on (db).

Creating a Cursor Object

Once you have established a database connection, you can use the cursor() method of the connection object to create a cursor object. This method returns a cursor instance that you can use to execute SQL queries and commands.

Module Installation

python3 installationpip3 install mysqlclient

coding

# Introducing Modules
import MySQLdb
# Connect to the database
db = (host="localhost", user="your_username", passwd="your_password", db="your_database")
# Create a cursor object
cursor = ()
utilizationCursorfulfillmentSQLstatement：createdcursorafter the object，你可以utilization它的execute()方法来fulfillmentSQLstatement。for example，你可以fulfillmentSELECTQueries to retrieve data from the database，maybe者fulfillmentINSERT、UPDATE、DELETE等statement来修改数据。
# Execute SQL queries
("SELECT * FROM your_table")
Getting Query Results：as if果fulfillment的是查询（as ifSELECTstatement），你可以utilizationcursortargetfetchone()、fetchmany()maybefetchall()method to get the result。
# Get all query results
results = ()
for row in results:
    print(row)
clotureCursorconnectivity：After completing database operations，你应该cloturecursor对象并cloture数据库连接，to free up resources。
# Close cursor
()
# Close the database connection
()

The fetchone(), fetchmany(size), and fetchall() methods of the cursor object are three different methods used to retrieve the results of a query executed by the execute() method from a cursor. The main difference between them is the way they retrieve the results and the number of results they retrieve:

fetchone():
This method retrieves the next row of the result set one at a time.
It returns a tuple containing the next line of data, or None if there are no more lines.
Using fetchone() is often useful when processing a result set line by line.

("SELECT * FROM your_table")
while True:
    row = ()
    if row is None:
        break
    print(row)

fetchmany(size):
This method retrieves the next size row in the result set.
The size parameter specifies the number of rows to retrieve. If the number of rows remaining in the result set is less than size, the remaining rows are returned.
If the size parameter is omitted or set to a negative number, fetchmany() will attempt to retrieve all remaining rows.
Use fetchmany() to fetch multiple rows of data in a single call.

("SELECT * FROM your_table")
result = (3)  # Get 3 rows of data
for row in result:
    print(row)

fetchall():
This method retrieves all (remaining) rows in the result set.
It returns a list where each element of the list is a tuple of one row of data.
Use fetchall() to get all the rows of a query result at once, which is useful for working with datasets that are not very large.

("SELECT * FROM your_table")
results = ()
for row in results:
    print(row)

Which method you choose depends on your specific needs, such as how you want to retrieve data from the database and how your application will process that data. If you need to process the data row by row, fetchone() might be a good choice. If you know that the amount of data you need to retrieve is not very large, or if you want to get all the data at once for processing, fetchall() may be more appropriate. And fetchmany() offers a compromise by allowing you to fetch data in batches, which can reduce memory usage when working with large amounts of data.

Oracle database

Connecting to the database

To connect to an Oracle database using Python, you can use cx_Oracle, a third-party library that is a Python extension specifically for Oracle databases. Below are the steps to connect to an Oracle database:

Module Installation

Install cx_Oracle. you can use pip to install it:pip install cx_Oracle

coding

import cx_Oracle
# Connect to an Oracle database
# Method 1: Use a concatenated string
dsn = cx_Oracle.makedsn('hostname', port, service_name='service_name')
connection = cx_Oracle.connect('username', 'password', dsn)
# Method 2: Provide connection parameters separately
# connection = cx_Oracle.connect('username', 'password', 'hostname', port, service_name='service_name')
# Execute SQL statements using cursor
cursor = ()
("SELECT * FROM your_table")
# Get query results
results = ()
for row in results:
    print(row)
# Close cursor and connection
()
()

SQL server database

Connecting to the database

To connect to a SQL Server database using Python, you can use the pyodbc library, a popular Python ODBC interface. Here are the steps to connect to a SQL Server database:

Module Installation

Install the pyodbc library using pip:pip install pyodbc

coding

import pyodbc
# Connect to a SQL Server database
conn_str = (
    r'DRIVER={ODBC Driver 17 for SQL Server};'
    r'SERVER=your_server_name;'
    r'DATABASE=your_database_name;'
    r'UID=your_username;'
    r'PWD=your_password'
)
conn = (conn_str)
# Create a cursor object
cursor = ()
# Execute SQL statements
("SELECT * FROM your_table")
# Get query results
results = ()
for row in results:
    print(row)
# Close cursor and connection
()
()