Python3: Only add new rows of an updated CSV File to MySQL database
I am downloading the same CSV file multiple times per day with Selenium. I am looking to append the contents of the new rows of this re-downloaded CSV file to MySQL every hour.
The original code I used prior to wanting to only capture the newly created rows in the CSV was:
import pymysql
from sqlalchemy import create_engine
import pandas as pd
data = pd.read_csv('CleanCSV.csv', sep=',', parse_dates=[0])
new_df2 = data.assign(time_stamp=datetime.datetime.now())
cnx = create_engine('mysql+pymysql://root:root@localhost:3306/stats',
echo=False)
new_df2.to_sql(name='stats', con=cnx, if_exists='replace', index=False)
print('Imported')
The above of course only just replaces the table and its contents, however I want to keep the original contents and only add new rows found in the CSV.
This code later evolved to (with a stackoverflow search):
import pymysql
from sqlalchemy import create_engine
import pandas as pd
data = pd.read_csv('Callbacks_Cleaned.csv', sep=',')
cnx = create_engine('mysql+pymysql://root:root@localhost:3306/stats',
echo=False)
for i in range(len(data)):
data.iloc[i:i + 1].to_sql(name="callbacks", if_exists='append', con=cnx)
However this is not working. I keep receiving an integrity / primary Key:
sqlalchemy.exc.IntegrityError: (pymysql.err.IntegrityError) (1062,
"Duplicate entry '0' for key 'PRIMARY'")
Any guidance here is greatly appreciated here.
python mysql python-3.x pandas sqlalchemy
asked Nov 14 '18 at 19:56
John SJohn S
178
add a comment |
I am downloading the same CSV file multiple times per day with Selenium. I am looking to append the contents of the new rows of this re-downloaded CSV file to MySQL every hour.
The original code I used prior to wanting to only capture the newly created rows in the CSV was:
import pymysql
from sqlalchemy import create_engine
import pandas as pd
data = pd.read_csv('CleanCSV.csv', sep=',', parse_dates=[0])
new_df2 = data.assign(time_stamp=datetime.datetime.now())
cnx = create_engine('mysql+pymysql://root:root@localhost:3306/stats',
echo=False)
new_df2.to_sql(name='stats', con=cnx, if_exists='replace', index=False)
print('Imported')
The above of course only just replaces the table and its contents, however I want to keep the original contents and only add new rows found in the CSV.
This code later evolved to (with a stackoverflow search):
import pymysql
from sqlalchemy import create_engine
import pandas as pd
data = pd.read_csv('Callbacks_Cleaned.csv', sep=',')
cnx = create_engine('mysql+pymysql://root:root@localhost:3306/stats',
echo=False)
for i in range(len(data)):
data.iloc[i:i + 1].to_sql(name="callbacks", if_exists='append', con=cnx)
However this is not working. I keep receiving an integrity / primary Key:
sqlalchemy.exc.IntegrityError: (pymysql.err.IntegrityError) (1062,
"Duplicate entry '0' for key 'PRIMARY'")
Any guidance here is greatly appreciated here.
python mysql python-3.x pandas sqlalchemy
asked Nov 14 '18 at 19:56
John SJohn S
178
add a comment |
I am downloading the same CSV file multiple times per day with Selenium. I am looking to append the contents of the new rows of this re-downloaded CSV file to MySQL every hour.
The original code I used prior to wanting to only capture the newly created rows in the CSV was:
import pymysql
from sqlalchemy import create_engine
import pandas as pd
data = pd.read_csv('CleanCSV.csv', sep=',', parse_dates=[0])
new_df2 = data.assign(time_stamp=datetime.datetime.now())
cnx = create_engine('mysql+pymysql://root:root@localhost:3306/stats',
echo=False)
new_df2.to_sql(name='stats', con=cnx, if_exists='replace', index=False)
print('Imported')
The above of course only just replaces the table and its contents, however I want to keep the original contents and only add new rows found in the CSV.
This code later evolved to (with a stackoverflow search):
import pymysql
from sqlalchemy import create_engine
import pandas as pd
data = pd.read_csv('Callbacks_Cleaned.csv', sep=',')
cnx = create_engine('mysql+pymysql://root:root@localhost:3306/stats',
echo=False)
for i in range(len(data)):
data.iloc[i:i + 1].to_sql(name="callbacks", if_exists='append', con=cnx)
However this is not working. I keep receiving an integrity / primary Key:
sqlalchemy.exc.IntegrityError: (pymysql.err.IntegrityError) (1062,
"Duplicate entry '0' for key 'PRIMARY'")
Any guidance here is greatly appreciated here.
python mysql python-3.x pandas sqlalchemy
asked Nov 14 '18 at 19:56
John SJohn S
178
I am downloading the same CSV file multiple times per day with Selenium. I am looking to append the contents of the new rows of this re-downloaded CSV file to MySQL every hour.
The original code I used prior to wanting to only capture the newly created rows in the CSV was:
import pymysql
from sqlalchemy import create_engine
import pandas as pd
data = pd.read_csv('CleanCSV.csv', sep=',', parse_dates=[0])
new_df2 = data.assign(time_stamp=datetime.datetime.now())
cnx = create_engine('mysql+pymysql://root:root@localhost:3306/stats',
echo=False)
new_df2.to_sql(name='stats', con=cnx, if_exists='replace', index=False)
print('Imported')
The above of course only just replaces the table and its contents, however I want to keep the original contents and only add new rows found in the CSV.
This code later evolved to (with a stackoverflow search):
import pymysql
from sqlalchemy import create_engine
import pandas as pd
data = pd.read_csv('Callbacks_Cleaned.csv', sep=',')
cnx = create_engine('mysql+pymysql://root:root@localhost:3306/stats',
echo=False)
for i in range(len(data)):
data.iloc[i:i + 1].to_sql(name="callbacks", if_exists='append', con=cnx)
However this is not working. I keep receiving an integrity / primary Key:
sqlalchemy.exc.IntegrityError: (pymysql.err.IntegrityError) (1062,
"Duplicate entry '0' for key 'PRIMARY'")
Any guidance here is greatly appreciated here.
python mysql python-3.x pandas sqlalchemy
python mysql python-3.x pandas sqlalchemy
asked Nov 14 '18 at 19:56
John SJohn S
178
asked Nov 14 '18 at 19:56
John SJohn S
178
edited Nov 14 '18 at 22:02
John S
asked Nov 14 '18 at 19:56
John SJohn S
178
asked Nov 14 '18 at 19:56
John SJohn S
178
asked Nov 14 '18 at 19:56
John SJohn S
178
178
add a comment |
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53307871%2fpython3-only-add-new-rows-of-an-updated-csv-file-to-mysql-database%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53307871%2fpython3-only-add-new-rows-of-an-updated-csv-file-to-mysql-database%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
