Python3 SQL Server database operations (instance description), python3 instance description

Python3 SQL Server database operations (instance description), python3 instance description

1. Preface

After learning the basic syntax of SQL Server, I will learn how to use SQL in the program. After all, if SQL cannot be used in the program, it will be less practical.

2. Basic SQL query statements

Python uses the pymssql module to operate the SQL Server database. Install pymssql first.

Enter pip install pymssql in the command line to install python.

Then, you must configure your local SQL Server database and enter Microsoft SQL Server Management Studio. If you choose to use Windows authentication, you must change it to SQL authentication. There are a lot of online tutorials, and you can search for them.

3. Simple Test statement

Import pymssqlconn = pymssql. connect (host = '2017. 0.0.1 ', user = 'sa', password = '000000', database = 'sqltest', charset = 'utf8') # Check whether the connection is successful. cursor = conn. cursor () SQL = 'select * from student'cursor.exe cute (SQL) # use an rs variable to retrieve data rs = cursor. fetchall () print (rs)

Open IDLE and create a python program:

Running result:

4. Submit and roll back

In python, after the "add, delete, modify" operation, you also need to execute commit () to actually submit the code for execution. In case of an accident, execute rollback () to roll back to the previous state, it is equivalent to the previous operations, which also protects the database.

Therefore, it is recommended to write a program like this:

Try: conn = pymssql. connect (host = '2017. 0.0.1 ', user = 'sa', password = '000000', database = 'sqltest', charset = 'utf8') cursor = conn. cursor () SQL = 'insert into student values ('2013', 'zhang san', 18, 'male', 'liberal arts ') 'cursor.exe cute (SQL) conn. commit () commit t Exception as ex: conn. rollback () raise exfinally: conn. close ()

You can try to delete conn. commit () and check whether the database has changed.

5. encapsulate the code into a class

'''Testdb class function: Test Database written by: PyLearn blog: http://www.cnblogs.com/PyLearn/ Last modified Date: ''' import pymssqlclass TestDB (): def _ init _ (self): try: self. conn = pymssql. connect (host = '2017. 0.0.1 ', user = 'sa', password = '000000', database = 'sqltest', charset = 'utf8') self. cursor = self. conn. cursor () self. SQL = "insert into student values ('2013', 'zhang san', 18, 'mal', 'liberal arts ')" self.cursor.exe cute (self. SQL) self. conn. commit () commit t Exception as ex: self. conn. rollback () raise ex finally: self. conn. close () if _ name _ = '_ main _': test_DB = TestDB ()

The above Python3 operation of SQL Server database (instance description) is all the content shared by the editor. I hope to give you a reference, and I hope you can provide more support to the customer's house.