Create table and insert two records SQL using python

Question

I'm trying to create a table on a tempdb database on a local server KHBW001 using MSSQL. My code is:

import pyodbc

connection = pyodbc.connect('Driver={SQL Server};'
                      'Server=KHBW001;'
                      'Database=tempdb;'
                      'Trusted_Connection=yes;')

cursor = connection.cursor()
cursor.executemany(
    "CREATE TABLE tempdb.dbo.NewTestPyTable(Symbol varchar(15), Shares integer, Price double)")  # creates new table

cursor.executemany("""
                INSERT INTO tempdb.dbo.NewTestPyTable (Symbol, Shares, Price)
                VALUES
                [('ETH',55,199.55),
                ('KHC',66,33.5)]
                """)  # insert two records into new table

connection.commit()

I'm getting the error:

"CREATE TABLE tempdb.dbo.NewTestPyTable(Symbol varchar(15), Shares integer, Price double)") # creates new table

TypeError: function takes exactly 2 arguments (1 given)

I don't quite understand what I'm doing wrong. Please assist

you should be using executemany as opposed to execute --> github.com/mkleehammer/pyodbc/wiki/… — gold_cy
– gold_cy, Commented May 26, 2019 at 12:28
wrap your sql statement in 3 double quotes as opposed to three single ticks so that your string column can be properly formatted, also put your parameters in a list as per how the GH shows — gold_cy
– gold_cy, Commented May 26, 2019 at 12:38
I did that... now i'm getting the error TypeError: function takes exactly 2 arguments (1 given) — Kusi
– Kusi, Commented May 26, 2019 at 12:44
how about you update your post with what you have tried, because I nor any one else can see how you attempted this... — gold_cy
– gold_cy, Commented May 26, 2019 at 12:44

Kusi · Accepted Answer · 2019-05-26 13:29:45Z

1

Figured it out...

import pyodbc

connection = pyodbc.connect('Driver={SQL Server};'
                      'Server=KHBW001;'
                      'Database=tempdb;'
                      'Trusted_Connection=yes;')

cursor = connection.cursor()
cursor.execute(
    "CREATE TABLE NewTestPyTable(Symbol varchar(15), Shares integer, Price integer)")  # creates new table
params = [('ETH', 55, 199),
          ('KHC', 66, 33)]
# insert two records into new table
cursor.executemany(
    "INSERT INTO tempdb.dbo.NewTestPyTable (Symbol, Shares, Price) VALUES (?, ?, ?)", params)

connection.commit()

answered May 26, 2019 at 13:29

Kusi

8052 gold badges11 silver badges25 bronze badges

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

Comments

I Iv · Accepted Answer · 2020-09-24 10:46:14Z

i think first of all the problem is in table creation

here is the documentation how to create it correctly https://www.w3schools.com/sql/sql_create_table.asp

type of data in SQL https://www.journaldev.com/16774/sql-data-types

further it seems to me you also need to use the money type for the price.

that's how i would do:

    import pyodbc
connection = pyodbc.connect('Driver={SQL Server};'
                      'Server=KHBW001;'
                      'Database=tempdb;'
                      'Trusted_Connection=yes;')

cursor = connection.cursor()
cursor.executemany(
    "CREATE TABLE tempdb.dbo.NewTestPyTable(Symbol varchar(15), Shares int, Price money)")  # creates new table

cursor.executemany("""
                INSERT INTO tempdb.dbo.NewTestPyTable (Symbol, Shares, Price)
                VALUES
                ('ETH',55,199.55),
                ('KHC',66,33.5);
                """)  # insert two records into new table

connection.commit()

Collectives™ on Stack Overflow

Create table and insert two records SQL using python

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related