7

I am trying something like:

import pyodbc

cnxn = pyodbc.connect(driver ='{SQL Server}' ,server ='host-MOBL\instance',database ='dbname', trusted_connection = 'yes' )
cursor = cnxn.cursor()


cursor.execute("""SELECT * FROM INFORMATION_SCHEMA.TABLES 
       WHERE TABLE_NAME = N'TableName'""")

def checkTableExists(cnxn, TableName):
    cursor = cnxn.cursor()
    cursor.execute("""
        SELECT COUNT(*)
    FROM information_schema.tables
    WHERE TABLE_NAME = '{0}'
    """.format(TableName.replace('\'', '\'\'')))
    if cursor.fetchone()[0] == 1:
        cursor.close()
        return True

    cursor.close()
    return False

if checkTableExists == True:
    print ("already")
elif checkTableExists == False:
    print ("No")

But there is nothing happen, can anyone help me on this? I am using Micrsoft SQL Server Management Studio 2014 Express version. The code will be run in Python. Thank you

Dsw Wds
  • 482
  • 5
  • 17

2 Answers2

12

Use the built-in Cursor.tables method for this check - following code sample assumes connection and cursor are instantiated

if cursor.tables(table='TableName', tableType='TABLE').fetchone():
    print("exists")
else:
    print("doesn't exist")

Note this isn't functionally different from querying INFORMATION_SCHEMA.TABLES, but allows code portability with different database platforms (and IMO improves readability).

Using SQL Server Native Client 11.0 and SQL Server 2014, calling Cursor.tables just executes the sp_tables system stored procedure.

Bryan
  • 17,112
  • 7
  • 57
  • 80
4

Here's a simple example:

import pyodbc

conn = pyodbc.connect('DRIVER={FreeTDS};SERVER=yourserver.com;PORT=1433;DATABASE=your_db;UID=your_username;PWD=your_password;TDS_Version=7.2;')
cursor = conn.cursor()

cursor.execute("""
    IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'your_table_name')
    BEGIN
        SELECT 'Your table exists.' AS result
    END
""")

rows = cursor.fetchall()
for row in rows:
    print(row.result)

That prints "Table Exists" for me. You should be able to modify it to your needs.

FlipperPA
  • 13,607
  • 4
  • 39
  • 71
  • ermm, I have try this before but it is not what I want, by the way I just don't know why it doesn't work for me, it suppose to be work like yours. Thx anyway, good answer – Dsw Wds Apr 20 '16 at 01:12
  • Bryan's suggestion is definitely better, I went SQL Server specific but his solution is much more robust. – FlipperPA Apr 20 '16 at 01:14
  • @Bryan I have upvote yours and his answer, Thx for you guys help – Dsw Wds Apr 20 '16 at 01:18