Python pyodbc insert into

Шаг 3. Подтверждение концепции, подразумевающее подключение к SQL с помощью pyodbc

Этот пример является подтверждением концепции. Пример кода упрощен для ясности и для него не гарантируется соблюдение рекомендаций корпорации Майкрософт.

Чтобы приступить к работе, выполните следующий пример скрипта. Создайте файл с именем test.py и добавляйте фрагменты кода по ходу работы.

Подключение

import pyodbc # Some other example server values are # server = 'localhost\sqlexpress' # for a named instance # server = 'myserver,port' # to specify an alternate port server = 'tcp:myserver.database.windows.net' database = 'mydb' username = 'myusername' password = 'mypassword' # ENCRYPT defaults to yes starting in ODBC Driver 18. It's good to always specify ENCRYPT=yes on the client side to avoid MITM attacks. cnxn = pyodbc.connect('DRIVER=;SERVER='+server+';DATABASE='+database+';ENCRYPT=yes;UID='+username+';PWD='+ password) cursor = cnxn.cursor() 

Выполнение запроса

Функция cursor.execute может использоваться для извлечения результирующего набора из запроса к базе данных SQL. Эта функция принимает запрос и возвращает результирующий набор, по которому может быть выполнена итерация с использованием cursor.fetchone().

#Sample select query cursor.execute("SELECT @@version;") row = cursor.fetchone() while row: print(row[0]) row = cursor.fetchone() 

Вставка строки

В этом примере вы узнаете, как безопасно выполнить инструкцию INSERT и передать параметры. Параметры защищают приложение от внедрения кода SQL.

#Sample insert query count = cursor.execute(""" INSERT INTO SalesLT.Product (Name, ProductNumber, StandardCost, ListPrice, SellStartDate) VALUES (. )""", 'SQL Server Express New 20', 'SQLEXPRESS New 20', 0, 0, CURRENT_TIMESTAMP).rowcount cnxn.commit() print('Rows inserted: ' + str(count)) 

Azure Active Directory и строка подключения

pyODBC использует драйвер Microsoft ODBC для SQL Server. Если ваша версия драйвера ODBC — 17.1 или более поздняя, интерактивный режим Azure Active Directory драйвера ODBC можно использовать через pyODBC.

Этот интерактивный параметр работает, если Python и pyODBC разрешают драйверу ODBC отображать диалоговое окно. Этот параметр доступен только в ОС Windows.

Пример строки подключения для использования с интерактивной проверкой подлинности Azure Active Directory

В следующем примере представлена строка подключения ODBC, определяющая интерактивную проверку подлинности Azure Active Directory.

См. сведения о параметрах проверки подлинности драйвера ODBC в руководстве по использованию Azure Active Directory с драйвером ODBC.

Дальнейшие действия

Дополнительную информацию можно найти в Центре разработчика Python.

Источник

How to Insert Values into SQL Server Table using Python

Steps to Insert Values into SQL Server Table using Python

Step 1: Install the Pyodbc Package

If you haven’t already done so, install the pyodbc package using the command below (under Windows):

Step 2: Connect Python to SQL Server

There are several items that you may retrieve before you connect Python to SQL Server, including the:

For illustration purposes, let’s use the following information to establish the connection:

• The server name is: RON\SQLEXPRESS
• The database name is: test_database
• The table name is: product

The ‘product’ table contains the following data:

product_id	product_name	price
1	Computer	800
2	TV	1200
3	Printer	150
4	Desk	400

Based on the information above, the following code can be used to connect Python to SQL Server for our example (you may check the following guide for the complete steps to connect Python to SQL Server):

import pyodbc conn = pyodbc.connect('Driver=;' 'Server=RON\SQLEXPRESS;' 'Database=test_database;' 'Trusted_Connection=yes;') cursor = conn.cursor() cursor.execute('SELECT * FROM product') for i in cursor: print(i)

Here is the result that you’ll get once you run the code in Python (adjusted to your database connection information):

(1, 'Computer', 800) (2, 'TV', 1200) (3, 'Printer', 150) (4, 'Desk', 400) 

Step 3: Insert values into SQL Server table using Python

Now let’s insert the following two records into the product table:

product_id	product_name	price
5	Chair	120
6	Tablet	300

Here is the complete Python code to insert those records:

import pyodbc conn = pyodbc.connect('Driver=;' 'Server=RON\SQLEXPRESS;' 'Database=test_database;' 'Trusted_Connection=yes;') cursor = conn.cursor() cursor.execute(''' INSERT INTO product (product_id, product_name, price) VALUES (5,'Chair',120), (6,'Tablet',300) ''') conn.commit()

