Kết nối python với sql server

     

If so, you’ll see the full steps to lớn establish this type of connection using a simple example.Bạn sẽ xem: Kết nối python cùng với sql server

To start, here is a template that you can use lớn connect Pynhỏ to Squốc lộ Server:

import pyodbc conn = pyodbc.connect("Driver=SQL Server;" "Server=server_name;" "Database=database_name;" "Trusted_Connection=yes;")cursor = conn.cursor()cursor.execute("SELECT * FROM database_name.table")for row in cursor: print(row)

The Example to lớn be Used

Let’s đánh giá an example, where:

The Server Name is: RONSQLEXPRESSThe Database Name is: TestDBThe Table Name (with a dbo schema) is: dbo.PersonThe dbo.Person table contains the following data:
NameAgeCity
Jade20London
Mary119NY
Martin25London
Rob35Geneva
Maria42Paris
Jon28Toronto

Steps to Connect Pydong dỏng to lớn SQL Server using pyodbc

Step 1: Install pyodbc

First, you’ll need khổng lồ install the pyodbc package which will be used to connect Pyhạn hẹp to lớn SQL Server.

Bạn đang xem: Kết nối python với sql server

You can use PIP lớn install the pyodbc package:

pip install pyodbc

Step 2: Retrieve sầu the VPS name

Now retrieve sầu your hệ thống name.

In the example below, the server name is: RONSQLEXPRESS

One way to find your current hệ thống name is by running the following query:

SELECT SERVERNAME

Step 3: Obtain the database name

Next, obtain the database name in which your desired table is stored.

You can find the database name under the Object Explorer menu (underneath the Databases section), which is located on the left side of your SQL Server.

In our example, the database name is: TestDB

Step 4: Get the table name

Now you’ll need to lớn get the name of your desired table.

The name of your table would also be located under the Object Explorer menu (underneath the Tables section).

Here, the name of the table is: dbo.Person

Step 5: Connect Pybé nhỏ to Squốc lộ Server

And for the final part, open your Python thả IDLE & fill the server name, database and table information.

Xem thêm: Bom Xịt Tv - 5 Đoạn Clip Ma Có Thật Được Camera Ghi Lại

Here is the structure of the code that you may use in Python:

import pyodbc conn = pyodbc.connect("Driver=Squốc lộ Server;" "Server=server_name;" "Database=database_name;" "Trusted_Connection=yes;")cursor = conn.cursor()cursor.execute("SELECT * FROM database_name.table")for row in cursor: print(row)And this is how the code would look lượt thích in Pythanh mảnh for our example:

Run the code in Pybé (adjusted khổng lồ your hệ thống name, database and table information).

You’ll notice that the results that were printed in Pynhỏ nhắn match with the info that was displayed in Squốc lộ Server:

From Squốc lộ to Pandas DataFrame

You can take things further by going from Squốc lộ lớn Pandas DataFrame using pd.read_sql_query:

import pandas as pdimport pyodbc conn = pyodbc.connect("Driver=SQL Server;" "Server=RONSQLEXPRESS;" "Database=TestDB;" "Trusted_Connection=yes;")cursor = conn.cursor()sql_query = pd.read_sql_query("SELECT * FROM TestDB.dbo.Person",conn)print(sql_query)print(type(sql_query))When applying pd.read_sql_query, don’t forget khổng lồ place the connection string variable at the over. In our case, the connection string variable is conn.

Once you run the code (adjusted lớn your database connection information), you’ll get the following Pandas DataFrame:


*

chú ý that the syntax of print(type(sql_query)) was also added to lớn the code khổng lồ confirm that now we’ve sầu got a DataFrame.

Conclusion and Additional Resources

You have sầu seen how to connect Pythanh mảnh to lớn Squốc lộ Server. Once you established such a connection between Pyhẹp và Squốc lộ Server, you can start using Squốc lộ in Python to manage your data.

You can also use Pyeo hẹp to lớn insert values inlớn SQL Server table.

If you want to lớn learn more about the different types of connections between Pynhỏ bé and other database applications, you may kiểm tra the following tutorials:

For further information about the pyodbc package, please visit the pyodbc documentation.


Chuyên mục: Domain Hosting