python操作数据库-SQLSERVER-pyodbc

su #Download appropriate package for the OS version #Choose only ONE of the following, corresponding to your OS version #RedHat Enterprise Server 6 curl https://packages.microsoft.com/config/rhel/6/prod.repo > /etc/yum.repos.d/mssql-release.repo #RedHat Enterprise Server 7 curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-release.repo #RedHat Enterprise Server 8 and Oracle Linux 8 curl https://packages.microsoft.com/config/rhel/8/prod.repo > /etc/yum.repos.d/mssql-release.repo exit sudo yum remove unixODBC-utf16 unixODBC-utf16-devel #to avoid conflicts sudo ACCEPT_EULA=Y yum install msodbcsql17 # optional: for bcp and sqlcmd sudo ACCEPT_EULA=Y yum install mssql-tools echo ‘export PATH="$PATH:/opt/mssql-tools/bin"‘ >> ~/.bash_profile echo ‘export PATH="$PATH:/opt/mssql-tools/bin"‘ >> ~/.bashrc source ~/.bashrc # optional: for unixODBC development headers sudo yum install unixODBC-devel

完成odbc驱动的安装后，依赖环境就已经完成了

2、安装pyodbc

这个只要pip安装下就好了。

pip install pyodbc

3、如何使用pyodbc进行t-sql操作

1）简单查询

import pyodbc 
# Some other example server values are
# server = ‘localhost\sqlexpress‘ # 实列名称
# server = ‘myserver,port‘ # 如果实例是有特殊端口号的，默认1433
server = ‘tcp:myserver.database.windows.net‘ 
database = ‘mydb‘ 
username = ‘myusername‘ 
password = ‘mypassword‘ 
cnxn = pyodbc.connect(‘DRIVER={ODBC Driver 17 for SQL Server};SERVER=‘+server+‘;DATABASE=‘+database+‘;UID=‘+username+‘;PWD=‘+ password)
cursor = cnxn.cursor()

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

2）执行操作

cursor.execute("""
INSERT INTO SalesLT.Product (Name, ProductNumber, StandardCost, ListPrice, SellStartDate) 
VALUES (?,?,?,?,?)""",
‘SQL Server Express New 20‘, ‘SQLEXPRESS New 20‘, 0, 0, CURRENT_TIMESTAMP) 
cnxn.commit()
row = cursor.fetchone()

while row: 
    print(‘Inserted Product key is ‘ + str(row[0]))
    row = cursor.fetchone()

3）如何使用window验证

cnxn = pyodbc.connect(‘DRIVER={ODBC Driver 17 for SQL Server};SERVER=‘+server+‘;DATABASE=‘+database+‘;Trusted_Connection=yes)

 

python操作数据库-SQLSERVER-pyodbc

标签：into   host   remove   cts   echo   根据   https   lan   epo