Table is not creating on SQL server while uploading data using python

Question

I'm new to connecting sql server using python.I need to read some data from sql server do the processing & upload the processed data on sql server.All of these task will be done using python. So I have written the code to pull the data from sql server,did the processing & finally while I'm trying to upload the data on sql server then my code is working fine I'm not getting any error message from python.But I'm unable to see the table on SQL Server, even if I'm trying to retrieve the data from sql server using my python code, I'm getting error message

 pyodbc.ProgrammingError: ('42S02', "[42S02] [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'output1'. (208) (SQLExecDirectW)")

Below is the code I used to insert data onto sql server

  conn = sqlite3.connect('Driver={SQL Server};'
                                'Server=My server name;'
                                'Database=my data base name;'
                                'uid=my uid;pwd=my password')
c=conn.cursor()
date = dt.datetime.today().strftime("%m/%d/%Y")
dataToUpload['OPT_TYPE']=opt_type
dataToUpload['Date']=date
list_opt_output=dataToUpload.values.tolist()

c.execute('''CREATE TABLE if not exists output1
         (Class text,Location text,Name text,Maths decimal(5,2), nonMaths decimal(5,2), promopercent decimal(5,3),OPT_TYPE text,Date date)''')
conn.commit()
print("Output table created")
c.executemany('''INSERT INTO output1(Class,Location,Name,Maths, nonMaths,promopercent,OPT_TYPE,Date) VALUES (?,?,?,?,?,?,?,?)''', list_opt_output)

conn.commit()
conn.close()

Can you guide me to resolve this issue?

Are you sure you aren't getting any error? You're using sqlite3 and the syntax CREATE TABLE IF NOT EXISTS is invalid. There's DROP TABLE IF EXISTS and CREATE OR ALTER only for programmability objexts like stored procedures, views, triggers and functions — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Mar 6, 2019 at 7:57
Use pyodbc to connect to SQL Server. The docs cover both pyodbc and pymssql but note that : "Microsoft places its testing efforts and its confidence in pyodbc driver." Bolds by Microsoft — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Mar 6, 2019 at 7:59
@PanagiotisKanavos, no I was not getting any error for CREATE TABLE IF NOT EXIST. After this statement I put a print statement "Table Created" & I was getting this message. Also I didn't have any try catch block. Any way after reading your comment I removed the CREATE TABLE IF NOT EXISTS & using only Create table. I'm running my code. I will let you know once it done — Sonia
– Sonia, Commented Mar 6, 2019 at 9:13

PSK · Accepted Answer · 2019-03-06 07:56:49Z

3

CREATE TABLE if not exists output1 is not valid SQL Server syntax. You can use IF OBJECT_ID('output1','U') IS NULL to check if table is present.

Change your query like following.

c.execute('''IF OBJECT_ID(''output1'',''U'') IS NULL CREATE TABLE output1
         (Class text,Location text,Name text,Maths decimal(5,2), nonMaths decimal(5,2), promopercent decimal(5,3),OPT_TYPE text,Date date)''')
conn.commit()

answered Mar 6, 2019 at 7:56

PSK

18k5 gold badges35 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Sonia Over a year ago

But this statement is throwing error message

File "<ipython-input-9-c9177580ee59>", line 2, in <module>     (Class text,Location text,Name text,Maths decimal(5,2), nonMaths decimal(5,2), promopercent decimal(5,3),OPT_TYPE text,Date date)''')  OperationalError: near "IF": syntax error

Panagiotis Kanavos Over a year ago

@Sonia which driver did you use? Did you use pyodbc or pymssql ?

PSK Over a year ago

You need to check your generated query, it can have additional quotes. Can you try keeping only one quote in the beginning and end

Sonia Over a year ago

Hi all, I modified my create statement command use this as create statement command

c.execute('''CREATE TABLE output1              (Channel text,Market text,Productlow text,promoprice decimal(5,2), nonpromoprice decimal(5,2), promopercent decimal(5,3),OPT_TYPE text,Date date)''')

But still I got the same error,table is not created.Can yo please help me? @PSK, I'm using pyodbc

Collectives™ on Stack Overflow

Table is not creating on SQL server while uploading data using python

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related