How to operate SQL Server database in Python

0.Contents

1.Preface

2.Preparation

3.Simple test statement

4.Submit and rollback

5. How to write encapsulated into classes

1. Preface

After learning the basic syntax of SQL Server, let’s learn how to use sql in the program. After all, it cannot be used in the program. If used, the practicality is not that great.

2. The most basic SQL query statement

Python uses the pymssql module to operate the SQL Server database, so you need to install pymssql first.
Enter this directly in the command linepip install pymssqlJust install it

Then you need to configure your local SQL Server database and enter Microsoft SQL Server Management Studio to set it up . If you choose to use Windows authentication, you need to change it to SQL authentication. There are many tutorials on this online, just search and you will find them.

3. Simple test statement

Open IDLE and create a new python program:

import pymssql

conn = pymssql.connect(host='127.0.0.1',
                       user='sa',
                       password='123',
                       database='SQLTest',
                       charset='utf8')#查看连接是否成功cursor = conn.cursor()
sql = 'select * from student'cursor.execute(sql)#用一个rs变量获取数据rs = cursor.fetchall()print(rs)

4. Submit and rollback

In python, after completing the "add, delete, modify" operation, you need to execute commit() to actually submit the code for execution. If something unexpected happens, execute rollback() to roll back to the previous state, which is equivalent to all the previous operations being in vain. Done, this also protects the database.
So it is recommended to write a program like this:

try:
    conn = pymssql.connect(host='127.0.0.1',
                           user='sa',
                           password='123',
                           database='SQLTest',
                           charset='utf8')
    cursor = conn.cursor()
    sql = 'insert into student values('0001', '张三', 18, '男', '文学院')'
    cursor.execute(sql)
    conn.commit()except Exception as ex:
    conn.rollback()    raise exfinally:
    conn.close()

You can try deleting conn.commit(), and then see if there are changes in the database.

5. How to encapsulate it into a class

'''    TestDB类    
功能：测试数据库的类写法    
作者：PyLearn       
最后修改日期: 2017/10/17'''
import pymssql
class TestDB():    
def __init__(self):
        try:            self.conn = pymssql.connect(host='127.0.0.1',
                                        user='sa',
                                        password='123',
                                        database='SQLTest',
                                        charset='utf8')            
self.cursor = self.conn.cursor()            
self.sql = "insert into student values('0001', '张三', 18, '男', '文学院')"
            self.cursor.execute(self.sql)            
self.conn.commit()        
except Exception as ex:            
self.conn.rollback()           
 raise ex        
 finally:            
 self.conn.close()if __name__ == '__main__':
    test_DB = TestDB()

The above is the detailed content of How to operate SQL Server database in Python. For more information, please follow other related articles on the PHP Chinese website!