Shulou(Shulou.com)06/01 Report--

Most people do not understand the knowledge points of this article "python how to connect sql server", so the editor summarizes the following content, detailed content, clear steps, and has a certain reference value. I hope you can get something after reading this article. Let's take a look at this "python how to connect sql server" article.

First install the pymssql library using pip

Pip install pymssql

Specific connection, test code:

# server defaults to 127.0.0.1. Add the port number if the TCP dynamic port is opened For example, '127.0.0.1 server 1433 password # user defaults to the password set by sa# password # database is the database name server =' 127.0.0.1'user = "sa" password= "123456" database = "pubs" conn = pymssql.connect (server, user, password, database) # can be simplified to conn = pymssql.connect (host='localhost', user='sa', password='123456') Database='pubs') cursor = conn.cursor () cursor.execute ('SELECT * FROM titles') print (cursor.fetchall ()) # if you use pandas to read the database import pymssqlimport pandas as pdconn = pymssql.connect (' IP address', 'account', 'password', 'database') sql_1 = "SELECT Id,creat_time from table name" # obtain data directly using pandas "data = pd.read_sql (sql_1, conn) conn.close () print (data)

Sealed version:

# coding=utf-8 # sqlserver connection import pymssqlclass MSSQL: def _ _ init__ (self,host,user,pwd,db): self.host = host self.user = user self.pwd = pwd self.db = db def _ GetConnect (self): "get connection information return: conn.cursor ()"if not self.db: raise (NameError) "No database information set") self.conn = pymssql.connect (host=self.host,user=self.user,password=self.pwd,database=self.db,charset= "utf8") cur = self.conn.cursor () if not cur: raise (NameError, "failed to connect to the database") else: return cur def ExecQuery (self Sql): execute the query statement and return a list containing tuple The element of list is the record line The element of tuple is the field "cur = self.__GetConnect () cur.execute (sql) resList = cur.fetchall () # the connection self.conn.close () return resList def ExecNonQuery (self) must be closed after the query is complete. Sql): "" execute non-query statement call example: cur = self.__GetConnect () cur.execute (sql) self.conn.commit () self.conn.close () "" cur = self.__GetConnect () cur.execute (sql) self.conn .commit () self.conn.close () def main (): # host defaults to 127.0.0.1 Add the port number if the TCP dynamic port is opened For example, '127.0.0.1 pwd 1433' # user defaults to sa # the password set for yourself # db is the database name ms = MSSQL (host='127.0.0.1',user= "sa", pwd= "123456", db= "pubs") resList = ms.ExecQuery ("SELECT * FROM titles") print (resList) if _ _ name__ = ='_ main__': main ()

If pymssql.OperationalError: (20009, b'DB-Lib error message 20009, severity 9: Unable to connect: Adaptive Server is unavailable or does not exist (SZSSQLEXPRESS)') is similar to error protection, there are several possible reasons:

1. The name of the database is misspelled

2.tcp/ip protocol is not enabled, this is located in (right-click this computer, click manage, click Services and applications, click sql configuration Manager, click sqlserver Network configuration, click mssqlserver Protocol, right-click tcp/ip, click enable, restart the computer will take effect)

3. Dynamic port error. The dynamic port is not open by default. If it is opened, you need to write the port number. Check the dynamic port number in tcp/ip protocol in 2, click tcp/ip protocol, click the ip address, and then scroll to the bottom to find the dynamic port number in ipall. You can also delete the dynamic port number and leave it unwritten.

4.sa user login is not enabled

Open sql server management studio, click Security, and click the login name. If the sa entry appears with a red x, it means that sa user login is not enabled. Right-click sa, and click status to enable it.

The above is about the content of this article on "how to connect python to sql server". I believe we all have a certain understanding. I hope the content shared by the editor will be helpful to you. If you want to know more about the relevant knowledge, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.