How can I create multiple connections with pymssql?

Question

I am trying to create a table via pymssql, but I am getting the error:

InterfaceError: Connection is closed.

I have already tried to put the CREATE TABLE statement into the same connection with the SELECT statement, but when I did this no Table was created.

Somebody got a workaround for this?

Here is my code:

from bs4 import BeautifulSoup as bs
import re
from collections.abc import Iterable
import pymssql


conn = pymssql.connect(
    host='xxxx',
    port=xxx,
    user='xxxx',
    password='xxxx',
    database='xxxx'
)
cursor = conn.cursor() 
cursor.execute('SELECT xxx FROM xxx')

text = cursor.fetchall()


conn.close()

c1 = conn.cursor()
c1.execute("""
IF OBJECT_ID('persons', 'U') IS NOT NULL
    DROP TABLE persons
CREATE TABLE persons (
    id INT NOT NULL,
    name VARCHAR(100),
    salesrep VARCHAR(100),
    PRIMARY KEY(id)
)
"""
          )
conn.close()

raw = []  
raw.append(text)
raw1 = str(raw)
soup = bs(raw1, 'html.parser')
autor = soup.get_text()

clear = []
s = autor.replace('\\n', '')
clear.append(s)

print (clear)

`conn.close()` closes the connection, hence your query isn't working. You either need to remake the connection or open another one — EcSync, Oct 17 '19 at 13:34
So I cant just throw the SELECT statement and the CREATE statement in the same connection? — gython, Oct 17 '19 at 13:42

EcSync · Accepted Answer · 2019-10-17T13:50:24.113

0

See below, I removed your conn.close() function

from bs4 import BeautifulSoup as bs import re from collections.abc import Iterable import pymssql

conn = pymssql.connect(
    host='xxxx',
    port=xxx,
    user='xxxx',
    password='xxxx',
    database='xxxx'
)
cursor = conn.cursor() 
cursor.execute('SELECT xxx FROM xxx')

text = cursor.fetchall()


#conn.close() 

c1 = conn.cursor()
c1.execute("""
IF OBJECT_ID('persons', 'U') IS NOT NULL
    DROP TABLE persons
CREATE TABLE persons (
    id INT NOT NULL,
    name VARCHAR(100),
    salesrep VARCHAR(100),
    PRIMARY KEY(id)
)
"""
          )
conn.close()

raw = []  
raw.append(text)
raw1 = str(raw)
soup = bs(raw1, 'html.parser')
autor = soup.get_text()

clear = []
s = autor.replace('\\n', '')
clear.append(s)

print (clear)

edited Oct 17 '19 at 13:50

answered Oct 17 '19 at 13:46

EcSync

842
1
6
20

I think that he should delete also the first `conn.close()`. Furthermore, in Python you comment a line with **#**, not **//** – Christian Cavuti Oct 17 '19 at 13:48
@ChristianCavuti my bad, I'm still in JavaScript mode! and yes I did mean the first `conn.close()` as per my comment – EcSync Oct 17 '19 at 13:52

score -1 · Answer 2 · answered Oct 17 '19 at 13:46

When you manage the connection with any of the connector, when you close the connection and you want to execute another query, you need to create a new connection, in a similary way:

conn = pymssql.connect(
    host='xxxx',
    port=xxx,
    user='xxxx',
    password='xxxx',
    database='xxxx'
)
cursor = conn.cursor() 
cursor.execute('SELECT xxx FROM xxx')
text = cursor.fetchall()
conn.close()
conn = pymssql.connect(
    host='xxxx',
    port=xxx,
    user='xxxx',
    password='xxxx',
    database='xxxx'
)
cursor = conn.cursor()
cursor.execute("""
IF OBJECT_ID('persons', 'U') IS NOT NULL
    DROP TABLE persons
CREATE TABLE persons (
    id INT NOT NULL,
    name VARCHAR(100),
    salesrep VARCHAR(100),
    PRIMARY KEY(id)
)
"""
)
conn.close()

Anyway, it's not the best practice to manage the connections to the db, so is good practice to use the connection pooling. Unfortunately, pymssql has not an internal connection pooling mechanism but you can implement it via SQLAlchemy, finding here the documentation to do it:

Thanks for your answer Christian. I am not getting the error anymore, but the CREATE statement, did not create a new table in the database. — gython, Oct 17 '19 at 14:12

How can I create multiple connections with pymssql?

2 Answers2