Python Forum
Newbie question for bulk insert into SQL Server database
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Newbie question for bulk insert into SQL Server database
#1
I am using SQL Server 2017 to save people's information, I created a simple data table [People] in [mydb] database
CREATE TABLE [dbo].[People] (
[ID]         [int]          NOT NULL,
[Name]       [nvarchar](40) NOT NULL,
[Age]        [int]          NOT NULL,
[Height]     [int]          NOT NULL,
[Weight]     [int]          NOT NULL
CONSTRAINT PK_People PRIMARY KEY(ID))
GO
person1 = Person(Name='Bob', Age=55, Height=165, Weight=70)
person2 = Person(Name='Doe', Age=45, Height=185, Weight=90)
person3 = Person(Name='John', Age=50, Height=175, Weight=85)
people = [person1, person2, person3]
I will have around 30,000 records of people like the above.
I want to use pyodbc or whatever software package to insert all the people records into [People] data table.
I also have a primary key [ID], I want to use the order in the list of people to be the primary key, for the above example, the ID could be: 1, 2, 3, etc...
I can't figure out how to do the bulk insert into SQL Server 2017 database, I think since SQL Server 2017 has native support for python, so this could be possible.
Please advise!
Thanks,
Reply
#2
I think you are going to have to explain what you want help with a bit more explicitly. The task you are asking about can be pretty complex, and you just posted a snip-it with SQL code not python. I don't know how much help you will get with that little information.
Reply
#3
Sorry, I put the T-SQL statements inside Python code.
Here is my question again:
I am using SQL Server 2017 to save people's information, I created a simple data table [People] in [mydb] database
CREATE TABLE [dbo].[People] (
[ID] [int] NOT NULL,
[Name] [nvarchar](40) NOT NULL,
[Age] [int] NOT NULL,
[Height] [int] NOT NULL,
[Weight] [int] NOT NULL
CONSTRAINT PK_People PRIMARY KEY(ID))
GO
I have the following Python code to generate some records of people, and I want to save all such records into SQL Server 2017 database. The database name is: mydb, and the data table name is: [People].
import collections
 
Person = collections.namedtuple('Person', ['Name', 'Age', 'Height', 'Weight'])
 
person1 = Person(Name='Bob', Age=55, Height=165, Weight=70)
person2 = Person(Name='Doe', Age=45, Height=185, Weight=90)
person3 = Person(Name='John', Age=50, Height=175, Weight=85)
......
person30000 = Person(Name='XYZ', Age=30, Height=160, Weight=65)
people = [person1, person2, person3, ... person30000]
I have 30,000 records of people like the above, which is a list of namedtuple.
I want to use pyodbc or whatever software package to insert all the people records into [People] data table.
I also have a primary key [ID], I want to use the order in the list of people to be the primary key, for the above example, the ID could be: 1, 2, 3, etc...
I can't figure out how to do the bulk insert into SQL Server 2017 database, I think since SQL Server 2017 has native support for python, so this could be possible.
Please advise!
Thanks,
Reply
#4
http://www.pymssql.org/en/stable/pymssql...compliance
Reply
#5
Hello:
Thanks for help, I read the document at:
http://www.pymssql.org/en/stable/pymssql...compliance
But I can't find useful bulk insert code.
Please advise or write some code to bulk insert the records into SQL Server database.
Thanks,
Reply
#6
my link is exactly at that example. see the code
 
cursor.executemany(
    "INSERT INTO persons VALUES (%d, %s, %s)",
    [(1, 'John Smith', 'John Doe'),
     (2, 'Jane Doe', 'Joe Dog'),
     (3, 'Mike T.', 'Sarah H.')])
# you must call commit() to persist your data if you don't set autocommit to True
conn.commit()
Reply
#7
There is a  MS supplied bulk loader for SQL Server, see: https://docs.microsoft.com/en-us/sql/rel...sql-server
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  How is pandas modifying all rows in an assignment - python-newbie question markm74 1 654 Nov-28-2023, 10:36 PM
Last Post: deanhystad
  newbie question - can't make code work tronic72 2 626 Oct-22-2023, 09:08 PM
Last Post: tronic72
  Newbie question about switching between files - Python/Pycharm Busby222 3 543 Oct-15-2023, 03:16 PM
Last Post: deanhystad
  Bulk loading of data using python shivamsvmsri 2 615 Sep-28-2023, 09:04 AM
Last Post: shivamsvmsri
  Newbie.... run for cover. OpenCV question Stevolution2023 2 922 Apr-12-2023, 12:57 PM
Last Post: Stevolution2023
  numpy newbie question bcwilly_ca 4 1,128 Feb-10-2023, 05:55 PM
Last Post: jefsummers
  pysql connection to cloud server database times out Pedroski55 9 4,592 Oct-11-2021, 10:34 PM
Last Post: Pedroski55
  myList.insert(index, element) question ChrisF 1 1,613 Aug-27-2021, 03:49 PM
Last Post: bowlofred
  How to take the tar backup files form remote server to local server sivareddy 0 1,871 Jul-14-2021, 01:32 PM
Last Post: sivareddy
  Question from complete python's newbie Davicom 3 2,305 Jun-09-2021, 06:09 PM
Last Post: bowlofred

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020