1

In Python and MSSQL, using pyodc Im trying to create a column in the table with the name Azimuth °, but when Im create the line :

cmd = (" CREATE TABLE " + tableName + "_fly" +" ("
" [Azimuth (°)] float,"
") ")

the table its created but with this name in the header: Azimuth (°)

any suggestions?

thanks in advance

mechanical_meat
  • 163,903
  • 24
  • 228
  • 223
M.Osorio
  • 21
  • 1
  • 5
    Why not name your columns something more usable for sql like simply "Azimuth". You can add the special characters and such when displaying the column. A column name like that would make me want to kill myself being forced to type in queries. – Sean Lange Feb 12 '16 at 19:47
  • This might help you: http://stackoverflow.com/questions/7038213/which-special-characters-are-allowed-in-sql-server-varchar-fields And for your query, you might want to use a Python library to build your CREATE TABLE... i.e. http://pymssql.org/en/latest/ – HEADLESS_0NE Feb 12 '16 at 19:50
  • Hi, thanks for all your answers, I'm trying to "migrate" from an old VB6 that reads an excel file and upload all table into mssql automatically. Since Im learning python I though it was a good exercise to practice. I want to be as close as possible in the SQL because from that SQL table there are so many other processes that maybe look for example "Azimuth (°)" . I'll check all your suggestions Thanks – M.Osorio Feb 12 '16 at 21:23
  • Thank you, used the pymssql and coding utf-8 and works like a charm!! – M.Osorio Feb 17 '16 at 18:09

2 Answers2

2

Update (February 2019): This appears to no longer be an issue with pyodbc, at least under Python_3. pyodbc 4.0.25 creates the table with the correct column name.

(Original answer)

I was able to reproduce your issue using pyodbc, even when specifying utf-8 as my source encoding.

However, I did get the correct result using pymssql:

# -*- coding: utf-8 -*-
import pymssql

cnxn = pymssql.connect(
    server='localhost',
    port='52865',
    user='sa',
    password='whatever',
    database='myDb',
    autocommit=True)
crsr = cnxn.cursor()

sql = """\
CREATE TABLE tableName_fly (
    [ID] INT IDENTITY PRIMARY KEY,
    [Azimuth (°)] FLOAT
    )
"""
crsr.execute(sql)

crsr.close()
cnxn.close()
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
1

This doesn't answer your question, but it might be helpful to know that the Azimuth character ° is a supported identifier in MSSQL.

The rules for valid database identifiers is mentioned here, you can select your matching DB version. The Azimuth character is a Unicode character, part of the 3.2 standard library under the Latin-1_Supplement block.

Forge
  • 6,538
  • 6
  • 44
  • 64
  • Sorry, no. `[Azimuth (°)]` may be an *inadvisable* column name in SQL Server, but it is not *illegal*. I just created such a column in SQL Server 2008 R2 using SSMS. – Gord Thompson Feb 12 '16 at 22:55
  • Thank you for correcting my horrible mistake @GordThompson. – Forge Feb 13 '16 at 07:55