{"id":21691,"date":"2022-12-20T06:17:00","date_gmt":"2022-12-20T05:17:00","guid":{"rendered":"https:\/\/expert-only.com\/?p=21691"},"modified":"2023-01-17T11:24:46","modified_gmt":"2023-01-17T10:24:46","slug":"work-with-sql-server-using-python","status":"publish","type":"post","link":"https:\/\/expert-only.com\/en\/python\/work-with-sql-server-using-python\/","title":{"rendered":"Work with SQL Server using Python and pyodbc"},"content":{"rendered":"\n<h4 class=\"has-text-align-center wp-block-heading\"><strong><em>How to connect to SQL Server with a Python script and the pyodbc library to create a table, select, update and delete data?<\/em><\/strong><\/h4>\n\n\n\n<p>Welcome to this tutorial on how to work with SQL Server databases using Python and pyodbc! In this course, you will learn how to use Python to interact with a SQL Server database. Whether you&#8217;re a beginner or an advanced user, this course will provide you with the knowledge and skills you need to work with SQL Server in Python. In the previous Python tutorial, we saw <a href=\"https:\/\/expert-only.com\/en\/python\/text-files-in-python\/\">how to manage text files using Python<\/a> and simple scripts. <\/p>\n\n\n\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_82_2 ez-toc-wrap-center counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/expert-only.com\/en\/python\/work-with-sql-server-using-python\/#1-get-started-with-python-and-pyodbc\" >1. Get started with Python and pyodbc<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/expert-only.com\/en\/python\/work-with-sql-server-using-python\/#11-install-the-pyodbc-library\" >1.1 Install the pyodbc library<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/expert-only.com\/en\/python\/work-with-sql-server-using-python\/#12-connect-to-the-sql-server-database\" >1.2 Connect to the SQL Server database<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/expert-only.com\/en\/python\/work-with-sql-server-using-python\/#2-prepare-the-sql-server-table-in-python\" >2. Prepare the SQL Server table in Python<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/expert-only.com\/en\/python\/work-with-sql-server-using-python\/#21-create-a-sql-server-table-in-python\" >2.1 Create a SQL Server table in Python<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/expert-only.com\/en\/python\/work-with-sql-server-using-python\/#22-insert-sample-data-using-python\" >2.2 INSERT sample data using Python<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/expert-only.com\/en\/python\/work-with-sql-server-using-python\/#3-sql-server-select-query-with-a-python-script\" >3. SQL Server SELECT query with a Python script<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/expert-only.com\/en\/python\/work-with-sql-server-using-python\/#4-ms-sql-update-query-in-python\" >4. MS SQL UPDATE query in Python<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/expert-only.com\/en\/python\/work-with-sql-server-using-python\/#5-delete-queries\" >5. DELETE Queries<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-10\" href=\"https:\/\/expert-only.com\/en\/python\/work-with-sql-server-using-python\/#6-conclusion-on-sql-server-and-python\" >6. Conclusion on SQL Server and Python<\/a><\/li><\/ul><\/nav><\/div>\n<h2 class=\"wp-block-heading\" id=\"h-1-get-started-with-python-and-pyodbc\"><span class=\"ez-toc-section\" id=\"1-get-started-with-python-and-pyodbc\"><\/span>1. Get started with Python and pyodbc<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>In this section, we will cover the basics of connecting to a SQL Server database using Python. We will use the <code><a href=\"https:\/\/pypi.org\/project\/pyodbc\/\" target=\"_blank\" rel=\"noreferrer noopener nofollow\">pyodbc<\/a><\/code> library. <strong>Pyodbc is a popular library<\/strong>, and it makes much easy to work with SQL Server databases using Python. <\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"11-install-the-pyodbc-library\"><\/span>1.1 Install the pyodbc library<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>First, you will need to install the <code>pyodbc<\/code> library. You can do this by running the following command in your command prompt or terminal.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"python\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">pip install pyodbc\n<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"12-connect-to-the-sql-server-database\"><\/span>1.2 Connect to the SQL Server database<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>Once the library is installed, import it in the Python script using the <strong>import<\/strong> function, and use it to create a connection to the SQL Server database. The script below performs these two operations.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Import the <code>pyodbc<\/code> library<\/li>\n\n\n\n<li>Use the <code>connect()<\/code> method to create a connection to the SQL Server database, ans pass in the necessary information:\n<ul class=\"wp-block-list\">\n<li>The SQL driver: <strong>{SQL Server}<\/strong><\/li>\n\n\n\n<li>The server name: <strong>localhost<\/strong><\/li>\n\n\n\n<li>Database name: <strong>Expert-Only<\/strong><\/li>\n\n\n\n<li>And if the connection should be trusted or not: <strong>yes<\/strong><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"python\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">import pyodbc\n\nconnection = pyodbc.connect('Driver={SQL Server};'\n                            'Server=server_name;'\n                            'Database=database_name;'\n                            'Trusted_Connection=yes;')\n<\/pre>\n\n\n\n<p>In the above script, we import the <code>pyodbc<\/code> library and use the <code>connect()<\/code> method to create a connection to the SQL Server database. We pass in the necessary information such as the server name, database name, and whether the connection should be trusted or not.<\/p>\n\n\n\n<figure class=\"wp-block-embed is-type-wp-embed is-provider-sql-and-it-tutorials wp-block-embed-sql-and-it-tutorials\"><div class=\"wp-block-embed__wrapper\">\n<blockquote class=\"wp-embedded-content\" data-secret=\"qdPQXwtvzs\"><a href=\"https:\/\/expert-only.com\/en\/python\/text-files-in-python\/\">Manage text files in Python using read and write methods<\/a><\/blockquote><iframe loading=\"lazy\" class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" style=\"position: absolute; clip: rect(1px, 1px, 1px, 1px);\" title=\"&#8220;Manage text files in Python using read and write methods&#8221; &#8212; SQL and IT Tutorials\" src=\"https:\/\/expert-only.com\/en\/python\/text-files-in-python\/embed\/#?secret=5AC0ElWfwz#?secret=qdPQXwtvzs\" data-secret=\"qdPQXwtvzs\" width=\"500\" height=\"282\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe>\n<\/div><\/figure>\n\n\n\n<p>Once you have a connection to the SQL Server database, you can use it to execute queries. In the next sections, we will cover how to execute different types of queries using Python.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"2-prepare-the-sql-server-table-in-python\"><\/span>2. Prepare the SQL Server table in Python<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>To work with SQL Server using Python, the most used database operations are SELECT, UPDATE and DELETE. To use them, prepare first the table and insert a few sample rows.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"21-create-a-sql-server-table-in-python\"><\/span>2.1 Create a SQL Server table in Python<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>First, import the pyodbc library and create the connection with the server, we then create a cursor object to execute the SQL query that creates the table customers, we then commit the transaction, and finally close the cursor and connection to the server. Below is a practical <strong>example of a Python connection string<\/strong> to connect to a SQL Server database. <\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"python\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">import pyodbc\n\nconnection = pyodbc.connect('Driver={SQL Server};'\n                            'Server=localhost;'\n                            'Database=Expert-Only;'\n                            'Trusted_Connection=yes;')\n\n# Create a cursor object\ncursor = connection.cursor()\n\n# Create the customers table\ncreate_table_query = \"\"\"\nCREATE TABLE [dbo].[Customers](\n   [CustomerID] [int] NOT NULL,\n   [FirstName] [nvarchar](20) NULL,\n   [LastName] [nvarchar](20) NULL,\n   [City] [nvarchar](20) NULL,\n   [Country] [nvarchar](50) NULL,\n   CONSTRAINT [CustomersPrimaryKeyCustomerID] \n      PRIMARY KEY CLUSTERED ([CustomerID] ASC)\n);\n\"\"\"\ncursor.execute(create_table_query)\n\n# Commit the transaction\nconnection.commit()\n\n# Close the cursor and the connection\ncursor.close()\nconnection.close()\n<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"22-insert-sample-data-using-python\"><\/span>2.2 INSERT sample data using Python<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>Another common type of query is the INSERT query, which is used to insert data into a table. To execute an INSERT query, you can use the <code>cursor()<\/code> method to create a cursor object and then use the <code>execute()<\/code> method to execute the query.<\/p>\n\n\n\n<p>In the following script, we use an <code>INSERT INTO<\/code> statement to insert 10 lines of data into the <code>Customers<\/code> table. We pass the values for the <code>CustomerID<\/code>, <code>FirstName<\/code>, <code>LastName<\/code>, <code>City<\/code>, and <code>Country<\/code> columns. The values are in the format (1, &#8216;Ali&#8217;, &#8216;Ahmed&#8217;, &#8216;Cairo&#8217;, &#8216;Egypt&#8217;). Make sure that the columns&#8217; names and the data types match your table schema.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"python\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\"># Insert data into the customers table\ninsert_query = \"\"\"\nINSERT INTO Customers (CustomerID, FirstName, LastName, City, Country)\nVALUES (1, 'Ali', 'Ahmed', 'Cairo', 'Egypt'),\n       (2, 'Johnny', 'John', 'Toronto', 'Canada'),\n       (3, 'John', 'Doe', 'Mexico City', 'Mexico'),\n       (4, 'Shu', 'Abbas', 'Paris', 'France'),\n       (5, 'Jeane', 'Raffin', 'Liushutun', 'China'),\n       (6, 'Legra', 'Leate', 'Baszki', 'Poland'),\n       (7, 'Sullivan', 'Goadby', 'Xiaoguwei', 'China'),\n       (8, 'Tom', 'Ellams', 'Lop Buri', 'Thailand'),\n       (9, 'Trace', 'Fowell', 'Sriwing', 'Indonesia'),\n       (10, 'Christoffer', 'Rochford', 'Mburukullu', 'Indonesia')\n\"\"\"\ncursor.execute(insert_query)\nprint(cursor.rowcount, \" rows inserted\")\n\n# Commit the changes to the database\nconnection.commit()\n<\/pre>\n\n\n\n<p>The script also displays the number of lines inserted in the table using the rowcount method.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"720\" height=\"800\" src=\"https:\/\/expert-only.com\/wp-content\/uploads\/2023\/01\/python-sql-server-pyodbc-insert-query.jpg\" alt=\"Use the pyodbc library to execute SQL Server insert query using Python\" class=\"wp-image-21758\" srcset=\"https:\/\/expert-only.com\/wp-content\/uploads\/2023\/01\/python-sql-server-pyodbc-insert-query.jpg 720w, https:\/\/expert-only.com\/wp-content\/uploads\/2023\/01\/python-sql-server-pyodbc-insert-query-270x300.jpg 270w\" sizes=\"auto, (max-width: 720px) 100vw, 720px\" \/><figcaption class=\"wp-element-caption\"><strong>Use the pyodbc library to execute SQL Server insert query using Python<\/strong><\/figcaption><\/figure><\/div>\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"3-sql-server-select-query-with-a-python-script\"><\/span>3. SQL Server SELECT query with a Python script<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>The SELECT query is the most common type of query used to retrieve data from a table. To execute a SELECT query, you can use the cursor() method to create a cursor object and then the execute() method to execute the query. Once the query is executed, the fetchall() method can be used to retrieve all the rows returned by the query.<br>In this script, a cursor object is created and a SELECT query is executed on the Customers table. The fetchall() method is used to retrieve all rows from the query and store in the variable rows, then iterates through the rows and print the data. Finally, the cursor and connection are closed.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"python\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\"># Create a cursor object\ncursor = connection.cursor()\n\n# Execute the SELECT query to retrieve data from the Customers table\ncursor.execute(\"SELECT * FROM Customers\")\n\n# Fetch all rows from the query\nrows = cursor.fetchall()\n\n# Iterate through the rows and print the data\nfor row in rows:\n    print(row)\n\n# Close the cursor and the connection\ncursor.close()\nconnection.close()\n<\/pre>\n\n\n\n<p>Keep in mind that if you want to select only specific columns you can change the SELECT statement to <code>SELECT CustomerID, lastname, firstname FROM Customers<\/code>, for example.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"720\" height=\"780\" src=\"https:\/\/expert-only.com\/wp-content\/uploads\/2023\/01\/python-sql-server-pyodbc-select-query-and-print-data.jpg\" alt=\"Execute a SQL Server SELECT query using Python and pyodbc\" class=\"wp-image-21765\" srcset=\"https:\/\/expert-only.com\/wp-content\/uploads\/2023\/01\/python-sql-server-pyodbc-select-query-and-print-data.jpg 720w, https:\/\/expert-only.com\/wp-content\/uploads\/2023\/01\/python-sql-server-pyodbc-select-query-and-print-data-277x300.jpg 277w\" sizes=\"auto, (max-width: 720px) 100vw, 720px\" \/><figcaption class=\"wp-element-caption\"><strong>Execute a SQL Server SELECT query using Python and pyodbc<\/strong><\/figcaption><\/figure><\/div>\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"4-ms-sql-update-query-in-python\"><\/span>4. MS SQL UPDATE query in Python<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Another common type of query is the UPDATE query, which is used to update data in a table. To execute an UPDATE query, you can use the <code>cursor()<\/code> method to create a cursor object and then use the <code>execute()<\/code> method to execute the query.<\/p>\n\n\n\n<p>As with all queries that perform a change in the data, it is important to always <strong>commit the transaction<\/strong> after executing an UPDATE query to save the changes in the table.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"python\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\"># Create a cursor object\ncursor = connection.cursor()\n\n# Execute the UPDATE query to update data in the Customers table\ncursor.execute(\"UPDATE Customers SET City = 'New York' WHERE CustomerID = 1\")\n\n# Commit the changes to the database\nconnection.commit()\n\n# Close the cursor and the connection\ncursor.close()\nconnection.close()\n<\/pre>\n\n\n\n<p>This script creates a cursor object and executes an UPDATE query on the Customers table using the cursor.execute() method. The query updates the value of the City column to &#8216;New York&#8217; where the CustomerID is 1. The connection.commit() method is used to commit the changes to the database. Finally, the cursor and connection are closed.<br>Of course, to update multiple columns at once, adapt the list of fields and change the WHERE clause to specify which rows to update by adding conditions.<\/p>\n\n\n\n<p>Other useful scripts when it comes to manage tables is the <a href=\"https:\/\/expert-only.com\/en\/python\/python-lists\/\">Python lists data type<\/a>.<\/p>\n\n\n\n<figure class=\"wp-block-embed is-type-wp-embed is-provider-sql-and-it-tutorials wp-block-embed-sql-and-it-tutorials\"><div class=\"wp-block-embed__wrapper\">\n<blockquote class=\"wp-embedded-content\" data-secret=\"YIng4oTnlQ\"><a href=\"https:\/\/expert-only.com\/en\/python\/python-lists\/\">Python lists<\/a><\/blockquote><iframe loading=\"lazy\" class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" style=\"position: absolute; clip: rect(1px, 1px, 1px, 1px);\" title=\"&#8220;Python lists&#8221; &#8212; SQL and IT Tutorials\" src=\"https:\/\/expert-only.com\/en\/python\/python-lists\/embed\/#?secret=sXzaI9HGo2#?secret=YIng4oTnlQ\" data-secret=\"YIng4oTnlQ\" width=\"500\" height=\"282\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe>\n<\/div><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"5-delete-queries\"><\/span>5. DELETE Queries<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Another common type of query is the DELETE query, which is used to delete data from a table. To execute a DELETE query, you can use the <code>cursor()<\/code> method to create a cursor object and then use the <code>execute()<\/code> method to execute the query.<\/p>\n\n\n\n<p>As with the INSERT and UPDATE SQL queries, it is important to always commit the <a href=\"https:\/\/web.itu.edu.tr\/aydoganf\/database\/what-is-a-database-transaction\/\" target=\"_blank\" rel=\"noreferrer noopener\">transaction<\/a> after the execution of a DELETE query to effectively save the changes in the table.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"python\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\"># Create a cursor object\ncursor = connection.cursor()\n\n# Execute the DELETE query to delete data from the Customers table\ncursor.execute(\"DELETE FROM Customers WHERE CustomerID = 1\")\n\n# Commit the changes to the database\nconnection.commit()\n\n# Close the cursor and the connection\ncursor.close()\nconnection.close()\n<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"6-conclusion-on-sql-server-and-python\"><\/span>6. Conclusion on SQL Server and Python<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>In this course, we have covered the basic scripts and learned how to work with SQL Server using the Python pyodbc library and methods. For example, how to connect to a SQL Server database, how to execute different types of queries, and how to commit transactions. This is just the beginning of what you can do with Python and a SQL Server database.<\/p>\n\n\n\n<p>With this knowledge, you can start building much more complex scripts and applications that can interact with SQL Server databases. I hope you enjoyed the tutorial and I encourage you to continue learning and experimenting with Python.<\/p>\n\n\n\n<p>To go further and automate your code, use <a href=\"https:\/\/expert-only.com\/en\/python\/user-defined-functions-in-python\/\">user-defined functions in Python<\/a> to create tailored code.<\/p>\n\n\n\n<figure class=\"wp-block-embed is-type-wp-embed is-provider-sql-and-it-tutorials wp-block-embed-sql-and-it-tutorials\"><div class=\"wp-block-embed__wrapper\">\n<blockquote class=\"wp-embedded-content\" data-secret=\"V3YhuBa1lC\"><a href=\"https:\/\/expert-only.com\/en\/python\/user-defined-functions-in-python\/\">User-defined functions in Python<\/a><\/blockquote><iframe loading=\"lazy\" class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" style=\"position: absolute; clip: rect(1px, 1px, 1px, 1px);\" title=\"&#8220;User-defined functions in Python&#8221; &#8212; SQL and IT Tutorials\" src=\"https:\/\/expert-only.com\/en\/python\/user-defined-functions-in-python\/embed\/#?secret=imlqop8kgB#?secret=V3YhuBa1lC\" data-secret=\"V3YhuBa1lC\" width=\"500\" height=\"282\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe>\n<\/div><\/figure>\n","protected":false},"excerpt":{"rendered":"<div class=\"mh-excerpt\"><p>How to connect to SQL Server with a Python script and the pyodbc library to create a table, select, update and delete data? Welcome to this tutorial on how to work with SQL Server databases using Python and pyodbc! <a class=\"mh-excerpt-more\" href=\"https:\/\/expert-only.com\/en\/python\/work-with-sql-server-using-python\/\" title=\"Work with SQL Server using Python and pyodbc\">&#8230;<\/a><\/p>\n<\/div>","protected":false},"author":1,"featured_media":10579,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[657],"tags":[],"class_list":{"0":"post-21691","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-python"},"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v21.7 (Yoast SEO v26.2) - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Work with SQL Server using Python - pyodbc library - Python<\/title>\n<meta name=\"description\" content=\"How to connect and work with SQL Server databases using the Python pyodbc library and create a table, select, update and delete data?\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/expert-only.com\/en\/python\/work-with-sql-server-using-python\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Work with SQL Server using Python and pyodbc\" \/>\n<meta property=\"og:description\" content=\"How to connect and work with SQL Server databases using the Python pyodbc library and create a table, select, update and delete data?\" \/>\n<meta property=\"og:url\" content=\"https:\/\/expert-only.com\/en\/python\/work-with-sql-server-using-python\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL and IT Tutorials\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/ExpertOnlyCom\/\" \/>\n<meta property=\"article:published_time\" content=\"2022-12-20T05:17:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-01-17T10:24:46+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/computer-hard-drive-119FE30BFC9_1920x1080.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1920\" \/>\n\t<meta property=\"og:image:height\" content=\"1080\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Expert-Only\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@expert_only\" \/>\n<meta name=\"twitter:site\" content=\"@expert_only\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Expert-Only\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"8 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/expert-only.com\/en\/python\/work-with-sql-server-using-python\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/expert-only.com\/en\/python\/work-with-sql-server-using-python\/\"},\"author\":{\"name\":\"Expert-Only\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef\"},\"headline\":\"Work with SQL Server using Python and pyodbc\",\"datePublished\":\"2022-12-20T05:17:00+00:00\",\"dateModified\":\"2023-01-17T10:24:46+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/expert-only.com\/en\/python\/work-with-sql-server-using-python\/\"},\"wordCount\":1113,\"commentCount\":2,\"publisher\":{\"@id\":\"https:\/\/expert-only.com\/en\/#organization\"},\"image\":{\"@id\":\"https:\/\/expert-only.com\/en\/python\/work-with-sql-server-using-python\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/computer-hard-drive-119FE30BFC9_1920x1080.jpg\",\"articleSection\":[\"Python tutorials\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/expert-only.com\/en\/python\/work-with-sql-server-using-python\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/expert-only.com\/en\/python\/work-with-sql-server-using-python\/\",\"url\":\"https:\/\/expert-only.com\/en\/python\/work-with-sql-server-using-python\/\",\"name\":\"Work with SQL Server using Python - pyodbc library - Python\",\"isPartOf\":{\"@id\":\"https:\/\/expert-only.com\/en\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/expert-only.com\/en\/python\/work-with-sql-server-using-python\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/expert-only.com\/en\/python\/work-with-sql-server-using-python\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/computer-hard-drive-119FE30BFC9_1920x1080.jpg\",\"datePublished\":\"2022-12-20T05:17:00+00:00\",\"dateModified\":\"2023-01-17T10:24:46+00:00\",\"description\":\"How to connect and work with SQL Server databases using the Python pyodbc library and create a table, select, update and delete data?\",\"breadcrumb\":{\"@id\":\"https:\/\/expert-only.com\/en\/python\/work-with-sql-server-using-python\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/expert-only.com\/en\/python\/work-with-sql-server-using-python\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/expert-only.com\/en\/python\/work-with-sql-server-using-python\/#primaryimage\",\"url\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/computer-hard-drive-119FE30BFC9_1920x1080.jpg\",\"contentUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/computer-hard-drive-119FE30BFC9_1920x1080.jpg\",\"width\":1920,\"height\":1080},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/expert-only.com\/en\/python\/work-with-sql-server-using-python\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"en\",\"item\":\"https:\/\/expert-only.com\/en\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Work with SQL Server using Python and pyodbc\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/expert-only.com\/en\/#website\",\"url\":\"https:\/\/expert-only.com\/en\/\",\"name\":\"SQL and IT Tutorials\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/expert-only.com\/en\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/expert-only.com\/en\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/expert-only.com\/en\/#organization\",\"name\":\"Expert-Only\",\"url\":\"https:\/\/expert-only.com\/en\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg\",\"contentUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg\",\"width\":381,\"height\":174,\"caption\":\"Expert-Only\"},\"image\":{\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/ExpertOnlyCom\/\",\"https:\/\/x.com\/expert_only\",\"https:\/\/www.youtube.com\/channel\/UCMS5sR_FwAetB0FmciNvUaA\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef\",\"name\":\"Expert-Only\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g\",\"caption\":\"Expert-Only\"}}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Work with SQL Server using Python - pyodbc library - Python","description":"How to connect and work with SQL Server databases using the Python pyodbc library and create a table, select, update and delete data?","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/expert-only.com\/en\/python\/work-with-sql-server-using-python\/","og_locale":"en_US","og_type":"article","og_title":"Work with SQL Server using Python and pyodbc","og_description":"How to connect and work with SQL Server databases using the Python pyodbc library and create a table, select, update and delete data?","og_url":"https:\/\/expert-only.com\/en\/python\/work-with-sql-server-using-python\/","og_site_name":"SQL and IT Tutorials","article_publisher":"https:\/\/www.facebook.com\/ExpertOnlyCom\/","article_published_time":"2022-12-20T05:17:00+00:00","article_modified_time":"2023-01-17T10:24:46+00:00","og_image":[{"width":1920,"height":1080,"url":"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/computer-hard-drive-119FE30BFC9_1920x1080.jpg","type":"image\/jpeg"}],"author":"Expert-Only","twitter_card":"summary_large_image","twitter_creator":"@expert_only","twitter_site":"@expert_only","twitter_misc":{"Written by":"Expert-Only","Est. reading time":"8 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/expert-only.com\/en\/python\/work-with-sql-server-using-python\/#article","isPartOf":{"@id":"https:\/\/expert-only.com\/en\/python\/work-with-sql-server-using-python\/"},"author":{"name":"Expert-Only","@id":"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef"},"headline":"Work with SQL Server using Python and pyodbc","datePublished":"2022-12-20T05:17:00+00:00","dateModified":"2023-01-17T10:24:46+00:00","mainEntityOfPage":{"@id":"https:\/\/expert-only.com\/en\/python\/work-with-sql-server-using-python\/"},"wordCount":1113,"commentCount":2,"publisher":{"@id":"https:\/\/expert-only.com\/en\/#organization"},"image":{"@id":"https:\/\/expert-only.com\/en\/python\/work-with-sql-server-using-python\/#primaryimage"},"thumbnailUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/computer-hard-drive-119FE30BFC9_1920x1080.jpg","articleSection":["Python tutorials"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/expert-only.com\/en\/python\/work-with-sql-server-using-python\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/expert-only.com\/en\/python\/work-with-sql-server-using-python\/","url":"https:\/\/expert-only.com\/en\/python\/work-with-sql-server-using-python\/","name":"Work with SQL Server using Python - pyodbc library - Python","isPartOf":{"@id":"https:\/\/expert-only.com\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/expert-only.com\/en\/python\/work-with-sql-server-using-python\/#primaryimage"},"image":{"@id":"https:\/\/expert-only.com\/en\/python\/work-with-sql-server-using-python\/#primaryimage"},"thumbnailUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/computer-hard-drive-119FE30BFC9_1920x1080.jpg","datePublished":"2022-12-20T05:17:00+00:00","dateModified":"2023-01-17T10:24:46+00:00","description":"How to connect and work with SQL Server databases using the Python pyodbc library and create a table, select, update and delete data?","breadcrumb":{"@id":"https:\/\/expert-only.com\/en\/python\/work-with-sql-server-using-python\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/expert-only.com\/en\/python\/work-with-sql-server-using-python\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/expert-only.com\/en\/python\/work-with-sql-server-using-python\/#primaryimage","url":"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/computer-hard-drive-119FE30BFC9_1920x1080.jpg","contentUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/09\/computer-hard-drive-119FE30BFC9_1920x1080.jpg","width":1920,"height":1080},{"@type":"BreadcrumbList","@id":"https:\/\/expert-only.com\/en\/python\/work-with-sql-server-using-python\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"en","item":"https:\/\/expert-only.com\/en\/"},{"@type":"ListItem","position":2,"name":"Work with SQL Server using Python and pyodbc"}]},{"@type":"WebSite","@id":"https:\/\/expert-only.com\/en\/#website","url":"https:\/\/expert-only.com\/en\/","name":"SQL and IT Tutorials","description":"","publisher":{"@id":"https:\/\/expert-only.com\/en\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/expert-only.com\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/expert-only.com\/en\/#organization","name":"Expert-Only","url":"https:\/\/expert-only.com\/en\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/","url":"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg","contentUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg","width":381,"height":174,"caption":"Expert-Only"},"image":{"@id":"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/ExpertOnlyCom\/","https:\/\/x.com\/expert_only","https:\/\/www.youtube.com\/channel\/UCMS5sR_FwAetB0FmciNvUaA"]},{"@type":"Person","@id":"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef","name":"Expert-Only","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/expert-only.com\/en\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g","caption":"Expert-Only"}}]}},"_links":{"self":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts\/21691","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/comments?post=21691"}],"version-history":[{"count":0,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts\/21691\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/media\/10579"}],"wp:attachment":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/media?parent=21691"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/categories?post=21691"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/tags?post=21691"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}