0

My settings are mssql on ubuntu 16.04 (google cloud if matters) and jupyter notebook on the same machine, inserting rows seems to work in one notebook but once closed , the rows are not in the table anymore, any ideas?

NB1

import pymssql
import pandas as pd
conn = pymssql.connect(server="localhost", 
user="SA",password="MM11", port=1433)
cursor = conn.cursor()

cursor.execute('use TestDB')

#cursor.execute("drop test_table")

cursor.execute("""
          CREATE TABLE test_table (ddd INT)""")

stmt = "SELECT * from test_table"
df = pd.read_sql(stmt,conn)
print df.shape

for x in range(1000):
    cursor.execute("insert into test_table values("+str(x)+")")

stmt = "SELECT * from test_table"
df = pd.read_sql(stmt,conn)
df.shape

Works well and outputs: (0, 1) (1000, 1)

NB2

import pymssql
import pandas as pd
conn = pymssql.connect(server="localhost", 
user="SA",password="MM11", port=1433)
cursor = conn.cursor()

cursor.execute('use TestDB')

stmt = "SELECT * from test_table"
df = pd.read_sql(stmt,conn)
df.shape

fails on read_sql

DatabaseError: Execution failed on sql 'SELECT * from test_table': 
(208, "Invalid object name 'test_table'.DB-Lib error message 20018, 
severity 16:\nGeneral SQL Server error: Check messages from the SQL 
Server\n")
Ezer K
  • 3,637
  • 3
  • 18
  • 34
  • what exactly does this mean? (or where could I read about it?) – Ezer K Aug 27 '17 at 13:38
  • 3
    Maybe your auto-commit is disabled? if so, you need to `commit;` changes before closing the session - https://msdn.microsoft.com/en-us/library/ms187878.aspx and https://stackoverflow.com/questions/1090240/how-do-you-set-autocommit-in-an-sql-server-session might be useful – Chen A. Aug 27 '17 at 13:38
  • makes sense (and you are a quick type), i'll try – Ezer K Aug 27 '17 at 13:39

0 Answers0