Don’t forget to add conn.commit() at the end of the code to ensure that the insert command would get executed.

Step 4: Verify the results

Finally, you can verify that the new records were inserted into the product table by running the following SELECT query in SQL Server:

You should now see the two additional records at the bottom of the table:

product_id	product_name	price
1	Computer	800
2	TV	1200
3	Printer	150
4	Desk	400
5	Chair	120
6	Tablet	300

You may also want to check the following tutorials to learn how to:

Источник

pyodbc library for Python to connect with SQL Server databases

pyodbc is an free to use and open source python library for working with ODBC databases like MySQL, Oracle, PostgreSQL, SQLite.
In this article, we will focus on how to use pyodbc for SQL Server databases.
Here we will learn with examples, how to connect to the SQL Server database, read data, update, insert and delete.
Also shown here, how to directly call stored procedures with or without parameters.

Contents

Install pyodbc

Download and install the latest version of python from Python Website if not already done. Setting up virtual environment is not a must. But this is a best practice when working on python projects. Click virtual environment for more details. Install pyodbc using the command: pip install pyodbc pyodbc installation may some times throw an error message like Microsoft Visual C++ 14.0 or greater is required.
Refer next section to fix this error.

pyodbc Installation Error — Microsoft Visual C++ 14.0 or greater is required

This issue is caused by the fact that the latest version of pyodbc is not optimized for the python version that is installed in our machines.
For example, at the time of writing this article, we had python 3.10.0 as the latest version in our machine and pyodbc version was 4.0.32 and this pyodbc version was throwing this error.
Follow the below steps to fix the issue.
Delete the already created virtual environment. Set it up again using the command.

To check if pyodbc installed successfully

Connect to Database

To connect to SQL Server database using pyodbc , we have to import pyodbc , define connection string and then use pyodbc.connect() method. Copy the below code to a python file in your Virtual Environment and run it.

import pyodbc s = '' #Your server name d = '' #Your database name u = '' #Your login p = '' #Your login password str = 'DRIVER=;SERVER='+s+';DATABASE='+d+';UID='+u+';PWD='+ p conn = pyodbc.connect(str) print(conn)

To run the code, use command python filename.py or py filename.py depending on the OS.
If using older version of Virtual Environment, use:

pyodbc.Connection object at 0x00000167148AE2A0

pyodbc Cursor Object

pyodbc Cursor object represents a database cursor, that we can use to manage data from an SQL query. We can create multiple cursors from same connection. Any changes done to data using one cursor is visible to the other cursor immediately.

Read data from table

To read data from SQL Server database, execute the command and use fetchone() , fetchval() or fetchall() method.

import pyodbc #Prepare connection string as in Section 5 conn = pyodbc.connect(connstr) cursor = conn.cursor() cursor.execute("SELECT * FROM TableName") for row in cursor.fetchall(): print(row)

Insert data to table

import pyodbc #Prepare connection string as in Section 5 conn = pyodbc.connect(connstr) cursor = conn.cursor() cursor.execute("INSERT INTO TableName (id, name) VALUES (?, ?)", someid, 'somename') conn.commit()

Update data to table

import pyodbc #Prepare connection string as in Section 5 conn = pyodbc.connect(connstr) cursor = conn.cursor() cursor.execute("UPDATE TableName SET name = ? WHERE 'somename', someid) conn.commit()

Delete data from table

import pyodbc #Prepare connection string as in Section 5 conn = pyodbc.connect(connstr) cursor = conn.cursor() cursor.execute("DELETE FROM TableName WHERE someid) conn.commit()

Call a stored procedure in pyodbc

import pyodbc #Prepare connection string as in Section 5 conn = pyodbc.connect(connstr) cursor = conn.cursor() cursor.execute("") conn.commit()

import pyodbc #Prepare connection string as in Section 5 conn = pyodbc.connect(connstr) cursor = conn.cursor() cursor.execute("", p1, p2) conn.commit()

pyodbc Rowcount

import pyodbc #Prepare connection string as in Section 5 conn = pyodbc.connect(connstr) cursor = conn.cursor() c = cursor.execute("SELECT * FROM TableName").rowcount print(c)

pyodbc fetchone(), fetchval() and fetchall()

fetchone() returns next row.
fetchval() returns first column value of first row.
fetchall() returns all remaining rows.

import pyodbc #Prepare connection string as in Section 5 conn = pyodbc.connect(connstr) cursor = conn.cursor() cursor.execute("SELECT * FROM TableName") for row in cursor.fetchall(): print(row)

Auto Commit

pyodbc commit() and rollback()

commit() Commits all transactions since last commit or rollback.
rollback() Rolls back all uncommitted transactions.

Источник